오라디버그는 데이터베이스 내부 정보를 얻을 때 유용하게 사용된다.
1. oradebug dump
덤프 수행
[ Syntax ]
oradebug dump <dump_name> <level> [addr]
[ Parameter ]
<dump_name>
<level>
[<addr>]
SQL> oradebug dumplist ( Oradebug 에서 덤프할 수 있는 Named Event)
EVENTS
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
HANGANALYZE
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
CROSSIC
CONTEXTAREA
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH ……….
2. oradebug dump systemstate
시스템 상태 정보 덤프
EX)
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug setinst all
Statement processed.
SQL> oradebug -g def dump systemstate 10
Statement processed.
3. oradebug dump processstate
프로세스 상태 정보 덤프
EX)
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug setinst all
Statement processed.
SQL> oradebug -g def dump systemstate 10
Statement processed.
4. oradebug dump errorstack
error stack 덤프
Dump Level Dump Contains
0 dump error buffer
1 level 0 + call stack
2 level 1 + process state objects
3 level 2 + context area
EX)
SQL> oradebug setospid 13446
Oracle pid: 12, Unix process pid: 13446, image: oracle@apollo (TNS V1-V3)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump errorstack 3
Statement processed.
5. oradebug dump controlf
데이터파일과 콘트롤파일 헤더를 덤프하는 것으로 리커버리나 데이터베이스 오픈시 파일들 상태가 일치하지 않는다는
ORA-1113, ORA-376 등의 에러가 발생하는 경우 CONTROLF 와 FILE_HDRS 를 사용하여 각 파일의 헤더를 덤프하여 파일들의 상태와 업데이트 시간을 비교
Dump Level Dump Contains
1 only the file header
2 just the file header, the database info record, and checkpoint progress records
3 all record types, but just the earliest and latest records for circular reuse record types
4 as above, but includes the 4 most recent records for circular reuse record types
5+ as above, but the number of circular reuse records included doubles with each level
6. Memory Dumps
버퍼캐시 덤프 수행
Dump Level Dump Contains
1 dump the buffer headers only
2 include the cache and transaction headers from each block
3 include a full dump of each block
4 dump the working set lists and the buffer headers and the cache header for each block
5 include the transaction header from each block
6 include a full dump of each block
7. oradebug dump library_cache
라이브러리 캐시 통계 덤프
Dump Level Dump Contains
1 dump libracy cache statistics
2 also include a hash table
3 level 2 + dump of the library object handles
4 Level 3 + dump of the heap
EX)
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump library_cache 2
Statement processed.
8. oradebug dump heapdump
특정 오라클 프로세스의 메모리가 지속적으로 증가만하거나 한번 증가한 후 줄어들지 않는 경우 메모리
정보를 덤프하여,
어느 메모리 세그먼트 부분이 많이 차지하는지 분석하기 위해 HEAPDUMP 를 수행
Dump Level Dump Contains
1 include PGA heap
2 include Shared Pool
4 include UGA heap
8 include CGA heap
16 include Top CGA
32 include Large Pool
9. oradebug dump heapdump_addr
Dump Level Dump Contains
1 dump structure
2 also include contents
EX)
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.
—————— part of the trace file ——————–
*** SESSION ID:(7.14111) 2003-09-03 13:01:21.412
******************************************************
HEAP DUMP heap name=”sga heap” desc=0x80000030
extent sz=0xfc4 alt=48 het=32767 rec=9 flg=2 opc=0
parent=0 owner=0 nex=0 xsz=0x3d2bdf4
EXTENT 0
Chunk b5815ff8 sz= 41699524 perm “perm ” alo=19395672
Chunk b7fda8bc sz= 887380 free ” ”
Chunk b80b3310 sz= 560 freeable “library cache ” ds=b80b5a0c
Chunk b80b3540 sz= 2588 freeable “sql area ” ds=b80b5898
Chunk b80b3f5c sz= 732 freeable “sql area ” ds=b80ba69c
Open the trace file and look for the ds (descriptor address).Convert hex to decimal and do the
dump.
SQL> oradebug dump heapdump_addr 1 3087751692
————————— part of the trace file generated using command above ————-
Statement processed.
*** 2003-09-03 13:12:35.330
*** SESSION ID:(8.20679) 2003-09-03 13:12:35.322
HEAP DUMP heap name=”library cache” desc=0xb80b5a0c
extent sz=0x224 alt=32767 het=8 rec=9 flg=2 opc=0
parent=80000030 owner=b80b5804 nex=0 xsz=0x224
EXTENT 0
Chunk b80b3324 sz= 464 perm “perm ” alo=176
Chunk b80b34f4 sz= 76 freeable “kgltbtab ”
EXTENT 1
Chunk b80b55b4 sz= 500 perm “perm ” alo=500
Chunk b80b57a8 sz= 40 free ” ”
EXTENT 2
Chunk b80b57f4 sz= 244 perm “perm ” alo=244
Chunk b80b58e8 sz= 52 free ” ”
Chunk b80b591c sz= 76 freeable “kgltbtab ”
Chunk b80b5968 sz= 76 freeable “kgltbtab ”
10 . INDEX DUMP
oradebug dump treedump
인덱스 구조를 덤프
EX)
select object_id from sys.dba_objects
where owner = upper(‘&Owner’) and
object_name = upper(‘&IndexName’);
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump treedump 40
Statement processed.