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

(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