Oracle CBOトレース(10053)の取得

概要

CBOトレースの取得方法について記載します。
event 10053は、CBO(Cost Base Optimizer)の動作をトレースするイベントです。
CBOがどのような計算を行い、最終的にその実行計画を選択したのかを調査する際に有用です。
主に、CBOが選択した実行計画が最適なものでないと考えられる場合の調査で使用することが多いです。

取得方法

10053トレースの取得方法は、大きく以下の3点があります。

SQL*Plusから調査対象のSQLを実行
共有プールに解析済みのカーソル情報より Optimizer のトレース(event 10053 トレース相当)の情報を取得する方法(※11.2～)
SQLTXPLAINの使用(※別途インストール要)

取得例

1.
connect ユーザー名/パスワード
SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set tracefile_identifier=’MY_10053_TRACE’;
SQL> alter session set events ‘10053 trace name context forever, level 1’;
<調査対象のSQL実行>
SQL> alter session set events ‘10053 trace name context off’;
SQL> exit

※
tracefile_identifierは必須ではありません。
トレース・ファイルに独自の名前を付加することで出力したトレースファイルを容易に識別するために実行しています。

2.
<解析対象のSQL実行>
<共有プールから解析対象SQLのSQL_IDの確認>
DBMS_SQLDIAG.DUMP_TRACE の実行
SQL> BEGIN
            DBMS_SQLDIAG.DUMP_TRACE(
              P_SQL_ID => '&P_SQL_ID',
              P_CHILD_NUMBER => 0,
              P_COMPONENT => 'Compiler',
              P_FILE_ID => 'OPT_TRACE');
         END;
         /
p_sql_idに値を入力してください: 0s4rmy40jw5dp ← 解析対象の SQL_ID を入力
旧 3: P_SQL_ID => '&P_SQL_ID',
新 3: P_SQL_ID => '0s4rmy40jw5dp',

上記実行後、<diagnostic_dest>/diag/rdbms/<db_unique_name>/<SID>/trace/配下に
「<SID>_ora_<PID>_<P_FILE_IDの識別文字列>.trc」という名称のトレースファイルが生成される

3.
標準機能ではないため今回は割愛します。

取得に際しての注意点

10053トレースは、SQLのハードパースが実行されたタイミングで出力される情報のためハードパースをしなければ有効な情報が出力されません。
意図的にハードパースさせるために下記のいずれかの方法でハードパースが実行されるようにしてください。

適当なコメントを追加し、別カーソルとする
情報取得前に共有プールのフラッシュを実行
コマンド：
SQL> alter system flush shared_pool;
但し、共有プールのフラッシュを行った場合、他のセッションにも影響を与えるので本番環境では実施すべきではないと考えます。

実行例(取得方法1)

SQL> conn hr/hr@pdb1;
Connected.
SQL>
SQL>
SQL> set line 2000
SQL> set pages 2000
SQL> select * from tab;

TNAME                    TABTYPE CLUSTERID
------------------------ ------- ----------
COUNTRIES                TABLE
DEPARTMENTS              TABLE
EMPLOYEES                TABLE
EMP_DETAILS_VIEW         VIEW
JOBS                     TABLE
JOB_HISTORY              TABLE
LOCATIONS                TABLE
REGIONS                  TABLE

8 rows selected.
SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> select
2      /*+ hard parse */
3      first_name,
4      last_name,
5      hire_Date
6 from
7      employees
8 where
9      hire_date in(
10         select
11             hire_date
12         from
13             employees
14         where
15             department_id = 30
16      )
17   ;

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Den                  Raphaely                  07-DEC-02
Alexander            Khoo                      18-MAY-03
Shelli               Baida                     24-DEC-05
Sigal                Tobias                    24-JUL-05
Guy                  Himuro                    15-NOV-06
Karen                Colmenares                10-AUG-07

6 rows selected.

SQL> alter session set events '10053 trace name context off';

Session altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

$ cd /opt/oracle/diag/rdbms/ora12201/ora12201/trace
$ ls -ltr
total 35436
-rw-r-----. 1 oracle oinstall 945 May 18 12:29 ora12201_vktm_446.trm
-rw-r-----. 1 oracle oinstall 1534 May 18 12:29 ora12201_vktm_446.trc
    :
(中略)
    :
-rw-r-----. 1 oracle oinstall   1147 May 28 12:00 ora12201_m001_5225.trc
-rw-r-----. 1 oracle oinstall    925 May 28 13:00 ora12201_m001_7691.trm
-rw-r-----. 1 oracle oinstall   1147 May 28 13:00 ora12201_m001_7691.trc
-rw-r-----. 1 oracle oinstall 70814 May 28 13:43 ora12201_ora_9374.trm
-rw-r-----. 1 oracle oinstall 154784 May 28 13:43 ora12201_ora_9374.trc <--★出力したトレースファイル