반응형

Undo 상태 모니터링 쿼리

  

set line 250

set pagesize 300

col segment_name format a20

col extent_info format a100

 

SELECT ue.usn

       , ue.segment_name

       , rs.extents

       , rs.rssize/1024/1024 AS "RSSIZE(mb)"

       , rs.hwmsize/1024/1024 AS "HWM(mb)"

       , rs.shrinks

       , rs.xacts

       , rs.status

       , ue.extent_info

FROM  (SELECT usn

             , segment_name

             , listagg( extent_id || ' : ' || status , ' / ' ) within group ( order by extent_id )  as extent_info

       FROM   dba_undo_extents , v$rollname

       WHERE    dba_undo_extents.segment_name = v$rollname.name

       GROUP BY usn , segment_name  ) ue ,

       v$rollstat rs

WHERE  rs.usn = ue.usn

AND    rs.extents <= 200

UNION ALL

SELECT ue.usn

       , ue.segment_name

       , rs.extents

       , rs.rssize/1024/1024 AS "RSSIZE(mb)"

       , rs.hwmsize/1024/1024 AS "HWM(mb)"

       , rs.shrinks

       , rs.xacts

       , rs.status

       , ue.extent_info

FROM  (SELECT usn

             , segment_name

             , 'Extents count over 200'  as extent_info

       FROM   dba_undo_extents , v$rollname

       WHERE    dba_undo_extents.segment_name = v$rollname.name

       GROUP BY usn , segment_name  ) ue ,

       v$rollstat rs

WHERE  rs.usn = ue.usn

AND    rs.extents > 200

ORDER BY xacts desc ;

 

Undo 사용하는 세션 확인 쿼리

  

SELECT a.sid , a.serial# , a.status , a.schemaname , a.program , b.xidusn usn , b.start_uext extent , b.used_ublk usedexts , b.start_date

FROM   v$session a , v$transaction b

WHERE  a.saddr = b.ses_addr ;

 

 

# 시나리오


SCOTT 세션에서 DML 발생 COMMIT 하지 않은 상태에서 UNDO 사용량 모니터링.

 

 

SCOTT (SID=14)

SCOTT (SID=145)

   INSERT INTO dept_test

   SELECT * FROM dept ;

   INSERT INTO dept_test

   SELECT * FROM dept ;

 

UNDO 사용량 모니터링

 

       USN SEGMENT_NAME                      EXTENTS RSSIZE(mb)    HWM(mb)

---------- ------------------------------ ---------- ---------- ----------

   SHRINKS      XACTS STATUS        

---------- ---------- ---------------

EXTENT_INFO                                                                    

--------------------------------------------------------------------------------

         5 _SYSSMU5_1527469038$                   27 32.1171875 32.1171875

         0          1 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : EXPIRE

D / 6 : EXPIRED / 7 : EXPIRED / 8 : EXPIRED / 9 : EXPIRED / 10 : EXPIRED / 11 :

EXPIRED / 12 : EXPIRED / 13 : EXPIRED / 14 : EXPIRED / 15 : EXPIRED / 16 : EXPIR

ED / 17 : EXPIRED / 18 : EXPIRED / 19 : EXPIRED / 20 : EXPIRED / 21 : EXPIRED /

22 : EXPIRED / 23 : EXPIRED / 24 : EXPIRED / 25 : ACTIVE / 26 : EXPIRED        

                                                                               

         2 _SYSSMU2_2232571081$                   30 28.1171875 28.1171875

         0          1 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : EXPIRE

D / 6 : EXPIRED / 7 : EXPIRED / 8 : EXPIRED / 9 : EXPIRED / 10 : EXPIRED / 11 :

EXPIRED / 12 : EXPIRED / 13 : EXPIRED / 14 : EXPIRED / 15 : EXPIRED / 16 : EXPIR

ED / 17 : EXPIRED / 18 : EXPIRED / 19 : EXPIRED / 20 : EXPIRED / 21 : EXPIRED /

22 : EXPIRED / 23 : EXPIRED / 24 : EXPIRED / 25 : EXPIRED / 26 : EXPIRED / 27 :

EXPIRED / 28 : ACTIVE / 29 : EXPIRED                                           

                                                                               

 

       USN SEGMENT_NAME                      EXTENTS RSSIZE(mb)    HWM(mb)

---------- ------------------------------ ---------- ---------- ----------

   SHRINKS      XACTS STATUS        

---------- ---------- ---------------

EXTENT_INFO                                                                    

--------------------------------------------------------------------------------

         3 _SYSSMU3_2097677531$                   15 13.1171875 13.1171875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : EXPIRE

