この記事は約24分3秒で読むことができます。

sql oracle CBOトレース(10053)の取得

参考文献

めちゃんこまとめてあって見やすい。。

A Look under the Hood of CBO: The 10053 Event

The 10053 trace Fileとは

オプティマイザが実行計画を生成する過程を詳細に記載したファイルのこと。実行計画の内容が意図したものになっていない場合に参照するといいかも。クエリ変換で何が起きたかを把握したいときに使うかんじ。。

初期化パラメータmax_dump_file_sizeの設定

unlimitedにする。

コード表示

alter session set max_dump_file_size=unlimited;
col name for a24;
col value for a12;
select name,value from V$PARAMETER where NAME like '%max_dump_file_size%';

max_dump_file_sizeに関しては以下を参照。

1.152 MAX_DUMP_FILE_SIZE

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 17:44:39 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Feb 03 2019 15:04:53 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col name for a24;
AINE@pdb1> col value for a12;
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%max_dump_file_size%';

NAME                     VALUE
------------------------ ------------
max_dump_file_size       unlimited

1 row selected.

Elapsed: 00:00:00.01

初期化パラメータtracefile_identifierの設定

吐かれたファイルを検索しやすくするために設定。

コード表示

alter session set tracefile_identifier='my_10053_trace';
col name for a24;
col value for a24;
select name,value from V$PARAMETER where NAME like '%tracefile_identifier%';

セッション単位で設定する

コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 17:50:22 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Feb 03 2019 17:50:07 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col name for a24;
AINE@pdb1> col value for a24;
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%tracefile_identifier%';

NAME                     VALUE
------------------------ ------------------------
tracefile_identifier

1 row selected.

Elapsed: 00:00:00.02
AINE@pdb1> alter session set tracefile_identifier='my_10053_trace';

Session altered.

Elapsed: 00:00:00.00
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%tracefile_identifier%';

NAME                     VALUE
------------------------ ------------------------
tracefile_identifier     my_10053_trace

1 row selected.

Elapsed: 00:00:00.01

trace levelの設定とトレース開始

トレースレベルに関しては以下を参照。level1level2があり、level2のほうが情報量が少ない。

Using 10053 Trace Events

レベル トレース内容
レベル1のみ Parameters used by the optimizer
レベル1のみ Index statistics
レベル1と2両方 Column statistics
レベル1と2両方 Single Access Paths
レベル1と2両方 Join Costs
レベル1と2両方 Table Joins Considered
レベル1と2両方 Join Methods Considered (NL/MS/HA)

セッション単位で設定する

コード表示

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

level2のほうが情報量が少なくなる分、ファイルサイズは小さくなる。基本はレベル1でいいと思う。。

コード表示

AINE@pdb1> alter session set events '10053 trace name context forever, level 1';

Session altered.

Elapsed: 00:00:00.02

調査対象のSQLの実行前に共有プールのフラッシュ!

ハードパースが実行されたSQLに対してトレースするので、以下のSQLを実行するか、適当なコメント入れてハードパースが起きるようにする!

コード表示

alter system flush shared_pool;

調査対象のSQLの実行

inlineヒントを使った以下のsqlをお試しで実行

コード表示

WITH a AS (
	SELECT /*+ inline */
		1
	FROM
		dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
), b AS (
	SELECT /*+ inline */
		1
	FROM
		a
		, a
		, a
		, a
		, a
		, a
		, a
		, a
		, a
		, a
) SELECT
	1
  FROM
	b
	, b
	, b;
コード表示

AINE@pdb1> WITH a AS (
  2  SELECT /*+ inline */
  3  1
  4  FROM
  5  dual
  6  , dual
  7  , dual
  8  , dual
  9  , dual
 10  , dual
 11  , dual
 12  , dual
 13  , dual
 14  , dual
 15  ), b AS (
 16  SELECT /*+ inline */
 17  1
 18  FROM
 19  a
 20  , a
 21  , a
 22  , a
 23  , a
 24  , a
 25  , a
 26  , a
 27  , a
 28  , a
 29  ) SELECT
 30  1
 31    FROM
 32  b
 33  , b
 34  , b;

            1
-------------
            1

1 row selected.

Elapsed: 00:00:21.18

共有プールから解析対象SQLのSQL_IDを確認

