SQLトレースを取得する。
自セッション*1
- SQLトレースを有効化する（開始）。*2
- waits: true にすると待機イベントと待機時間が出力される（デフォルト値: true）
- binds: true にするとバインド変数の値が出力される（デフォルト値: false）
- plan_stat: STAT# で出力されるセクション（実行計画と実行統計）の出力頻度
- FIRST_EXECUTION: 初回のみ出力（デフォルト）
- ALL_EXECUTIONS: 実行のたびに出力（11.1以降）
- NEVER: 出力しない
-- ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; と同じ EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => true, binds => true); -- ALTER SESSION SET EVENTS '10046 trace name context forever, level 16'; と同じ EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => true, binds => true, plan_stat=>'ALL_EXECUTIONS');
- トレースファイル名に含める文字列を指定する*3。
- ファイル名は
_ora_ _TEST01_SQLTRACE.trc となる。
- ファイル名は
ALTER SESSION SET TRACEFILE_IDENTIFIER = "TEST01_SQLTRACE";
SELECT * FROM ...;
- SQLトレースを無効化する（終了）。
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;
- トレースファイルのパスを確認する*4。
set linesize 100 col TRACEFILE for a100 SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = SYS_CONTEXT('userenv','sid'); TRACEFILE ---------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_40357_TEST01_SQLTRACE.trc
- SQLトレースの中身を確認する。
tail -f /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_40357_TEST01_SQLTRACE.trc
- tkprof で整形する。
cd /u01/app/oracle/diag/rdbms/orcl/orcl1/trace tkprof orcl1_ora_40357_TEST01_SQLTRACE.trc orcl1_ora_40357_TEST01_SQLTRACE.tkprof sort=exeela
他セッション（SID, SERIAL# 指定）
- セッションIDとシリアル番号を確認する。
select SID,SERIAL#,USERNAME,OSUSER,PROCESS,TYPE,MODULE,SERVICE_NAME,sql_trace from v$session where TYPE='USER'; SID SERIAL# USERNAME OSUSER PROCESS TYPE MODULE SERVICE_NA SQL_TRAC ------ ------- ---------- ---------- ---------- ---------- -------------------------------------------------- ---------- -------- 8 6703 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V pdb DISABLED 408 52686 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V SYS$USERS DISABLED 611 32130 SYS oracle 40356 USER sqlplus@...jp.oracle.com (TNS V1-V3) SYS$USERS ENABLED 1006 41072 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V SYS$USERS DISABLED 1010 5332 oracle 111007 USER SYS$USERS DISABLED 1206 59729 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V SYS$USERS DISABLED 1211 6902 oracle 111005 USER SYS$USERS DISABLED
- SQLトレースを有効化する（開始）。
BEGIN DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id => 611, serial_num => 32130, waits => true, binds => true, plan_stat=>'ALL_EXECUTIONS'); END; /
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 611, serial_num => 32130);
他セッション（SERVICE_NAME, MODULE 指定）
- SERVICE_NAME, MODULE を確認する。
select SID,SERIAL#,USERNAME,OSUSER,PROCESS,TYPE,MODULE,SERVICE_NAME,sql_trace from v$session where TYPE='USER'; SID SERIAL# USERNAME OSUSER PROCESS TYPE MODULE SERVICE_NA SQL_TRAC ------ ------- ---------- ---------- ---------- ---------- -------------------------------------------------- ---------- -------- 8 6703 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V pdb DISABLED 16 44847 oracle 34303 USER SYS$USERS DISABLED 408 52686 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V SYS$USERS DISABLED 611 32130 SYS oracle 40356 USER sqlplus@...jp.oracle.com (TNS V1-V3) SYS$USERS DISABLED 813 57274 oracle 34305 USER SYS$USERS DISABLED 1006 41072 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V SYS$USERS DISABLED 1206 59729 SYS oracle 55291 USER oraagent.bin@...jp.oracle.com (TNS V SYS$USERS DISABLED 1208 27792 oracle 34301 USER SYS$USERS DISABLED 1609 30466 oracle 34307 USER SYS$USERS DISABLED
- SQLトレースを有効化する（開始）。
BEGIN DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE( service_name => 'SYS$USERS', module_name => 'sqlplus@...jp.oracle.com (TNS V1-V3)', waits => true, binds => true, plan_stat=>'ALL_EXECUTIONS'); END; /
BEGIN DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE( service_name => 'SYS$USERS', module_name => 'sqlplus@...jp.oracle.com (TNS V1-V3)'); END; /
前提条件
初期化パラメータ
設定の確認と変更例
- トレースの出力先ディレクトリを確認する。
SQL> select value from V$DIAG_INFO where name = 'Diag Trace'; VALUE ---------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl1/trace
- 初期化パラメータの値を確認する。
SQL> show parameters MAX_DUMP_FILE_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dump_file_size string unlimited
- 初期化パラメータの値をセッションレベルで変更する。
- 自セッションでSQLトレース取得するときだけ変更したい場合はセッションレベルでの変更でよい。
SQL> alter session set MAX_DUMP_FILE_SIZE = unlimited; Session altered.
- 初期化パラメータの値をシステムレベルで変更する。
SQL> alter system set MAX_DUMP_FILE_SIZE = unlimited scope=both; Session altered.
参考
- Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach
10-15. Tracing a SQL Session
...
All the parameters for the session_trace_enable procedure are self-evident, except the plan_stat parameter. You can set the following values for this parameter:
- never: The trace file won’t contain any information about row source operations.
- first_execution (same as setting the plan_stat parameter to the value null): Row source information is written once, after the first execution of a statement.
- all_executions: Execution plan and execution statistics are written for each execution of the cursor, instead of only when the cursor is closed.
Since an execution plan for a statement can change during the course of a program run, you may want to set the plan_stat parameter to the value all_executions if you want to capture all possible execution plans for a statement.
http://www.amazon.co.jp/Oracle-Database-Performance-Tuning-Recipes/dp/1430261870
- http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/059600527X/
- https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof
- Event 10046 – Full List of Levels – Striving for Optimal Performance
- アプリケーション・トレースの実行
- https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn30502.htm
- SQLトレースの取得方法まとめ（ケース別） | アシスト