D / 6 : EXPIRED / 7 : EXPIRED / 8 : EXPIRED / 9 : EXPIRED / 10 : EXPIRED / 11 :

UNEXPIRED / 12 : EXPIRED / 13 : EXPIRED / 14 : EXPIRED                         

                                                                               

         4 _SYSSMU4_1152005954$                    8  5.1796875  5.1796875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : UNEXPI

RED / 6 : EXPIRED / 7 : EXPIRED                                                

                                                                               

        10 _SYSSMU10_3550978943$                  51 49.1171875 49.1171875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : EXPIRE

D / 6 : EXPIRED / 7 : EXPIRED / 8 : EXPIRED / 9 : EXPIRED / 10 : EXPIRED / 11 :

EXPIRED / 12 : EXPIRED / 13 : EXPIRED / 14 : EXPIRED / 15 : EXPIRED / 16 : EXPIR

ED / 17 : EXPIRED / 18 : EXPIRED / 19 : EXPIRED / 20 : EXPIRED / 21 : EXPIRED /

 

       USN SEGMENT_NAME                      EXTENTS RSSIZE(mb)    HWM(mb)

---------- ------------------------------ ---------- ---------- ----------

   SHRINKS      XACTS STATUS        

---------- ---------- ---------------

EXTENT_INFO                                                                    

--------------------------------------------------------------------------------

22 : EXPIRED / 23 : EXPIRED / 24 : EXPIRED / 25 : EXPIRED / 26 : EXPIRED / 27 :

EXPIRED / 28 : EXPIRED / 29 : EXPIRED / 30 : EXPIRED / 31 : EXPIRED / 32 : EXPIR

ED / 33 : EXPIRED / 34 : EXPIRED / 35 : EXPIRED / 36 : EXPIRED / 37 : EXPIRED /

38 : EXPIRED / 39 : EXPIRED / 40 : EXPIRED / 41 : EXPIRED / 42 : EXPIRED / 43 :

EXPIRED / 44 : EXPIRED / 45 : EXPIRED / 46 : EXPIRED / 47 : EXPIRED / 48 : EXPIR

ED / 49 : UNEXPIRED / 50 : EXPIRED                                             

                                                                               

         6 _SYSSMU6_2443381498$                    8  6.1171875  6.1171875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : UNEXPI

RED / 6 : EXPIRED / 7 : EXPIRED                                                

                                                                               

         7 _SYSSMU7_3286610060$                    8  6.1171875  6.1171875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : EXPIRE

D / 6 : UNEXPIRED / 7 : EXPIRED                                                

                                                                               

 

       USN SEGMENT_NAME                      EXTENTS RSSIZE(mb)    HWM(mb)

---------- ------------------------------ ---------- ---------- ----------

   SHRINKS      XACTS STATUS        

---------- ---------- ---------------

EXTENT_INFO                                                                    

--------------------------------------------------------------------------------

         8 _SYSSMU8_2012382730$                    5 10.1171875 10.1171875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : UNEXPIRED          

                                                                               

         9 _SYSSMU9_1424341975$                    7  5.1171875  5.1171875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : UNEXPIRED / 4 : UNEXPIRED / 5 : EX

PIRED / 6 : EXPIRED                                                            

                                                                               

         1 _SYSSMU1_3780397527$                    6  4.1171875  4.1171875

         0          0 ONLINE        

0 : EXPIRED / 1 : EXPIRED / 2 : EXPIRED / 3 : EXPIRED / 4 : EXPIRED / 5 : UNEXPI

RED                                                                            

                                                                               

 

10 rows selected.

 

5번과 2 undosegment active transaction 1개씩 있음을 확인 있으며, 5 undo segment 25 extent 활성 트랜잭션 데이터가 있으며, 2 undo segment 28 Extent 활성 트랜잭션 데이터가 있음을 확인 있음.

 

그럼 각각의 활성 Extent 사용하는 세션은 어떤 세션들인지 확인하기 위하여 언두 사용 세션 확인 쿼리를 수행해 보면

시나리오대로 SCOTT 세션에서 사용하고 있음을 확인 있다.

 

UNDO 사용하는 세션 모니터링

  

      SID     SERIAL#   STATUS   SCHEMANAME        PROGRAM               USN      EXTENT   USEDEXTS START_DATE

---------- ---------- -------- -------------- -------------------- ---------- ---------- ---------- -----------

       145       1081 INACTIVE  SCOTT         Toad.exe                      2         28          1 2018-12-13

        14         17 INACTIVE  SCOTT         Toad.exe                      5         25          1 2018-12-13

 

 


반응형

+ Recent posts