Database OracleDB インフラ

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

2018年8月18日

こんにちは!シミダイ(@shimidai2100)です。

Oracle DatabaseのSQLトレースログの

  • 取得方法
  • 出力先
  • tkprofの使い方
  • 削除方法

を紹介したいと思います。

性能劣化時障害時の調査に大変役立つログであり、サポート問い合わせ時に利用します。


SQLトレースの取得方法

SQLトレースには2つの取得方法があります。

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

 

関連記事

-Database, OracleDB, インフラ

Copyright© shimidai2100 , 2020 All Rights Reserved.