|
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
|