오라클 성능 트러블슈팅의 기초 (2012년)
oradebug 0 0 47,826

by 구루비스터디 oradebug hanganalyze [2018.09.27]


oradebug

  • oradebug 는 말 그대로 Oracle 을 디버깅 하는 용도로 고안된 툴
  • 프로세스 바인딩 : 특정 서버 프로세스에 바인딩을 해야 함.
  • SYSDBA 권한 필요
  • 바인딩 대상 :
    1. 서버 프로세스 ( 1. OS 프로세스 아이디(V$PROCESS.SPID)
    2. 오라클 프로세스 아이디((V$PROCESS.PID) )
    3. 백그라운드 프로세스
  • 진단 이벤트 : 특정 이벤트에 대한 활성화 / 비활성화 ex) ORA-04030
  • 11g 특성 : 보다 직관적인 명령어,
  • 기타 : 파일명(tracfile_name), 파일 사이즈(unlimit), 덤프 목록(dumplist)


oradebug 기본



EX )

SQL> show user
USER is "SYS"

SQL> col sid new_value sid
SQL>  select distinct sid from v$mystat where rownum < 2 ;

       SID
----------
        30



SQL> col spid new_value ospid
SQL> col pid new_value orapid
SQL> select pid, spid
  2  from v$process
  3  where addr = ( select paddr from v$session where sid = &sid ) ;
old   3: where addr = ( select paddr from v$session where sid = &sid )
new   3: where addr = ( select paddr from v$session where sid =         30 )

       PID SPID
---------- ------------------------------------------------------------------------
        19 8625



SQL> oradebug setospid &ospid
Oracle pid: 19, Unix process pid: 8625, image: oracle@IIG.com (TNS V1-V3)


SQL> oradebug setorapid &orapid
Oracle pid: 19, Unix process pid: 8625, image: oracle@IIG.com (TNS V1-V3)


SQL> oradebug setmypid
Statement processed.



SQL> oradebug setorapname smon
Oracle pid: 13, Unix process pid: 7972, image: oracle@IIG.com (SMON)


SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/iignf/IIGNF/trace/IIGNF_ora_8625.trc


SQL> oradebug unlimit
Statement processed.



oradebug dumplist



SQL> oradebug dumplist
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
CROSSIC
CONTEXTAREA
HANGDIAG_HEADER
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
REALFREEDUMP
FLUSH_JAVA_POOL
POOL_SIMULATOR
PGA_DETAIL_GET
PGA_DETAIL_DUMP
PGA_DETAIL_CANCEL
PGA_SUMMARY
MODIFIED_PARAMETERS
EVENT_TSM_TEST
ERRORSTACK
CALLSTACK
TEST_STACK_DUMP
TEST_GET_CALLER
RECORD_CALLSTACK
EXCEPTION_DUMP
BG_MESSAGES
ENQUEUES
KSTDUMPCURPROC
KSTDUMPALLPROCS
KSTDUMPALLPROCS_CLUSTER
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER
LDAP_USER_DUMP
LDAP_KERNEL_DUMP
DUMP_ALL_OBJSTATS
DUMPGLOBALDATA
HANGANALYZE
HANGANALYZE_PROC
HANGANALYZE_GLOBAL
GES_STATE
OCR
CSS
CRS
SYSTEMSTATE_GLOBAL
GIPC
MMAN_ALLOC_MEMORY
MMAN_CREATE_DEF_REQUEST
MMAN_CREATE_IMM_REQUEST
MMAN_IMM_REQUEST
DUMP_ALL_COMP_GRANULE_ADDRS
DUMP_ALL_COMP_GRANULES
DUMP_ALL_REQS
DUMP_TRANSFER_OPS
DUMP_ADV_SNAPSHOTS
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FLUSH_CACHE
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
PIN_RANDOM_BLOCKS
SET_NBLOCKS
CHECK_ROREUSE_SANITY
DUMP_PINNED_BUFFER_HISTORY
KCBO_OBJ_CHECK_DUMP
KCB_WORKING_SET_DUMP
REDOLOGS
ARCHIVE_ERROR
LOGHIST
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_READ_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
UPDATE_BLOCK0_FORMAT
TR_SET_BLOCK
TR_SET_ALL_BLOCKS
TR_SET_SIDE
TR_CRASH_AFTER_WRITE
TR_READ_ONE_SIDE
TR_CORRUPT_ONE_SIDE
TR_RESET_NORMAL
TEST_DB_ROBUSTNESS
LOCKS
GC_ELEMENTS
FILE_HDRS
KRB_CORRUPT_INTERVAL
KRB_CORRUPT_SIZE
KRB_CORRUPT_REPEAT
KRB_CORRUPT_OFFSET
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_FAIL_INPUT_FILENO
KRB_SIMULATE_NODE_AFFINITY
KRB_TRACE
KRB_BSET_DAYS
KRB_SET_TIME_SWITCH
KRB_OVERWRITE_ACTION
KRB_CORRUPT_SPHEADER_INTERVAL
KRB_CORRUPT_SPHEADER_REPEAT
KRB_CORRUPT_SPBITMAP_INTERVAL
KRB_CORRUPT_SPBITMAP_REPEAT
KRB_CORRUPT_SPBAD_INTERVAL
KRB_CORRUPT_SPBAD_REPEAT
KRB_UNUSED_OPTION
KRBMRSR_LIMIT
KRBMROR_LIMIT
KRBABR_TRACE
KRDRSBF
KRC_TRACE
KRA_OPTIONS
KRA_TRACE
FBTAIL
FBINC
FBHDR
FLASHBACK_GEN
KTPR_DEBUG
DUMP_TEMP
DROP_SEGMENTS
TEST_SPACEBG
TREEDUMP
LONGF_CREATE
KDLIDMP
ROW_CACHE
LIBRARY_CACHE
LIBRARY_CACHE_OBJECT
CURSORDUMP
CURSORTRACE
CURSOR_STATS
XS_SESSION_STATE
SHARED_SERVER_STATE
LISTENER_REGISTRATION
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
KXFRHASHMAP
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
SELFTESTASM
ASMDISK_ERR_ON
ASMDISK_READ_ERR_ON
ASMDISK_ERR_OFF
IOERREMUL
IOERREMULRNG
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
MMON_TEST
ATSK_TEST
HM_FW_TRACE
HM_FDG_VERS
IR_FW_TRACE
KSDTRADV_TEST



