Oracleトレースファイルの詳細な使用方法

I. トレースファイルとは何ですか？

トレースファイルには、広範かつ詳細な診断およびデバッグ情報が含まれています。トレースファイルを解釈し分析することで、問題を発見し、分析し、解決することができます。トレースファイルの生成場所については、データベース運用者が意図的に生成するものと、異常なエラーが発生したためにデータベースが自動的に生成するものに分けられる。後者については、Oracle社内のテクニカルサポートスタッフに限っては有用だが、我々にとっては、ほとんど読むことができない。一方、前者のカテゴリは、アプリケーションのパフォーマンスを分析、チューニング、最適化し、問題に対処、解決するために頻繁に使用するものです。

では、トレースファイルはどこにあるのでしょうか？トレースファイルが保存されている場所のパスは、以下のようにデータディクショナリーv$diag_infoに問い合わせることで知ることができます。

select * from v$diag_info;

ディレクトリ /u01/app/oracle/diag/rdbms/orcl/orcl/trace に移動すると、以下のように接尾辞が trc のトレースファイルを見ることができます。

II. トラッキングファイルの命名規則

トレースファイルの名前は、一般的に以下の部分から構成されます。

• ORACLE_SID
• 固定文字
• サーバーのプロセスID番号
• ファイルの拡張子.trc
• セクションはアンダースコアでリンクされています。

例： orcl_mmon_12210.trc, ここで "orcl" はこの環境のデータベースの SID、 "12210" はこのトレース ファイルのセッション生成に使用したサーバーのプロセス ID 番号です。ORACLE_SIDとセッションに使用されたサーバープロセスIDを知るにはどうしたらよいですか？

第三に、トレースファイルはどのように決定するのですか？

デモンストレーションのために、通常のユーザーであるscottにdbaの役割を付与してみましょう。

1. adminログインを使用し、scottにdbaのロールを付与します。

[oracle@oracle12c ~]$ sqlplus / as sysdba

SQL> grant dba to scott;

Grant succeeded.

SQL> 

2. 以下のように、Oracle SIDを決定します。ここでのSIDは：orcl

SQL> select instance_name from V$instance;

INSTANCE_NAME
----------------
orcl

SQL> 

3. scottユーザーに切り替えて、セッションIDを決定します。

SQL> conn scott/tiger
Connected.
SQL> select sid from v$mystat where rownum=1;

       SID
----------
	70

SQL> 

4. セッションIDを元に、セッションのアドレス情報を決定する

SQL> select paddr from v$session where sid=70;

PADDR
----------------
000000006DAB6588

SQL> 

5. セッションのアドレス情報から、OSのプロセス番号を判断する

SQL> select spid from v$process where addr='00000000006DAB6588';

SPID
------------------------
54685

SQL> 

ディレクトリ /u01/app/oracle/diag/rdbms/orcl/trace に移動すると、この時点では 54685 を含むトレース ファイルが存在しないことがわかります。トレース ファイルを使用するには、セッションのトレースを手動で開く必要があるためです。

6. セッションのトレースをオンにする

SQL> alter session set sql_trace=true;

Session altered.

SQL> 

7. 簡単なSQL文を実行し、/u01/app/oracle/diag/rdbms/orcl/traceディレクトリを確認し、その時点で生成されたトレースファイルを見ることができます。

[oracle@oracle12c trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle12c trace]$ ls *54685.trc
orcl_ora_54685.trc
[oracle@oracle12c trace]$ 

IV. SQLを診断するためにトレースファイルを使用する

トレースは、以下の簡単な例で示すように、SQL文の診断に非常に有効です。

1. 次のSQL文を実行します。

select * from scott.emp where deptno=10;

select * from scott.emp where deptno=20;

select * from scott.emp where deptno=30;

これらの3つのSQLクエリは、部門10、20、30の従業員を対象としています。3つのSQL文は、パラメータ値が異なるwhere条件以外はすべて同じものであることがわかります。このようなSQL文は、「重複SQL」と呼ばれます。データベース内に重複したSQLがたくさんあると、実行するたびにSQLを解析して実行計画を生成するようになります。これは、データベースのパフォーマンスに影響します。

上記の結論を検証するためのトレースファイルです。

2. 先ほどセッションのトレースをオンにしたので、トレースを行う必要がなくなった場合は、手動でオフにする必要があります。

SQL> alter session set sql_trace=false;

Session altered.

SQL> 

3. tkprof ツールを使用してトレースファイルをフォーマットします。

[oracle@oracle12c trace]$ tkprof orcl_ora_54685.trc /home/oracle/a.txt sys=no sort=fchela

TKPROF: Release 12.2.0.1.0 - Development on Mon Jun 28 10:37:48 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.
[oracle@oracle12c trace]$ 

4. 生成されたa.txtファイルを以下のように表示します。

SQL ID: 1mvxd868z75nf Plan Hash: 3956160932
select *
from scott.emp where deptno=30

SQL ID: 2nbac4n9hnzth Plan Hash: 3956160932
select *
from scott.emp where deptno=20

SQL ID: 062r5atccuyv4 Plan Hash: 3956160932
select *
from scott.emp where deptno=10

3つのSQLのSQL IDが異なるにもかかわらず、生成されたプランハッシュが同じであることがわかります。これは、3つのSQLの実行計画が同じであることを意味します。このことから、この3つのSQL文をバインド変数を使って書き換えることで、毎回実行計画を生成することなく実行することができるのです。最初に生成された実行計画を再利用すればよいのです。これによってパフォーマンスが向上します。

以上、Oracleトレースファイルの使用方法について詳しく説明しました。Oracleトレースファイルの詳細については、スクリプトハウスの他の関連記事を参照してください!