共有プールよりsql_idを取得。

コード表示

COL con_id FOR 99
COL sql_id FOR a13
COL schema FOR a24
COL sql_text FOR a100
COL child# FOR 99999
COL exec FOR 9999

SELECT
	con_id
	, sql_id
	, parsing_schema_name AS schema
	, sql_text
	, child_number AS child#
	, executions AS exec
FROM
	v$sql
WHERE
	sql_text LIKE '%inline%'
	AND sql_text NOT LIKE '%SQL_TEXT%'
ORDER BY
	sql_id;
コード表示

AINE@pdb1> COL con_id FOR 99
AINE@pdb1> COL sql_id FOR a13
AINE@pdb1> COL schema FOR a24
AINE@pdb1> COL sql_text FOR a100
AINE@pdb1> COL child# FOR 99999
AINE@pdb1> COL exec FOR 9999
AINE@pdb1>
AINE@pdb1> SELECT
  2  con_id
  3  , sql_id
  4  , parsing_schema_name AS schema
  5  , sql_text
  6  , child_number AS child#
  7  , executions AS exec
  8  FROM
  9  v$sql
 10  WHERE
 11  sql_text LIKE '%inline%'
 12  AND sql_text NOT LIKE '%SQL_TEXT%'
 13  ORDER BY
 14  sql_id;

CON_ID SQL_ID        SCHEMA                   SQL_TEXT                                                                                             CHILD#  EXEC
------ ------------- ------------------------ ---------------------------------------------------------------------------------------------------- ------ -----
     3 0hfcqnf1j7zqd AINE                     WITH a AS ( SELECT /*+ inline */ 1 FROM dual , dual , dual , dual , dual , dual , dual , dual , dual      0     1
                                               , dual ), b AS ( SELECT /*+ inline */ 1 FROM a , a , a , a , a , a , a , a , a , a ) SELECT 1   FRO
                                              M b , b , b


1 row selected.

Elapsed: 00:00:00.10

DBMS_SQLDIAG.DUMP_TRACEの実行

DBMS_SQLDIAG.DUMP_TRACEに関しては以下を参照。

Capturing 10053 trace files continued
Oracle DBMS_SQLDIAG.DUMP_TRACE

コード表示

exec dbms_sqldiag.dump_trace('&sql_id', 0,'Compiler','my_10053_trace');
コード表示

AINE@pdb1> exec dbms_sqldiag.dump_trace('&sql_id', 0,'Compiler','my_10053_trace');
Enter value for sql_id: 0hfcqnf1j7zqd

PL/SQL procedure successfully completed.

Elapsed: 00:00:21.71

吐かれたファイルの確認

$diagnostic_dest/diag/rdbms/orcl/$ORACLE_SID/trace/配下に出力される。ファイル名は$sid_ora_$pid_$p_file_id.trc

コード表示

col name for a24
col value for a24
select name,value from V$PARAMETER where name like '%diagnostic_dest%';
コード表示

AINE@pdb1> col name for a24
AINE@pdb1> col value for a24
AINE@pdb1> select name,value from V$PARAMETER where name like '%diagnostic_dest%';

NAME                     VALUE
------------------------ ------------------------
diagnostic_dest          /opt/oracle

1 row selected.

Elapsed: 00:00:00.01

コード表示

[oracle@f285aba0589a ~]$ cd /opt/oracle/diag/rdbms/orcl/$ORACLE_SID/trace/
[oracle@f285aba0589a trace]$ pwd
/opt/oracle/diag/rdbms/orcl/ORCL/trace
[oracle@f285aba0589a trace]$ ll -lt | grep trace
-rw-r-----. 1 oracle oinstall 115760415 Feb  9 16:49 ORCL_ora_14486_my_10053_trace.trc
-rw-r-----. 1 oracle oinstall  18054743 Feb  9 16:49 ORCL_ora_14486_my_10053_trace.trm
[oracle@f285aba0589a trace]$

トレース終了

以下のSQLでトレースを終了させる

コード表示

alter session set events '10053 trace name context off';
コード表示

AINE@pdb1> alter session set events '10053 trace name context off';

Session altered.

Elapsed: 00:00:00.00
AINE@pdb1>

Leave a Reply

Your email address will not be published. Required fields are marked *