[Oracle] SQLトレースログの取得方法・出力先・tkprof使い方・削除方法

2018年8月18日2022年6月4日

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