[Oracle] Oracle Dump

1. Index Tree Dump 

Index의 Tree 구조를 기록한다. 

 

설정 방법 

ALTER SESSION SET EVENTS ‘immediate trace name treedump level <index_id>';

 

Dump 내용 

----- begin tree dump
branch: 0x8405dde 138436062 (0: nrow: 3, level: 3)
branch: 0xdc11022 230756386 (-1: nrow: 219, level: 2)
branch: 0x8405f15 138436373 (-1: nrow: 138, level: 1)
leaf: 0x8405ddf 138436063 (-1: nrow: 21 rrow: 21)
leaf: 0x8405de0 138436064 (0: nrow: 18 rrow: 13)
leaf: 0x8405de2 138436066 (1: nrow: 15 rrow: 15)

 

• Node Type : branch/leaf 노드 여부 
• DBA : 블록 주소, 0x8405de0 138436064 (16진수, 10진수)
• Relative Posision : 부모 노드에서 상대적인 위치 ( -1, 0, 1, 2) 
• Entry 수 : nrow = 전체 Entry 수 (Deleted Entry 포함), rrow = Real Entry 수 ( Deleted Entry 제외 ) 

DBA를 Relative File Number와 Block Number로 변환하는 방법은 아래와 같다. 

 

SELECT dbms_utility.data_block_address_file(138436066) as file_no --십진수
       dbms_utility.data_block_address_block(138436066) as block_no --십진수 
FROM dual;

 

Index Tree Dump를 수행하는 Procedure를 다음과 같이 만들어서 사용하면 편리하다. 

 

CREATE OR REPLACE PROCEDURE TREE_DUMP ( V_NAME IN VARCHAR2 ) 
IS
	V_OBJ_ID_NUMBER; 
BEGIN 

	FOR R_ID IN ( SELECT OBJECT_ID 
                    FROM ALL_OBJECTS 
                   WHERE OBJECT_NAME = UPPER(V_NAME) O
                   ORDER BY OBJECT_ID 
                ) 
    LOOP
    
    	EXECUTE IMMEDIATE
        'ALTER SESSION SET EVENTS "IMMEDIATE TRACE NAME TREEEDUMP LEVEL '||r_id.object_id||'"';
    END LOOP;
END;
/

 

 

2. Heap Dump

Heap 구조를 기록한다. Heap과 관련된 Bug를 Troublesooting 할 때 많이 사용된다. 

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME HEAPDUMP LEVEL';
oradebug dump heapdump;

 

Level 값의 의미는 다음과 같다.

• Level = 1 : Top PGA
• Level = 2 : Top SGA
• Level = 3 : Top UGA
• Level = 8 : Current Call
• Level = 16 : User Call
• Level = 32 : Large Pool 

Heap Dump는 다음과 같은 형태를 가진다.

******************************************************
HEAP DUMP heap name="pga heap" desc=09701D30
extent sz=0x206c alt=92 het=32767 rec=0 flg=2 opc=2
parent=00000000 owner=00000000 nex=00000000 xsz=0xfff8
EXTENT 0 addr=08520008
Chunk 8520010 sz= 18880 perm "perm " alo=8020
Chunk 85249d0 sz= 2824 free " "
Chunk 85254d8 sz= 8036 freeable "Alloc environm " ds=08427B34
Chunk 852743c sz= 20572 freeable "Fixed Uga "
Chunk 852c498 sz= 180 freeable "ldm context "
Chunk 852c54c sz= 180 freeable "ldm context "
Chunk 852c600 sz= 180 freeable "ldm context "
Chunk 852c6b4 sz= 180 freeable "ldm context "
Chunk 852c768 sz= 180 freeable "ldm context "
Chunk 852c81c sz= 180 freeable "ldm context "

 

3. Library Cache Dump 

Library Cache 구조를 기록한다. SQL Cursor(Parent/Child)의 구조를 파악하고자 할 때 많이 사용된다.

 

ALTER SESSION SET EVENT 'immediate trace name library_cache level 10';

 

...
-- Parent Cursor
BUCKET 89106:
LIBRARY OBJECT HANDLE: handle=84ad91c0 mtx=0x84ad92f0(2) cdp=2
name=SELECT * FROM SHARE_TEST WHERE NAME = 😡
hash=8f5a0aa420c69c69f3dc950037b95c12 timestamp=11-09-2007 14:17:12
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=3 hpc=0000 hlc=0000
lwt=0x84ad9268[0x84ad9268,0x84ad9268] ltm=0x84ad9278[0x84ad9278,0x84ad9278]
pwt=0x84ad9230[0x84ad9230,0x84ad9230] ptm=0x84ad9240[0x84ad9240,0x84ad9240]
ref=0x84ad9298[0x84ad9298,0x84ad9298] lnd=0x84ad92b0[0x84ad92b0,0x84ad92b0]
LIBRARY OBJECT: object=833e5518
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16 <-- Child Cursor 위치
child# table reference handle
------ -------- --------- --------
0 833010c0 83300d30 858bd9b0
1 833010c0 83300fc0 8212fdf8
-- Child Cursor
LIBRARY OBJECT HANDLE: handle=858bd9b0 mtx=0x858bdae0(0) cdp=0
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=3 hpc=0000 hlc=0000
lwt=0x858bda58[0x858bda58,0x858bda58] ltm=0x858bda68[0x858bda68,0x858bda68]
pwt=0x858bda20[0x858bda20,0x858bda20] ptm=0x858bda30[0x858bda30,0x858bda30]
ref=0x858bda88[0x83300d30,0x83300d30] lnd=0x858bdaa0[0x858bdaa0,0x858bdaa0]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
83300d30 0 CHL[02]
LIBRARY OBJECT: object=833008f0
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 7f751c70 7f751b08 84a04420 14 DEP[01]
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
00000000 37000000 00020000 00000000
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
84a04420 84a04420
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 85beb878 83300a48 I/-/A/-/- 0 NONE 00 <-- Basic Info
6 7f7518e0 845c7780 I/-/A/-/- 0 NONE 00 <-- Execution Plan

 

4. HangAnalyze 

Oracle Hang이 발생했을 경우, Hang 분석에 필요한 정보를 기록한다. 

sqlplus / as sysdba
oradebug setmypid
oradebug hanganalyze 'level' -- ex: oradebug hanganalyze 3

 

Level에 따른 출력 내용은 다음과 같다.

• 10 - Dump all processes (IGN state)
• 5 - Level 4 + Dump all Processes involved in wait chains(NLEAF state)
• 4 - Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF, LEAF_NW, IGN_DMP state)
• 3 - Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
• 1-2 Only HANGANALYZE output, no process dump at all 

RAC에서 다른 Instance와의 연관된 내용까지 분석하려면 다음과 같은 명령문을 사용해야 한다. 

oradebug setinst all
oradebug g def hanganalyze 1

 

Hangalnalyze에 생성된 덤프 파일의 예는 다음과 같다. 

 

*** 2006-04-06 14:17:29.050
Open chains found:
Chain 1 : :
<0/118/36478/1093/PL/SQL lock timer>
-- <1/132/29546/1113/enq: TX - row lock contention>
-- <1/127/16507/1041/enq: TX - row lock contention>
Chain 2 : :
<1/156/1/14747/rdbms ipc message> -- <0/110/58858/enq: TC - contention>
Chain 3 : :
<0/113/43663/single-task message> -- <0/145/49269/library cache pin>
Chain 4 : :
<0/158/1/control file parallel write>
-- <1/131/33219/1095/enq: TC - contention>

 

5. System/Process State Dump

system이나 Process의 현재 상태에 대한 상세한 정보를 파일에 출력한다. 

alter session set events 'immediate trace name processstate level 10';
alter session set events 'immediate trace name systemstate level 10';

또는 

oradebug dump processtae 10;
oradebug dump systemstate 10;

 

참조

https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=234774