Oracle Dump

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] 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 상황에서 액티브 세션 목록 얻기을 참조한다.