ordebug 기타




SQL> show user
USER is "SYS"


SQL> oradebug setmypid
Statement processed.


SQL> oradebug dump heapdump 1
Statement processed.


SQL> alter session set events 'immediate trace name heapdump level 1';

Session altered.

Elapsed: 00:00:00.05

SQL> oradebug dump processstate 266
Statement processed



ordebug 진단 이벤트




SQL> oradebug event 10046 trace name context forever, level 12 ;
Statement processed.
SQL> oradebug event 10046 trace name context off ;
Statement processed.
==

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set events '10046 trace name context off';

Session altered.



SQL> oradebug setmypid
Statement processed.

SQL> oradebug event sql_trace wait=true, bind=true ;
Statement processed.
SQL> oradebug event sql_trace off ;
Statement processed.



SQL> col sql_id new_value sql_id
SQL> select sql_id from v$sqlarea where rownum = 1 ;

SQL_ID
---------------------------------------
1fkh93md0802n

Elapsed: 00:00:00.03


SQL> oradebug setmypid
Statement processed.


SQL> oradebug event sql_trace [sql:&sql_id] wait=true ;
Statement processed.
SQL> oradebug event sql_trace [sql:&sql_id] off ;
Statement processed.


SQL> define sql_id2 = &sql_id
SQL> oradebug event sql_trace [sql:&sql_id|&sql_id2] wait=true ;
Statement processed.
SQL> define
DEFINE _DATE           = "29-FEB-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "IIGNF" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE SQL_ID          = "1fkh93md0802n" (CHAR)
DEFINE SQL_ID2         = "1fkh93md0802n" (CHAR)






SQL> oradebug event 4030 trace name heapdump level 0x20000001
Statement processed.
SQL> oradebug event 4030 trace name context off
Statement processed.


SQL> alter system set events '4030 trace name heapdump level 0x20000001';

System altered.

SQL> alter system set events '4030 trace name context off';

System altered.



hanganalyze 샘플


cat 01_hanganalyze_lib.sh 
ORACLE_HOME=/app/oracle/product/10.2.0; export ORACLE_HOME
echo "====================================== hanganalyze ====================================="
#sqlplus -s -prelim "/as sysdba"<<EOF 
sqlplus -s "/as sysdba"<<EOF
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
oradebug dump systemstate 10
!echo " >>> Wait !!! First HANGANALYZE has been taken and next one is after 30 seconds";sleep 30
oradebug hanganalyze 3
oradebug dump systemstate 10
!echo " >>> Wait !!! First HANGANALYZE has been taken and next one is after 30 seconds";sleep 30
oradebug hanganalyze 3
oradebug dump systemstate 10
EOF
echo " >>>>>>> Completed !!!! <<<<<< "



  • 만일 SQL*PLUS 를 통해 SYSDBA 유저로 로그인 하는 것조차 불가능할 정도의 상황이라면 어떻게 해야 할까요 ?
  • 예비 접속(Preliminary Connection) 을 사용하면 됩니다.
  • 예비 접속이란 오라클 데이터베이스에 직접 접속하지는 않지만, 일부 제한된 명령은 사용 가능한 접속 형태를 말합니다.
  • 정상적인 접속이 불가능한 비상상황에서 SYSTEMSTATE 덤프와 같이 데이터를 수집하기 위한 용도록 제공됩니다.
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4101

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입