概要
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)
Connected.
SQL>
SQL>
SQL> set line 2000
SQL> set pages 2000
SQL> select * from tab;
------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
SQL> alter session set max_dump_file_size=unlimited;
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 ;
-------------------- ------------------------- ---------
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
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ 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 <--★出力したトレースファイル
参考資料
Cost-Based Oracle Fundamentals

Cost-Based Oracle Fundamentals (Expert's Voice in Oracle)
- 作者: Jonathan Lewis
- 出版社/メーカー: Apress
- 発売日: 2007/01/24
- メディア: ペーパーバック
- クリック: 1回
- この商品を含むブログ (1件) を見る
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition, Second Edition

Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition
- 作者: Stelios Charalambides
- 出版社/メーカー: Apress
- 発売日: 2017/04/05
- メディア: Kindle版
- この商品を含むブログを見る
http://www.centrexcc.comA%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf