Oracle Dump
EXEM Knowledge Base
목차 |
[편집] Index Tree Dump
Index의 Tree 구조를 기록한다.
ALTER SESSION SET EVENTS ‘immediate trace name treedump level <index_id>';
----- 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)
위의 Dump 내용의 항목의 의미는 다음과 같다.
- Node Type: branch/leaf 노드 여부
- DBA: 블록 주소. 0x8405de2 138436066(16진수, 10진수)
- Relative Position: 부모 노드에서의 상대적인 위치(-1,0,1,2,...)
- Entry 수: nrow = 전체 Entry 수(Deleted Entry 포함), rrow = Real Entry 수(Deleted Entry 제외)
DBA를 Relative File Number와 Block Number로 변환하는 방법은 아래와 같다.
SQL> 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) order by object_id) loop
execute immediate
'alter session set events ''immediate trace name treedump level '||r_id.object_id||'''';
end loop;
end;
/
Partitioned Index인 경우에는 다음과 같이 특정 Partition을 지정할 수도 있다.
create or replace procedure tree_dump2(v_name in varchar2, v_part_name in varchar2)
is
v_obj_id number;
begin
for r_id in (select object_id from user_objects
where object_name = upper(v_name)
and subobject_name = upper(v_part_name)
order by object_id) loop
execute immediate
'alter session set events ''immediate trace name treedump level '||r_id.object_id||'''';
end loop;
end;
/
[편집] Heap Dump
Heap 구조를 기록한다. Heap과 관련된 Bug를 Troubleshooting할 때 많이 사용된다.
alter session set events 'immediate trace name heapdump level <level>'; oradebug dump heapdump <level>;
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
위의 레벨 값에 다음과 같은 Level Bit를 추가하면 최대 5개의 Subheap까지 같이 Dump를 수행해 준다.
- 0×10000000: 최대 5개의 Subheap을 같이 Dump
- 0x20000000: 최대 5개의 Subheap에 대해 자식 Subheap까지 같이 Dump(Recursive)
즉, 다음과 같이 Heapdump를 수행하면
oradebug dump heapdump 0x20000002
Top SGA와 함께 최대 5개의 Subheap에 대해 Recursive하게 Heap Dump를 출력해준다. 단, Subheap까지 덤프를 수행하면 덤프 파일의 크기가 대단히 커질 수 있다는 점에 유의한다. 하지만 대부분의 메모리 릭 문제는 Subheap 레벨에서 발생하므로 Subheap까지 덤프를 수행할 것을 권장한다.
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 "
Heap Dump의 결과 중 다음과 같이 ds(Heap Descriptor) 값을 가지는 경우에는 해당 Chunk가 SubHeap을 가리킨다는 것을 의미한다.
EXTENT 3551 addr=0A080004
Chunk a08000c sz= 48 free " "
Chunk a08003c sz= 65476 recreate "session heap " latch=00000000
ds a067600 sz=498960112 ct= 3552
SubHeap을 Dump하려면 다음과 같은 명령을 이용한다.
oradebug dump heapdump_addr <level> <address> SYS@ukja10> oradebug dump heapdump_addr 1 0xa067600
Level은 다음과 같은 의미를 지닌다.
- Level = 1: Data 구조만 출력
- Level = 2: Data 내용까지 출력
Level 2는 매우 많은 양을 출력하기 때문에 성능에 큰 악영향을 줄 수 있으므로 꼭 필요한 경우에만 사용해야 한다. 대부분의 경우 Level 1만으로도 원하는 결과를 얻을 수 있다.
[편집] Library Cache Dump
Library Cache 구조를 기록한다. SQL Cursor(Parent/Child)의 구조를 파악하고자 할 때 많이 사용된다.
ALTER SESSION SET EVENTS 'immediate trace name library_cache level 10'; -- 버전에 따라 16, 32 등의 값을 사용해야 완벽한 범위의 Library Cache Dump가 수행된다.
...
-- Parent Cursor
BUCKET 89106:
LIBRARY OBJECT HANDLE: handle=84ad91c0 mtx=0x84ad92f0(2) cdp=2
name=SELECT * FROM SHARE_TEST WHERE NAME = :x
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
[편집] HangAnalyze
Oracle Hang이 발생했을 경우, Hang 분석에 필요한 정보를 기록한다. 사용법은 다음과 같다.
SQL> connect /as sysdba SQL> oradebug setmypid SQL> oradebug hanganalyze <level> -- 예: 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와의 연관된 내용까지 분석하려면 다음과 같은 명령문을 사용해야 한다.
SQL> oradebug setinst all SQL> oradebug -g def hanganalyze 1
Hanganalyze에 생성된 덤프 파일의 예는 다음과 같다.
*** 2006-04-06 14:17:29.050
Open chains found:
Chain 1 : <cnode/sid/sess_srno/ospid/wait_event> :
<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 : <cnode/sid/sess_srno/ospid/wait_event> :
<1/156/1/14747/rdbms ipc message> -- <0/110/58858/enq: TC - contention>
Chain 3 : <cnode/sid/sess_srno/ospid/wait_event> :
<0/113/43663/single-task message> -- <0/145/49269/library cache pin>
Chain 4 : <cnode/sid/sess_srno/ospid/wait_event> :
<0/158/1/control file parallel write>
-- <1/131/33219/1095/enq: TC - contention>
[편집] 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'; alter session set events 'immediate trace name systemstate level 266';
또는
oradebug dump processstate 10; oradebug dump systemstate 10; -- oradebug dump systemstate 266; -- 10g부터(short stack 포함)
[편집] Error Stack Dump
Process의 Error Stack Dump를 수행한다. Stack 정보 외에 현재 실행 중인 PL/SQL, SQL 등의 정보가 함께 출력되기 때문에 디버깅 용도로 많이 사용된다.
alter session set events 'immediate trace name errorstack level 3'; alter session set events '1652 trace name errorstack level 3';
혹은
oradebug setospid <spid> oradebug dump errorstack 3
출력 예는 다음과 같다.
----- PL/SQL Call Stack -----
object line object
handle number name
2CD21218 169 package body SYS.DBMS_PIPE
2CD215EC 6 package body UKJA.PKG_SYNC
2CD215EC 17 package body UKJA.PKG_SYNC
2CD98678 1 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_ksedst+38 CALLrel _ksedst1+0 1 1
_ksedmp+898 CALLrel _ksedst+0 1
_ksdxfdmp+847 CALLreg 00000000 3
_ksdxcb+1481 CALLreg 00000000 887FB20 11 3 887FA80 887FAD0
_ssthreadsrgruncall CALLrel _ksdxcb+0 1
back+428
_OracleOradebugThre CALLrel _ssthreadsrgruncall 887FF84
adStart@4+795 back+0
7C80B710 CALLreg 00000000
00000000 VIRTUAL 7C93E4F4
7C80253D CALLrel 7C802550
_skgpwwait+124 CALL??? 00000000
_ksliwat+843 CALLrel _skgpwwait+0 817C564 B818088 3424CE88
4C4B40 0
_kslwaitns+24 CALLrel _ksliwat+0 1F4 1 E7 0 2CD20E44 1000
5265C00
_kskthbwt+159 CALLrel _kslwaitns+0 1F4 1 E7 0 2CD20E44 1000
5265C00
_kslwait+52 CALLrel _kskthbwt+0
_kkxpgetr+916 CALLrel _kslwait+0 1F4 E7 0 2CD20E44 1000
5265C00
_kkxpget+663 CALLrel _kkxpgetr+0 4BFFFE8 6 4FAEF10 1000
817C844 5265C00 3D
_pevm_icd_call_comm CALLreg 00000000 4F7CBE0 4 4D04828
on+722
...
----------------------------------------
Cursor#1(04F60C24) state=BOUND curiob=04F67C5C
curflg=46 fl2=0 par=00000000 ses=3432A014
sqltxt(2CD987BC)=select * from t1
where c1 = :b1 and c2 = :b2
and pkg_sync.f_wait_for_signal = 1
hash=59ca9b387f72d09556b17be170c424e0
parent=305B8518 maxchild=02 plk=31B534D4 ppn=n
cursor instantiation=04F67C5C
child#0(2CD98678) pcs=305B871C
clk=31BB2B50 ci=305BF038 pn=31B520F4 ctx=303E7E24
kgsccflg=0 llk[04F67C60,04F67C60] idx=0
xscflg=c0110676 fl2=d100008 fl3=42222008 fl4=0
sharing failure(s)=10
Bind bytecodes
Opcode = 2 Bind Twotask Scalar Sql In (may be out) Copy
oacdef = 305a03ac Offsi = 36, Offsi = 0
Opcode = 2 Bind Twotask Scalar Sql In (may be out) Copy
oacdef = 305a03d0 Offsi = 36, Offsi = 20
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=56 off=0
kxsbbbfp=04f78304 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=846 siz=0 off=24
kxsbbbfp=04f7831c bln=32 avl=01 flg=01
value="x"
Frames pfr 04F7AB28 siz=2288 efr 04F7AB70 siz=1916
Cursor frame dump
enxt: 3.0x00000044 enxt: 2.0x00000028 enxt: 1.0x00000710
pnxt: 2.0x00000004 pnxt: 1.0x00000170
kxscphp 04F6608C siz=1000 inu=0 nps=744
kxscehp 04F66194 siz=1000 inu=0 nps=160
...
[편집] ASH Dump
SGA의 ASH(Active Session History) 정보를 기록한다.
alter session set events 'immediate trace name ashdump level <level>'; oradebug dump ashdump <level>;
Level은 과거 몇 분간의 이력을 기록할 것인가를 지정한다. 만일 level 10으로 지정하면 과거 10분간의 정보를 기록하게 된다. ASH 정보는 Active Session의 목록을 저장하기 때문에 Oracle Hang과 같은 현상이 발생했을 때 사후 분석 용도로 유용하다.
트레이스 파일에 기록되는 덤프 내용의 예는 다음과 갈다.
Processing Oradebug command 'dump ashdump 10' ASH dump <<<active session="" history="" -="" process="" trace="" dump="" header="" begin="">>> **************** SCRIPT TO IMPORT **************** ------------------------------------------ Step 1: Create destination table <ashdump> ------------------------------------------ CREATE TABLE ashdump AS SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0 ---------------------------------------------------------------- Step 2: Create the SQL*Loader control file <ashldr.ctl> as below ---------------------------------------------------------------- load data infile * "str '\n####\n'" append into table ashdump fields terminated by ',' optionally enclosed by '"' ( SNAP_ID CONSTANT 0 , DBID , INSTANCE_NUMBER , SAMPLE_ID , SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME ,'MM-DD-YYYY HH24:MI:SSXFF')" , SESSION_ID , SESSION_SERIAL# , SESSION_TYPE , USER_ID , SQL_ID , SQL_CHILD_NUMBER , SQL_OPCODE , FORCE_MATCHING_SIGNATURE , TOP_LEVEL_SQL_ID , TOP_LEVEL_SQL_OPCODE , SQL_PLAN_HASH_VALUE , SQL_PLAN_LINE_ID , SQL_PLAN_OPERATION# , SQL_PLAN_OPTIONS# , SQL_EXEC_ID , SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START" , PLSQL_ENTRY_OBJECT_ID , PLSQL_ENTRY_SUBPROGRAM_ID , PLSQL_OBJECT_ID , PLSQL_SUBPROGRAM_ID , QC_INSTANCE_ID , QC_SESSION_ID , QC_SESSION_SERIAL# , EVENT_ID , SEQ# , P1 , P2 , P3 , WAIT_TIME , TIME_WAITED , BLOCKING_SESSION , BLOCKING_SESSION_SERIAL# , CURRENT_OBJ# , CURRENT_FILE# , CURRENT_BLOCK# , CURRENT_ROW# , CONSUMER_GROUP_ID , XID , REMOTE_INSTANCE# , TIME_MODEL , SERVICE_HASH , PROGRAM , MODULE , ACTION , CLIENT_ID ) --------------------------------------------------- Step 3: Load the ash rows dumped in this trace file --------------------------------------------------- sqlldr userid/password control=ashldr.ctl data=<this_trace_filename> errors=1000000 --------------------------------------------------- <<<active session="" history="" -="" process="" trace="" dump="" header="" end="">>> <<<active session="" history="" -="" process="" trace="" dump="" begin="">>> #### 58646642,1,12519562,"04-26-2010 09:44:01.822000000",160,1,2,0, "",0,0,0,"",0,0,0,0,0, 0,"",0,0,0,0,0,0,0, 3213517201,9858,0,3,1,0,69788,4294967295,0, 4294967295,0,0,0,0,,0,0,165959219, "ORACLE.EXE (CKPT)","", "","" #### 58646642,1,12519486,"04-26-2010 09:42:45.808000000",160,1,2,0, "",0,0,0,"",0,0,0,0,0, 0,"",0,0,0,0,0,0,0, 3213517201,9767,1,1,1,0,38825,4294967295,0, 4294967295,0,0,0,0,,0,0,165959219, "ORACLE.EXE (CKPT)","", "","" #### 58646642,1,12519416,"04-26-2010 09:41:35.770000000",160,1,2,0, "",0,0,0,"",0,0,0,0,0, 0,"",0,0,0,0,0,0,0, 4078387448,9683,3,3,3,0,11083,4294967295,0, 4294967295,0,0,0,0,,0,0,165959219, "ORACLE.EXE (CKPT)","", "","" #### 58646642,1,12519384,"04-26-2010 09:41:03.774000000",163,1,2,0, "",0,0,0,"",0,0,0,0,0, 0,"",0,0,0,0,0,0,0, 3176176482,40612,5,1,1000,999888,0,4294967291,0, 4294967295,0,0,0,0,,0,0,165959219, "ORACLE.EXE (DIA0)","", "","" #### 58646642,1,12519304,"04-26-2010 09:39:43.719000000",160,1,2,0, "",0,0,0,"",0,0,0,0,0, 0,"",0,0,0,0,0,0,0, 3213517201,9551,1,1,1,0,38798,4294967295,0, 4294967295,0,0,0,0,,0,0,165959219, "ORACLE.EXE (CKPT)","", "","" #### 58646642,1,12519265,"04-26-2010 09:39:04.663000000",163,1,2,0, "",0,0,0,"",0,0,0,0,0, 0,"",0,0,0,0,0,0,0, 3176176482,40493,5,1,1000,999941,0,4294967291,0, 4294967295,0,0,0,0,,0,0,165959219, "ORACLE.EXE (DIA0)","", "","" #### 58646642,1,12519183,"04-26-2010 09:37:42.554000000",160,1,2,0, "",0,0,0,"",0,0,0,0,0, 0,"",0,0,0,0,0,0,0, 3213517201,9406,0,1,1,0,54077,4294967295,0, 4294967295,0,0,0,0,,0,0,165959219, "ORACLE.EXE (CKPT)","", "","" #### <<<active session="" history="" -="" process="" trace="" dump="" end="">>> *** 2010-04-26 09:45:51.625 Oradebug command 'dump ashdump 10' console output: <none> </none></active></active></active></this_trace_filename></ashldr.ctl></ashdump></active>
더욱 자세한 내용은 Oracle Hang 상황에서 액티브 세션 목록 얻기을 참조한다.