oracle rollback (롤백 모니터링)

oracle rollback (롤백 모니터링)

1.주요 테이블에 세션 KILL 현상 모니터링
delete from mt_pnt;

 

클라이언트 프로그램 닫음. 서버 세션 종료 안됨.

2.모니터링 세션
O_2 8217,42810  934568,1426068   SVCES:TMA  ????      omstgdb    oracle@omstgdb                 db file sequential read:[T]MT_PNT: 1                         cqvdtymbzuc56 DELETE    21.3    67.0       0

alter system kill session '8217,42810 ' immediate;

 

3.세션 Kill 이후
-- front session 지연
O_1 6320,8827   3035690,1234     TMALL:FRO  weblogic  frontwas3  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .1     4.7       0
O_1 8583,21674  3114038,1234     TMALL:FRO  weblogic  frontwas12 JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .3     5.6       0
O_1 7428,64233  2024334,1234     TMALL:FRO  weblogic  frontwas3  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .1     4.7       0
O_1 6865,23496  1605894,1234     TMALL:FRO  weblogic  frontwas11 JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .4     4.2       0
O_1 5339,10925  3224126,1234     TMALL:FRO  weblogic  frontwas2  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .6     3.6       0
O_1 5648,31849  2065088,1234     TMALL:FRO  weblogic  frontwas15 JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .4     3.7       0
O_1 6910,10949  3064190,1234     TMALL:FRO  weblogic  frontwas9  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .8     3.8       0
O_1 8521,61457  3023040,1234     TMALL:FRO  weblogic  frontwas1  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .3     3.6       0
O_1 6067,31769  3387798,1234     TMALL:FRO  weblogic  frontwas14 JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .2     5.5       0
O_1 5877,44147  1635090,1234     TMALL:FRO  weblogic  frontwas1  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT     1.3     5.0       0
O_1 5221,40209  852614,1234      TMALL:FRO  weblogic  frontwas14 JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .3     3.5       0
O_1 5671,57934  3527756,1234     TMALL:FRO  weblogic  frontwas14 JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .0     4.6       0
O_1 5972,15150  418246,1234      TMALL:FRO  weblogic  frontwas5  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:                              c3w269jgfb8dc INSERT      .8     4.9       0
O_1 6548,33946  3305658,1234     TMALL:FRO  weblogic  frontwas2  JDBC Thin Client               gc buffer busy:[TY]_SYSSMU518$:    
                         c3w269jgfb8dc INSERT      .2     3.2       0

4.undo를 참조하는 세션에 대한 대기 이벤트 발생
-전체 delete후 rollback중이므로, before이미지가 undo에 있는 세션은 rollback 완료시 까지
대기하게 되고, 새로운 세션은 before 이미지를 확인후 세션 처리로 인해 전체적인 지연이
발생함.

-delete 이후 해당 before 이미지에서 지연이 발생하는 것을 확인했지만, 실제 세션 Kill 이전 어떤 undo segment를

사용했었는지 사전 확인이 필요함.

-undo_stat.sql을 실행하면서 함께 모니터링후 Kill session을 진행

 

->@tr_undo.sq

column inst for a5
col START_SCN for 99999999999
col ROLLNAME for a12
col status for a7

ACCEPT SID PROMPT 'Enter SID: ' DEFAULT '%'

 

select 'OM_'||t.INST_ID inst,
      to_char(START_DATE, 'YYYY/MM/DD HH24:MI:SS') SDATE,
      START_SCN,
      DEPENDENT_SCN,
      ADDR,
      XIDUSN,
      '_SYSSMU'||t.xidusn||'$' ROLLNAME,
      XIDSLOT,
      XIDSQN,
      USED_UREC,
      USED_UBLK,
      ROUND(t.used_ublk * 8192 / 1024 / 1024, 2) used_bytes,
      STATUS
from gv$transaction t
where addr in (
    select taddr
    from gv$session
    where sid like '&&sid'
      )
order by inst, START_DATE , used_bytes desc
/

 

col segment_name for a12
col status for a15

 

select EXTENT_ID,
  FILE_ID,
  BLOCK_ID,
  segment_name,
  BLOCKS,
  BYTES/1024/1024 MB,
  STATUS
from dba_undo_extents
where segment_name in (
                        select '_SYSSMU'||t.xidusn||'$' ROLLNAME
                        from v$transaction t
                        where addr in (
                                        select taddr
                                        from gv$session
                                        where sid like '&&sid' )
                       )
order by STATUS                      
/

5.세션 Kill 이후 Undo 모니터링

 

1)smon_check 스크립트를 통해 해당 undo block의 rollback 진행 확인

select KTUXEUSN,  to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", KTUXESLT,KTUXESQN,KTUXERDBF,KTUXERDBB,KTUXESTA,KTUXESIZ
from x$ktuxe
where KTUXEUSN in (select segment_id from dba_rollback_segs where segment_name = '&&USN')
and KTUXESTA = 'ACTIVE'
;

 

2)완료 시간 추정(분)

declare
l_start number;
  l_end    number;
  begin
    select ktuxesiz into l_start from x$ktuxe where  KTUXEUSN=&&KTUXEUSN and KTUXESLT=&&KTUXESLT;
    dbms_lock.sleep(60);
    select ktuxesiz into l_end from x$ktuxe where  KTUXEUSN=&&KTUXEUSN and KTUXESLT=&&KTUXESLT;
    dbms_output.put_line('est Minutes:'|| round(l_end/(l_start -l_end),2));
  end;
  /

 

참고 :

select * from v$fast_start_servers ;

select * from v$fast_start_transactions; 

롤백빠르게 처리 : http://blog.naver.com/k65fac/40067011284

 

테이블 조회해도 관련 내용 확인 가능함

[출처] 세션 Kill이후 롤백 모니터링|작성자 루엘리