11. ·Ñ¹é ¼¼±×¸ÕÆ® ´Ù·ç±â (Managing Rollback Segments)
- Rollback Segment : before image ÀúÀå
Rollback SegmentÀÇ ¸ñÀû
- Transaction Rollback
- Transaction Recovery
- Read Consistency : ÀÏ°ý¼ºÀ» À§Çؼ...
Read-Consistency
* Select ¹®ÀåÀÌ ½ÇÇàµÇ´Â µµÁß¿¡ µ¥ÀÌÅÍ°¡ º¯°æµÇ´õ¶óµµ º¯°æÀü data¸¦ ºÒ·¯¿À°Ô ÀÏ°ý¼ºÀ» ÁØ´Ù.
* SET TRANSACION READ ONLY ¸í·ÉÀ¸·Î...
Read only ¸ðµåÀÌ¸é¼ ÀÏ°ý¼ºÀÖ´Â µ¥ÀÌÅ͸¦ º¸¿©ÁְԵȴÙ.
--> ÀÌ ¸ðµå ÀÏ ¶§, select ½Ã°£ÀÌ ³Ê¹« ±æ¾î¼ ±× ½Ã°£Áß¿¡ transactionÀÌ ³Ê¹« ¸¹ÀÌ ÀϾ¸é
ORA_01555 SNAPSHOT TOO OLD ¿¡·¯°¡ ¹ß»ýÇÒ ¼ö ÀÖ´Ù.
ÀÌ ¿¡·¯°¡ ¹ß»ýÇϸé rollback segmentÀÇ °¹¼ö¸¦ ´Ã·ÁÁÖ¸é µÈ´Ù.
Creating Rollback Segments
CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment
[TABLESPACE tablespace ]
[STORAGE ([INITIAL integer [K|M]]
[NEXT integer [K|M]]
[MINEXTENTS integer]
[MAXEXTENTS ]
[OPTIMAL {integer [K|M]|NULL}] )
]
* PCTINCREASE ´Â »ç¿ë ¸øÇÔ.
* MINEXTENTS >= 2
¿¹) CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (INITIAL 100K NEXT 100K OPTIMAL 4M
MINEXTENTS 20 MAXEXTENTS 100) ;
Rollback Segments ONLINE ¹æ¹ý
- Rollback Segment´Â createÇϸé offline »óÅÂÀÌ´Ù.
1) ALTER ROLLBACK SEGMENT rbs01 ONLINE ;
--> shutdown ÇÏ¸é ´Ù½Ã OFFLINEÀ¸·Î µÈ´Ù.
2) initSID.ora file¿¡¼ ROLLBACK_SEGMENTS=(rbs01)À» Ãß°¡ÇÏ¸é µÈ´Ù.
--> startupÇÒ ¶§¸¶´Ù Ç×»ó Àû¿ëµÈ´Ù.
Rollback Segment Storage º¯°æ
ALTER ROLLBACK SEGMENT rollback_segment
[STORAGE ( [NEXT integer [K|M]]
[MINEXTENTS integer]
[MAXEXTENTS ]
[OPTIMAL ] )]
¿¹) ALTER ROLLBACK SEGMENT rbs01
STORAGE (MAXEXTENTS 200) ;
Rollback Segment Deallocate Çϱâ
¿¹) ALTER ROLLBACK SEGMENT rbs01
SHRINK TO 4M ;
Rollback Segment OFFLINE ¹æ¹ý
- transactionÀÌ ³¡³¯ ¶§±îÁö ´ë±âÇÏ°í ÀÖ´Ù°¡ ¸ðµç transactionÀÌ ³¡³ª¸é OFFLINEÀÌ µÈ´Ù.
- »õ·Î¿î transactionÀ» Çã¿ëÇÏÁö ¾Ê´Â´Ù.
¿¹) ALTER ROLLBACK SEGMENT rbs01 OFFLINE ;
Rollback Segment DROP Çϱâ
- DROPÇϱâ Àü¿¡´Â ¹Ýµå½Ã OFFLINEÀ» ÇØ¾ß ÇÑ´Ù.
¿¹) DROP ROLLBACK SEGMENT rbs01 ;
DBA_ROLLBACK_SEGS view : Rollback Segment Á¤º¸¸¦ °®°í ÀÖ´Â view
column :
SEGMENT_ID
SEGMENT_NAME
TABLESPACE_NAME
OWNER (PUBLIC or SYS)
STATUS (ONLINE or OFFLINE)
SVRMGR> SELECT segment_name, tablespace_name, owner, status
2 FROM dba_rollback_segs ;
Rollback Segment Statistics
V$ROLLNAME : usn, name
V$ROLLSTAT : usn, extents, rssize, xacts, optsize, hwmsize, aveactive, status, curext, curblk
- XACTS : ÇöÀç rollback segment¸¦ »ç¿ëÇÏ°í ÀÖ´Â transaction ¼ö
SVRMGR> SELECT n.name, s.extents, s.rssize, s.optsize,
2 s.hwmsize, s.xacts, s.status
3 FROM v$rollname n, v$rollstat s
4 WHERE n.usn = s.usn ;
Rollback Segment: Current Activity
V$SESSION : saddr, username, sid, serial#
V$TRANSACTION : ses_addr, xidusn, ubafil, ubablk, ubasqn, ubarec, status, used_ublk, used_urec
SVRMGR> SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
2 FROM v$session s, v$transaction t
3 WHERE s.saddr = t.ses_addr ;
Blocking Session ã´Â ¹æ¹ý
SVRMGR> SELECT s.sid, s.serial#, t.start_time, t.xidusn, s.username
2 FROM v$session s, v$transaction t, v$rollstat r
3 WHERE s.saddr = t.ses_addr
4 AND t.xidusn = r.usn
5 AND ((r.curext = t.start_uext-1) OR
6 ((r.curext = r.extents-1) AND t.start_uext=0)) ;
--> ù ¹ø° extent°¡ blocking µÇ¾úÀ» ¶© 5LINE¿¡¼ÀÇ Á¶°ÇÀÌ ¼º¸³µÇÁö ¾Ê±â ¶§¹®¿¡...
6LINE Á¶°ÇÀ» Ãß°¡ÇÑ´Ù.
¿¬½À
ÇöÀç database¿¡ ¸î°³ÀÇ rollback segment°¡ Á¸ÀçÇÏ´ÂÁö Á¶È¸ÇϽʽÿÀ.
$ sqlplus system/manager
SQL> select * from dba_rollback_segs ;
rbs tablespace¿¡ rbs03, rbs04 ¶õ À̸§À¸·Î rollback segment¸¦ »ý¼ºÇϽʽÿÀ. (storage´Â initial 10k next 10k minextents 2 optimal 20k ¸¦ ÀÌ¿ëÇϽʽÿÀ)
SQL> create rollback segment rbs03
2 tablespace rbs
3 storage (initial 10k next 10k minextents 2 optimal 20k) ;
SQL> create rollback segment rbs04
2 tablespace rbs
3 storage (initial 10k next 10k minextents 2 optimal 20k) ;
rollback segmentµéÀÇ status¸¦ Á¶È¸ÇÏ°í, offline »óÅÂÀÎ rollback segmentµéÀ» online »óÅ·Π¸¸µé¾î º¸½Ê½Ã¿À.
SQL> select segment_name, tablespace_name, status
2 from dba_rollback_segs ;
SQL> alter rollback segment rbs03 online ;
SQL> alter rollback segment rbs03 online ;
scott user ·Î Á¢¼ÓÇÑ ÈÄ transaction À» ¼öÇàÇÏ°í, ¾î´À rollback segment°¡ ±× transaction ¿¡ ÀÇÇؼ »ç¿ëµÇ°í ÀÖ´ÂÁö È®ÀÎÇϽʽÿÀ.
$ sqlplus scott/tiger
SQL> create table test(name char(30)
2 storage (initial 100k)
3 tablespace data01 ;
SQL> insert into test values('aaaaaaaa') ;
´Ù¸¥ Å͹̳¯ À©µµ¿ì¸¦ ¿¾î¼,
$sqlplus system/manager
SQL> select name, extents
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn
4 and s.xacts > 0 ;
scott user·Î Á¢¼ÓÇÑ ÈÄ, transactionÀ» ¼öÇàÇÏ¿© rollback segment°¡ shrink µÇ´ÂÁö È®ÀÎÇ϶ó.
$ sqlplus scott/tiger
SCOTT.SQL> set transaction use rollback segment rbs03 ;
SCOTT.SQL> begin
2 for i in 1..500 loop
3 insert into test values('aaaaaaaaaa') ;
4 end loop ;
5 end ;
6 /
´Ù¸¥ Å͹̳¯ À©µµ¿ì¸¦ ¿¾î¼, rollback segmentÀÇ »ç¿ëÀ» monitoring ÇÑ´Ù.
$ sqlplus system/manager
SYSTEM.SQL> select name, extents
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn
4 and s.xacts > 0 ;
SCOTT.SQL> rollback ;
SYSTEM.SQL> select name, extents, xacts, shrinks
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn ;
==> rbs03ÀÇ xacts¿Í extents È®ÀÎ!!!
SYSTEM.SQL> alter rollback segment rbs03 shrink ;
SYSTEM.SQL> select name, extents, xacts, shrinks
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn ;
==> rbs03ÀÇ extents°¡ shrink µÇ¾ú´ÂÁö È®ÀÎ!!!
rbs03 ¿Í rbs04 rollback segment ¿¡ active transaction ÀÌ ¾ø´ÂÁö È®ÀÎÇÑ ÈÄ, DROP ÇϽʽÿÀ.
$ sqlplus system/manager
SQL> select name, extents, xacts, shrinks
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn ;
==> xacts °¡ 0 ÀÎÁö È®ÀÎ!!!
SQL> alter rollback segment rbs03 offline ;
SQL> alter rollback segment rbs04 offline ;
SQL> drop rollback segment rbs03 ;
SQL> drop rollback segment rbs04 ;
[/pre]
11. ÅÛÆ÷·¯¸® ¼¼±×¸ÕÆ® ´Ù·ç±â (Managing Temporary Segments)
[b]Temporary Segment Usage[/b]
; Temporary segment´Â ¾Æ·¡ÀÇ ¹®ÀåÀ» ¼öÇàÇÒ ¶§ »ç¿ëµÈ´Ù.
SELECT ... ORDER BY
CREATE INDEX
SELECT DISTINCT
SELECT ... GROUP BY
SELECT ... UNION
* size´Â initial parameter SORT_AREA_SIZE ¿¡¼ ¼³Á¤ÇÑ´Ù.
[b]Temporary Segment ÀÇ type (TEMPORARY, PERMANENT)[/b]
1) Temporary segments in a PERMANENT tablespace
- transactionÀÌ ÇÊ¿äÇÒ ¶§ »ý¼ºµÈ´Ù.
- ¹®ÀåÀÌ ¿ÏÀüÇÏ°Ô ½ÇÇàµÇ¾úÀ» ¶§ SMONÀÌ ÀÛ¾÷ÇÑ´Ù.
ALTER TABLESPACE tablespace_name PERMANENT
2) Temporary segments in a TEMPORARY tablespace
- sort segment ¶ó°íµµ ÇÑ´Ù.
- instance°¡ startup ÇÑ ÈÄ¿¡ »ý¼ºµÈ´Ù.
- instance°¡ shutdown µÉ ¶§±îÁö »ì¾ÆÀÖ´Ù.
- Sort Extent PoolÀÇ Á¤º¸¸¦ ±âÃÊ·Î ÇÑ´Ù.
ALTER TABLESPACE tablespace_name TEMPORARY
[b]V$SORT_SEGMENT view : sort extent poolÀÇ »óÅ Á¤º¸[/b]
column :
tablespace_name
extent_size
total_extents
total_blocks
used_extents
used_blocks
free_extents
free_blocks
max_sort_size
max_sort_blocks
SVRMGR> SELECT tablespace_name, extent_size,
2 total_extents, max_sort_blocks
3 FROM v$sort_segment ;
[b]V$SORT_USAGE view : ÇöÀç active sort Á¤º¸[/b]
column :
session_addr
tablespace
contents
extents
blocks
SVRMGR> SELECT s.username, u.tablespace,
2 u.contents, u.extents, u.blocks
3 FROM v$session s, v$sort_usage u
4 WHERE s.saddr = u.session_addr ;
[b] ¿¬½À[/b]
TEMP Å×ÀÌºí½ºÆäÀ̽º¸¦ TEMPORARY ·Î º¯°æÇÏ°í, º¯°æÀÌ µÇ¾ú´ÂÁö¸¦ °ËÁõÇϽʽÿÀ.
SQL> alter tablespace temp temporary ;
SQL> select tablespace_name, contents
2 from dba_tablespaces ;
SQL> select username, temporary_tablespace
2 from dba_users ;
==> system ÀÇ temporary tablespace°¡ temp·Î µÇ¾îÀÖ´ÂÁö È®ÀÎÇÏ°í, ¸¸¾à ¾Æ´Ï¸é ¹Ù²Û´Ù.
( alter user system temporary tablespace temp ;)
ÀνºÅϽº¸¦ Á¾·áÇÏÁö ¾Ê°í SORT_AREA_SIZE ¸¦ 2Kb·Î º¯°æÇϽʽÿÀ.
SQL> alter system set sort_area_size = 2048 deferred ;
* deferred : ÀÌ ¸í·ÉÀ» ½ÇÇàÇÏ°í ³ª¼ ´ÙÀ½¿¡ Á¢¼ÓÇÒ ¶§ ºÎÅÍ »ç¿ëÇ϶ó!
»õ·Ó°Ô »ç¿ëÀÚ SYSTEM À¸·Î½á µ¥ÀÌÅͺ£À̽º¿¡ µÎ °³ÀÇ session À» ¿ÀÇÂÇϽʽÿÀ. ÇϳªÀÇ ¼¼¼Ç¿¡¼ srt_dd.sql À» ½ÇÇàÇÏ°í ³ª¸ÓÁö ¼¼¼Ç¿¡¼ Á¤·Ä ÀÛ¾÷À» ¸ð´ÏÅÍ ÇϽʽÿÀ.
========== srt_dd.sql ===========
-- srt_dd.sql
-- Use for Lab 11 Q3 O8DBA class
SELECT object_name
FROM dba_objects
UNION
SELECT segment_name
FROM dba_segments
/
=================================
$ sqlplus system/manager
SQL> @ srt_dd
´Ù¸¥ Å͹̳¯ À©µµ¿ì¸¦ ¿°í,
$ sqlplus system/manager
SQL> select tablespace_name, total_extents, total_blocks
2 from v$sort_segment ;
SORT_AREA_SIZE ¸¦ Àç¼³Á¤ ÇϽʽÿÀ.
SQL> alter system set sort_area_size = 65536 deferred ;
|
|
|