oracle 운영시 개별 세션이나 프로세스가 이상이 있을 경우 trace/dump 뜨는 방법.

오라클 운영시 특정 세션이나 프로세스가 resource를 과도하게 소모하거나, 속도 저하 등의 이상이 발생해 다른 세션들 까지 영향을 주는 경우가 종종 발생하곤합니다. 이럴 때 해당 세션이나 프로세스의 문제를 해결하기 위해 시간을 끌다가는 전체 업무에 주는 영향으로 인해 더 큰 문제가 발생할 수 있죠.

다음은 개별 세션이나 프로세스가 이상이 있을때 가징 기본적으로 수집해야 하는 trace/dump를 수행하는 방법입니다. 알아서 관련 정보를 다 수집해 주면 좋겠으나, 일일히 관련 정보를 수집해야 하니 좀 번거로울 수 있겠으나, 이런 작업들을 안해 놓으면 문제가 미궁으로 빠질 수 있으므로, 미리미리 알아 놓는 게 좋겠죠..

오라클은 많은 trace/dump 수행 방법을 제공하고 있는데, 다음의 event 10046, errorstack은 개별 세션의 정보를 수집합니다. 물론 이 두개의 trace/dump를 수행하고 전체적인 systemstate dump나 hanganalyze도 같이 수행을 하면 전체적인 영향까지도 확인 할 수 있겠죠.

10046 event는 일반적인 SQL_TRACE 기능을 수행합니다. 또 특정 level을 지정해주면 해당 세션의 wait event와 bind value까지 tracing 해줍니다.

1  - Enable standard SQL_TRACE functionality (Default)
4  - As Level 1 PLUS trace bind values
8  - As Level 1 PLUS trace waits
12 - As Level 1 PLUS both trace bind values and waits

ERRORSTACK dump은 해당 process의 현재 상태 및 정보를 수집합니다. SQL 구문과 Call stack, errorstack, processstate, context area 정보를 수집합니다.

0    Error stack only (no longer be a valid option)
1    Error stack and function call stack (if implemented)
2    As 1 plus the ProcessState
3    As 2 plus the context area (all cursors and current cursor highlighted)

다음은 oradebug를 이용해 10046 event를 on/off 시키는 방법입니다.
event를 on 시킨 후 충분한 데이터를 수집했다고 판단되면 off 시키면 되며, 결과물은 sql_trace 처럼 tkprof로 정리해서 볼수 있다.

* 10046 tracing on
sqlplus '/as sysdba'
oradebug에 세션이나 프로세스를 지정할 때 oracle pid나 o/s pid, 자기자신의 process를 지정할 수 있다.
따라서 위의 3가지 중 하나를 지정하면 된다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid
trace 발생양에 대한 제한을 unlimit로 지정한다.
SQL>oradebug unlimit
event 10046을 level 12로 지정한다.
SQL>oradebug Event 10046 trace name context forever, level 12
생성한 trace file이름을 출력한다.
SQL>oradebug tracefile_name
SQL>exit

* 10046 trace off
sqlplus '/as sysdba'
10046 trace 지정시 사용한 oracle pid나 o/s pid, 자기자신의 process를 지정 한다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid
event 10046 설정을 해지한다.
SQL>oradebug Event 10046 trace name context off
SQL>exit

다음은 oradebug를 이용해 ERRORSTACK dump를 수행하는 방법입니다. ERRORSTACK은 해당 시점의 프로세스 정보를 수집하는 것이므로 on/off 단계는 필요 없이 한번만 수행해 주면 됩니다.

* ERRORSTACK dump
sqlplus '/as sysdba'
oradebug에 세션이나 프로세스를 지정할 때 oracle pid나 o/s pid, 자기자신의 process를 지정할 수 있다.
따라서 위의 3가지 중 하나를 지정하면 된다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid
trace 발생양에 대한 제한을 unlimit로 지정한다.
SQL>oradebug unlimit
errorstack dump를 level 3으로 수행한다.
SQL>oradebug dump errorstack 3
생성한 trace file이름을 출력한다.
SQL>oradebug tracefile_name
SQL>exit

가끔 sqlplus 자체가 수행이 되지 않아 trace/dump 수행이 어려운 경우가 있는데, 이러한 경우 sqlplus의 -prelim option을 사용하거나 debugger tool인 dbx를 사용해서 errorstack을 수행할 수 있습니다.

dbx debugger를 이용한 process의 call stack 보기.

dbx -a $pid
dbx) where
dbx) detach
 
* 반드시 detach 하지 않을 경우 attach 한 프로세스는 비정상 종료된다.
* -a flag는 해당 프로세스에 attach 하여 dbx 시행하는 동안 해당 프로세스는 실행이 중지되므로 background process에 대한 attach는 피하는 편이 신상에 좋습니다.. --;

dbx debugger를 이용한 core file의 call stack 보기.

dbx program명 core
dbx) where
dbx) quit

위의 내용은 oracle metalink note에서 참고했습니다.
Note.21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note.37871.1 EVENT: ERRORSTACK - How to get
Note 1058210.6 How to enable 10046 for another session using ORADEBUG  
Note 62294.1 The DBMS_SUPPORT Package                                
Note 35054.1 Interpreting ERRORSTACK output                          
Note 39817.1 Interpreting RAW 10046 output