SQL TRACE

SQL 튜닝, 대기현상(내부 매커니즘)을 분석 할때 사용하는 툴

즉, 개발환경에서 문제없이 수행 되는 SQL 문장이 운영환경에서 느린 성능을 보이는 경우 SQL Trace 를 이용하면 원인 분석이 가능하다.

10046 이벤트를 사용하며 4가지 레벨로 제어 한다

LEVEL 1 : SQL 문장의 실행정보(Parse, Execute, Fetch 단계와 Row source operation 결과)만을 제공한다

LEVEL 4 : LEVEL 1 + 바인드 변수값을 제공한다

LEVEL 8 : LEVEL 1 + 대기정보를 제공한다

LEVEL 12 : LEVEL 4 + LEVEL 8, 즉 SQL 문장의 실행정보와 바인드 변수 값, 대기정보를 제공한다

현재 세션 Trace

SQL> ALTER SESSION SET sql_trace=TRUE; -- trace file 시작
SQL> ALTER SESSION SET sql_trace=FALSE; -- trace file 종료

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

다른 세션 Trace

- dbms_system 사용

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE); --sql trace 입장
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' '); -- 이벤트 입장

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' '); -- nm은 name이다.

--SQL> exec dbms_system.set_ev(7, 10, 65535, 10, 'controlf');
--SQL> exec dbms_system.set_ev(8,1056,65535,10,'PROCESSSTATE');

- oradebug 사용

SQL> CONN sys/password AS SYSDBA; --반드시 sysdba 권한 유저로 실행
(SQL> ORADEBUG SETMYPID; -- Debug current session.)

or
SQL> ORADEBUG SETOSPID 1234; -- Debug session with the specified OS process.

or
SQL> ORADEBUG SETORAPID 123456; -- Debug session with the specified Oracle process ID.

SQL> ORADEBUG unlimit; -- Trace file 크기제한 無

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME; -- Display the current trace file.

/u01/app/oracle/product/10.2.0/db_1/rdbms/log/dhdb_ora_10803.trc
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

10g 부터 SQL Trace 로 캡쳐 불가능한 작업을 dbms_monitor 패키지를 사용하여 Client ID, Service/Module/Action 등을 Trace 할 수 있다

이때, 여러 개의 세션에 대한 Trace 파일이 생성되며, trcsess 툴을 이용해 하나의 Trace 파일로 만들 수 있다

exec dbms_monitor.client_id_trace_enable(client_id=>'12345', waites=>true, binds=>true);

exec dbms_monitor.serv_mod_act_trace_enable(123456);

exec dbms_monitor.session_trace_enable(1234567);

또한, dbms_monitor 패키지를 이용하면 세션이 아닌 Client ID 나 Service/Module/Action 명을 기준으로 통계정보(Statistics)를 수집 할 수 있다

client_id_stat_enable, serv_mod_act_stat_enable 을 통해 수집된 정보는 V$CLIENT_STATS, V$SERV_MOD_ACT_STATS 뷰를 통해 관찰 가능하다

cf.alter session set events 'immediate trace name controlf level 10; --> 이 경우는 event 번호가 없으므로 name으로 trace를 생성한다.

=====================================================

출처 및 참고자료 : Advanced Oracle Wait Interface in 10g

=====================================================

special thank's to eddy