Oracle DatabaseのSQLトレースログの
- 取得方法
- 出力先
- tkprofの使い方
- 削除方法
を紹介したいと思います。
性能劣化時や障害時の調査に大変役立つログであり、
サポート問い合わせ時に利用します。
SQLトレースの取得方法
SQLトレースには２つの取得方法があります。
「alter session」文と「DBMS_MONITOR」プロシージャになります。
トレースログは
<oracle_sid>_ora_<session_id>.trcのファイルフォーマットで作成されます。
alter session
初期化パラメータ「SQL_TRACE」を「TRUE」にした状態で「alter session」で
イベントを設定することでSQLトレースを取得できます。
alter session set SQL_TRACE = TRUE;
alter session set events '10046 trace name context forever, level <レベル>';SQLトレースを取得する際にはレベルを指定することで、取得する情報量を制御します。
- Level 1SQLトレースだけ
- Level 4 -> SQLトレースとバインド変数
- Level 8 -> SQLトレースと待機イベント
- Level 12 -> SQLトレースとバインド変数と待機イベント
DBMS_MONITOR
「DBMS_MONITOR」でSQLトレースを取得するためには、シリアルIDとシリアル番号が必要なります。
「V$SESSION」ビューからシリアルIDとシリアル番号を取得出来ます。
set line 1000
set pages 1000
col MODULE for a20
col SERVICE_NAME for a20
select SID
,SERIAL#
,MODULE
,SERVICE_NAME
from V$SESSION
where USERNAME= <ユーザー名>;確認後に「DBMS_MONITOR.SESSION_TRACE_ENABLE」で
begin
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id => <シリアルID>
,serial_num => <シリアル番号>
,waits => TRUE or FALSE -- 待機情報トレース有無を設定
,binds => TRUE or FALSE -- バインド情報トレース有無を設定
);
end;
/取得が終わったら必ずSQLトレースを無効化しましょう。
BEGIN
DBMS_MONITOR.SESSION_TRACE_DISABLE(
session_id => <シリアルID>
,serial_num => <シリアル番号>
);
END;
/SQLトレースの出力先
SQLトレースの出力先は「DIAGNOSTIC_DEST」に指定されている
<diagnostic_dest>/diag/rdbms/<dbname>/<instname>/traceディレクトリに出力されます。
トレースログは
<oracle_sid>_ora_<session_id>.trcのファイルフォーマットで作成されます。
show parameter DIAGNOSTIC_DEST以下の「alter system」文でパラメータを変更することができます。
変更後に再起動を行う必要があります。
alter system set DIAGNOSTIC_DEST = <出力先> scope = SPFILE;「DIAGNOSTIC_DEST」が設定されていない場合は初期化パラメータの「USER_DUMP_DEST」で指定します。
show parameter USER_DUMP_DEST以下の「alter system」文でパラメータを変更することができます。
alter system set USER_DUMP_DEST = <出力先> scope = BOTH; なおトレースファイルの最大サイズを変更するパラメータ「MAX_DUMP_FILE_SIZE」です。
以下のSQLで変更することができます。
alter system set MAX_DUMP_FILE_SIZE = <integer [K|M|G] |UNLIMITED > scope = BOTH;tkprofの使い方
「tkprof」の使い方を紹介します。
まずは「tkprof」とは生のSQLトレースを見やすい形に成形してくれるユーティリティになります。
使用方法は以下になります。
tkprof <SQLトレースファイル名> <成形後のトレースファイル>トレースログは
<oracle_sid>_ora_<session_id>.trcのファイルフォーマットで作成させています。
TKPROF: Release 18.0.0.0.0 - Development on 土 8月 11 06:08:49 2018
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Trace file: orcl18c_ora_14007.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select col01, col02
from
tab001 where COL01 = '0000001077'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 5 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 74
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID BATCHED TAB001 (cr=5 pr=0 pw=0 time=54 us starts=1 cost=4 size=22 card=1)
1 1 1 INDEX RANGE SCAN TAB001_IDX (cr=4 pr=0 pw=0 time=85 us starts=1 cost=3 size=0 card=1)(object id 23110)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
Trace file: orcl18c_ora_14007.trc
Trace file compatibility: 12.2.0.0
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
105 lines in trace file.
118 elapsed seconds in trace file.生SQLトレースログですが今回の記事では内容を割愛します。
SQLトレースの削除方法
SQLトレースログの削除方法ですが、OSコマンドで削除することが出来ます。
「ADRCI」コマンドでも削除することができますが、
特にスケジュール機能が付いているわけではないので好みよいでしょう。
$ adrci
adrci> purge -age <保存期間を”分”で指定> -type trace以下のように実行すること1日分を残して、過1日以前に生成されたログを削除します。
[oracle@rhel73-18000 trace]$ adrci
ADRCI: Release 18.0.0.0.0 - Production on 土 8月 11 05:58:46 2018
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci> purge -age 1440 -type trace
