JJON's StorY

No. 22243

(10G) TRANSACTION ROLLBACK을 하는 문장을 찾는 방법과 ROLLBACK 시간 예상하기
============================================================================

PURPOSE
-------
이 문서는 Transaction Rollback을 모니터하는데 이용한다. Rollback을 하는데 얼마나
많은 시간이 남았는지 , rollback을 하는 문장은 무엇인지 확인할수 있다.

Explanation
-----------
Oracle Database 10g에서는 transaction rollback이 수행될때 , 진행중인 transaction이
$SESSION_LONGOPS view에 event가 기록되어진다.
이 view는 10g 이전 데이타베이스에도 존재하였지만, transaction을 rollback을 하는것은
기록하지 못했었다. Rollback의 경우에 , 6초보다 길어지는 경우에 이 view에 기록되어진다.

$SESSION_LONGOPS view는 모든 진행중인 작업에 대해 보여주는데, "Transaction Rollback"
이라고 기록되어진 OPNAME 컬럼을 통해 확인할수 있다.

다음은 각 컬럼에 대해 살펴보도록 한다.

TIME_REMAINING : 예상된 남겨진 시간을 보여준다. (초단위)
ELAPSED_SECONDS : 이제까지 수행한 시간을 보여준다. (초단위)
TOTALWORK : 수행하는 작업의 전체 양을 보여준다.
SOFAR : 이제까지 수행한 작업의 양을 보여준다.
UNITS : 작업한 양의 단위를 보여준다.
LAST_UPDATE_TIME : view의 내용이 현재것인지를 판단하게 한다.

Example
-------
< Terminal 1 >

sqlplus scott/tiger

SQL> create table emp10 as select * from emp where 1=2;
Table created.

SQL> begin
for i in 1..100000 loop
insert into emp10 select * from emp;
end loop;
end;
/

SQl>Commit;

SQL>update emp10 set sal=1000;

SQL>Rollback;

< Terminal 2 >

sqlplus system/manager

Terminal 1 에서 실행중인 Rollback에 대한 자세한 정보를 얻기 위해 다음의 query을 수행한다.

SQL>  select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING,
      ELAPSED_SECONDS from v$session_longops where username='SCOTT';

OPNAME
----------------------------------------------------------------
TARGET                                                                SOFAR
---------------------------------------------------------------- ----------
 TOTALWORK UNITS                            START_TIME   TIME_REMAINING
---------- -------------------------------- ------------ --------------
ELAPSED_SECONDS
---------------
Transaction Rollback
                                                                        978
     23438 Blocks                           24-NOV-04               207
              9

참고 : 위의 query 결과를 보면, 지금까지 978 block이 rollback 되었고 전체 작업양은 23438 block임을
       알수 있다. 이 작업은 9초간 수행되었고 남아 있는 예상 시간은 207 초이다.

       계속 query하면 작업이 진행됨에 따라 SOFAR column의 값은 늘어나고, TIME_REMAINING column의
       값은 둘어드는 것을 관찰할수 있다.(TIME_REMAINING column의 값은 예상시간이기 때문에 조금 늘었다
       줄었다 할때도 있다.)

SQL> /

OPNAME
----------------------------------------------------------------
TARGET                                                                SOFAR
---------------------------------------------------------------- ----------
 TOTALWORK UNITS                            START_TIME   TIME_REMAINING
---------- -------------------------------- ------------ --------------
ELAPSED_SECONDS
---------------
Transaction Rollback
                                                                      10162
     23438 Blocks                           24-NOV-04               183
            140

참고 : 새로운 SQL_ID column은 V$SQL view와 대응하여 rollback하고 있는 문장을 찾는데 사용된다.
 

SQL> select OPNAME,SQL_ID ,TOTALWORK,UNITS,START_TIME,TIME_REMAINING,
     ELAPSED_SECONDS from v$session_longops where username='SCOTT';

OPNAME                                                           SQL_ID
---------------------------------------------------------------- -------------
 TOTALWORK UNITS                            START_TIME   TIME_REMAINING
---------- -------------------------------- ------------ --------------
ELAPSED_SECONDS
---------------
Transaction Rollback                                             18grqd4nfgv98
     23438 Blocks                           24-NOV-04               119
            226

SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID ='18grqd4nfgv98';

SQL_TEXT
---------------------------------------------------------------------
rollback

Reference Documents
-------------------
<NOTE:1067799.6> - ESTIMATING TIME TO ROLLBACK A TRANSACTION
<NOTE:265198.1> - Oracle 10G Transaction Rollback Monitoring
<Note:271981.1> 10g - Estimating time and finding the statement of a Transaction Rollback

●  Rollback Segment를 삭제하기 전에 Rollback Segment 상태가 OFFLINE인지 확인함.
· 삭제할 Rollback Segment가 현재 ONLINE, PARTLY AVAILABLE, NEEDS RECOVERY나 INVALID상태면 삭제할 수 없음.
· 상태가 INVALID면 세그먼트는 이미 삭제된 것임

●  Rollback Segment를 삭제하기 전에 OFFILNE으로 설정해야 함.

●  Rollback Segment를 삭제하려면 DROP ROLLBACK SEGMENT시스템 권한이 있어야 함.

●  Rollback Segment가 OFFLINE이면 Server Manager의 [Drop] 메뉴 항목이나 SQL 명령어 DROP ROLLBACK SEGMENT를 사용하여 삭제할 수 있음.
·예 : DROP PUBLIC ROLLBACK SEGMENT data1_rs;

●  DROP ROLLBACK SEGMENT명령어를 사용한다면 public키워드를 포함하거나 생략하여 삭제할 Rollback Segment의 정확한 유형을 Public 또는 Private으로 나타냄.

▶ 주의 : ROLLBACK_SEGMENTS에 지정된 Rollback Segment가 삭제되면 데이터베이스 매개변수 파일을 편집하여 삭제된 Rollback Segment 이름을 ROLLBACK_SEGMENTS 매개변수의 목록에서 제거함.
▶ 다음 인스턴스를 시작하기 전에 이 단계가 수행되지 않으면 삭제된 Rollback Segment를 획득할 수 없으므로 다음 인스턴스를 시작할 수 없음.

●  Rollback Segment가 삭제되면 상태가 INVALID로 변경됨.

●  다음 Rollback Segment를 생성할 때 가능하다면 삭제된 Rollback Segment가 사용했던 열을 사용하고, 삭제된 Rollback Segment의 행은 더 이상 DBA_ROLLBACK_SEGS뷰에 나타나지 않음.

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 {integer|UNLIMITED}]
                [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 {integer|UNLIMITED}]
                 [OPTIMAL {integer[K|M]|NULL}] )]

   예) 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 ;