この記事は、JPOUG Advent Calendar 2025 14日目の記事です。13日目は Kenji Hiranoさん の記事『OCIのBaseDBでパフォーマンスハブを表示する方法を調べてみた ～準備編～』でした。
この記事では、問合せ変換されたSQLの変換後SQLを10053トレースからSELECTで見てみる、について扱います。
本記事の概要
オプティマイザによる問合せ変換後のSQLは、Oracleが用意した関数などを通して確認できるものではなく、トレースファイルから確認する必要があります。
やり方としては、10053トレースを取得した後、'Final query after transformations'という文字列の後ろの行を探せばよいのですが、ここではあえて外部表でトレースファイルを読み込みSELECTによって該当箇所を表示させる、ということをやってみます。
トレースファイルをSELECTで読む例をひとつ作っておくと、今回の用途に限らず、他にも応用が効きますので、この例がお役に立てれば幸いです。
事前準備
トレースファイルの出力先のディレクトリオブジェクトを作成
--以下でDefault Trace File: 現行プロセスの現在のデフォルト・トレース・ファイルを確認
SQL> select value fullpath from v$diag_info where name = 'Default Trace File';
FULLPATH
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db0428_nf9_kix/DB0428/trace/DB0428_ora_79436.trc
--ディレクトリオブジェクト作成
create or replace directory TRC_DIR as '/u01/app/oracle/diag/rdbms/db0428_nf9_kix/DB0428/trace';
10053トレースを作成
--10053トレースON
alter session set events '10053 trace name context forever, level 1';
--10053トレースから問合せ変換されたSQLをみたいSQLを実行
/* 今回のQIITAでは以下SQLを実行 */
select sysdate;
--10053トレースOFF
alter session set events '10053 trace name context off';
今回のQIITAでは、23aiでの新機能FROM句なしSQLselect sysdate;を実行すると、内部的にはfrom dualつきで問合せ変換されて実行される、というのを確認します。（オプティマイザの問合せ変換の本質とは、ずれる例示になってしまいすみません。）
FROM句なしSQLが内部的にFROM DUAL付きで実行され、それが10053トレースで確認できる、という知識は、こちらのQiitaで拝見して得られました。
https://qiita.com/ssfujita/items/1a84532b5fa52a821b67
10053トレースを外部表からSELECTして問合せ変換後のSQLを得る
set serveroutput on size unlimited
declare
trace_dir varchar2(4000);
fullpath varchar2(4000);
filename varchar2(255);
sqltxt clob;
rc sys_refcursor;
v_rn number;
v_line varchar2(4000);
begin
select value into fullpath from v$diag_info where name = 'Default Trace File';
filename := regexp_substr(fullpath, '[^/\]+$');
sqltxt := q'[
with ext as (
select rn, line
from external (
(rn number, line varchar2(4000))
type oracle_loader
default directory TRC_DIR
access parameters (
records delimited by newline
fields ( rn recnum, line char(4000) )
)
location (']'||filename||q'[')
)
),
pos as (
select
(select min(rn) from ext where line like '%Final query after transformations:%') as start_rn,
(select min(rn) from ext
where line like '%kkoqbc: optimizing query block SEL$1%'
and rn > (select min(rn) from ext where line like '%Final query after transformations:%')
) as end_rn
from dual
)
select e.rn, e.line
from ext e cross join pos p
where p.start_rn is not null
and e.rn > p.start_rn -- 開始マーカー行は除外（含めたい場合は >= に変更）
and (p.end_rn is null or e.rn < p.end_rn) -- 終了マーカー行は除外
order by e.rn
]';
open rc for sqltxt;
-- 出力（必要に応じてファイル出力やCLOB集約に変更可）
loop
fetch rc into v_rn, v_line;
exit when rc%notfound;
dbms_output.put_line(to_char(v_rn)||': '||v_line);
end loop;
close rc;
end;
/
出力例：
992: SELECT SYSDATE@! "SYSDATE" FROM "SYS"."DUAL" "DUAL"
select sysdate;と実行したのですが、FROM DUAL付きのSQLに変換されています。
このSQLのポイント：
・'Final query after transformations:'と'kkoqbc: optimizing query block SEL$1'という文字列の間に、問合せ変換後のSQLが出てくるのでそこをSELECT文を駆使して抽出。
（バージョンなど環境が異なる場合でも、この文字列の間に挟まれて表示されるという保証があるものではないことにご留意ください。）
・18cでの新機能である、CREATE TABLEなしでSELECTで外部表読み込み可能なインライン外部表を使用（from externalのところ）。インライン外部表でトレースファイルを1行=1レコードで読み込み、行番号（RECNUM）で範囲を切り出しています。