0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@asahide

ADB 26ai DBMS_USERDIAG の sql_id フィルタで特定 SQL の実行計画を SESSION_CLOUD_TRACE から取得する

0
Posted at

2026年5月18日の情報です

1. はじめに

前回記事「ADB 26ai 新パッケージ DBMS_USERDIAG で SQL トレースを取る」では DBMS_USERDIAG の基本的な使い方を確認しました。今回は ENABLE_SQL_TRACE_EVENT の sql_id パラメータを使い、特定の SQL だけに絞ったトレースを SESSION_CLOUD_TRACE から SQL で確認する手順を紹介します。

SESSION_CLOUD_TRACE は ADB 固有のビューで、トレースファイルをダウンロードせずに DB 上で直接クエリできます。plan_stat => 'ALL_EXECUTIONS' と組み合わせると、実行のたびに STAT 行が書き出され、実行計画の操作名と実際の行数（cnt）を実行履歴として SQL から参照できます。

今回の検証ゴール

# 検証項目
1 sql_id フィルタで対象 SQL のみ STAT 行が SESSION_CLOUD_TRACE に出力されるか
2 plan_stat => 'ALL_EXECUTIONS' で実行ごとに STAT 行が書き出されるか
3 SESSION_CLOUD_TRACE の STAT 行から実行計画の操作名と cnt を SQL で取得できるか

結論の先出し

  • sql_id フィルタは動作する。対象 SQL の STAT 行が SESSION_CLOUD_TRACE に出力される ✅
  • sql_id 指定時は CHECK_SQL_TRACE_EVENT が 0 を返す。0 でもトレースは機能している ✅
  • plan_stat => 'ALL_EXECUTIONS' で実行ごとに STAT 行が出力される ✅
  • SESSION_CLOUD_TRACE の STAT 行から実行計画の操作名と cnt を SQL で取得できる ✅
  • STAT 行に card=（推定行数）・cr=（一貫性読み取り）は含まれない。これらが必要な場合は Object Storage の .trc ファイルを参照する

2. 検証環境

項目 内容
ADB-S インスタンス adbtest02
データベースバージョン Oracle AI Database 26ai EE 23.26.2.1.0
DEFAULT_LOGGING_BUCKET 設定済み（OCI Object Storage）

3. sql_id フィルタの使い方

ENABLE_SQL_TRACE_EVENT に sql_id を指定すると、そのカーソルが実行されたときのみ STAT / WAIT / BINDS 行が SESSION_CLOUD_TRACE に書き出されます。

BEGIN
  DBMS_USERDIAG.ENABLE_SQL_TRACE_EVENT(
    sql_id    => 'a4m8pcc1279qw',  -- 対象 SQL の SQL_ID
    waits     => 1,
    binds     => 1,
    plan_stat => 'ALL_EXECUTIONS'  -- 実行ごとに STAT 行を出力
  );
END;
/

sql_id 指定時は CHECK_SQL_TRACE_EVENT が 0 を返す

sql_id を指定した場合、CHECK_SQL_TRACE_EVENT は 0 を返します。sql_id フィルタはセッション全体のトレースとは別の内部機構で管理されているためと考えられます（公式ドキュメントに記載なし、実測から得た仮説）。0 でもトレースは機能しており、SESSION_CLOUD_TRACE に STAT 行が出力されます。

sql_id は対象 SQL を一度実行した後、V$SQL から取得できます。

SELECT sql_id, SUBSTR(sql_text, 1, 80) AS sql_text
  FROM v$sql
 WHERE sql_text LIKE '%your_keyword%'
   AND sql_text NOT LIKE '%v$sql%'
 ORDER BY last_active_time DESC
 FETCH FIRST 5 ROWS ONLY;

4. 検証手順と結果

4.1. テストデータ準備

選択度に偏りのあるテーブルを作成します（OPEN=100 行、CLOSED=900,000 行）。

CREATE TABLE t_orders (
  id     NUMBER GENERATED ALWAYS AS IDENTITY,
  status VARCHAR2(10),
  val    NUMBER
);

INSERT INTO t_orders (status, val)
SELECT 'OPEN', ROWNUM FROM dual CONNECT BY ROWNUM <= 100;

INSERT INTO t_orders (status, val)
SELECT 'CLOSED', ROWNUM FROM dual CONNECT BY ROWNUM <= 900000;

COMMIT;

CREATE INDEX idx_t_orders_status ON t_orders(status);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDERS', cascade => TRUE);

4.2. sql_id 取得・トレース取得

対象 SQL を一度実行して V$SQL から sql_id を確認した後、トレースを開始してバインド値を変えて 2 回実行します。

-- sql_id 取得用の初回実行（バインド値 OPEN）
VARIABLE v_status VARCHAR2(10)
EXEC :v_status := 'OPEN'
SELECT /*+ NO_RESULT_CACHE */ COUNT(*), MAX(val)
  FROM t_orders WHERE status = :v_status;

-- sql_id: a4m8pcc1279qw を確認
SELECT sql_id, SUBSTR(sql_text, 1, 80)
  FROM v$sql
 WHERE sql_text LIKE '%t_orders%' AND sql_text LIKE '%v_status%'
   AND sql_text NOT LIKE '%v$sql%'
 ORDER BY last_active_time DESC FETCH FIRST 3 ROWS ONLY;

-- トレース開始
BEGIN
  DBMS_USERDIAG.SET_TRACEFILE_IDENTIFIER('sql_id_filter_test');
  DBMS_USERDIAG.ENABLE_SQL_TRACE_EVENT(
    sql_id    => 'a4m8pcc1279qw',
    waits     => 1,
    binds     => 1,
    plan_stat => 'ALL_EXECUTIONS'
  );
END;
/
-- CHECK は 0 を返すがトレースは機能している（§3 参照）

-- 実行 1: OPEN（100 行）
EXEC :v_status := 'OPEN'
SELECT /*+ NO_RESULT_CACHE */ COUNT(*), MAX(val)
  FROM t_orders WHERE status = :v_status;

-- 実行 2: CLOSED（900,000 行）
EXEC :v_status := 'CLOSED'
SELECT /*+ NO_RESULT_CACHE */ COUNT(*), MAX(val)
  FROM t_orders WHERE status = :v_status;

-- トレース停止
BEGIN
  DBMS_USERDIAG.ENABLE_SQL_TRACE_EVENT(disable => 1);
END;
/

4.3. SESSION_CLOUD_TRACE で STAT 行を確認

行数と BINDS の確認

SELECT COUNT(*) FROM session_cloud_trace;
-- 195行

-- バインド値が記録されていることを確認
SELECT row_number, trace
  FROM session_cloud_trace
 WHERE trace LIKE '%value="%'
 ORDER BY row_number;

ROW_NUMBER TRACE
---------- ----------------
       153   value="OPEN"
       179   value="CLOSED"

STAT 行の取得

SELECT row_number,
       SUBSTR(trace, 1, 120) AS stat_line
  FROM session_cloud_trace
 WHERE trace LIKE 'STAT %'
 ORDER BY row_number;

ROW_NUMBER STAT_LINE
---------- --------------------------------------------------------
        46 STAT #xxxxxxxx id=1 cnt=1   ... op='INDEX UNIQUE SCAN      ← 初回ハードパース時の再帰 SQL（後述）
        66 STAT #xxxxxxxx id=1 cnt=0   ... op='VIEW                   ← 同上
       ...
       139 STAT #xxxxxxxx id=17 cnt=0  ... op='TABLE ACCESS BY INDEX ROWID

       156 STAT #xxxxxxxx id=1 cnt=1      pid=0 op='SORT AGGREGATE
       159 STAT #xxxxxxxx id=2 cnt=100    pid=1 op='TABLE ACCESS BY INDEX ROWID BATCHED
       164 STAT #xxxxxxxx id=3 cnt=100    pid=2 op='INDEX RANGE SCAN

       182 STAT #xxxxxxxx id=1 cnt=1      pid=0 op='SORT AGGREGATE
       185 STAT #xxxxxxxx id=2 cnt=900000 pid=1 op='TABLE ACCESS BY INDEX ROWID BATCHED
       190 STAT #xxxxxxxx id=3 cnt=900000 pid=2 op='INDEX RANGE SCAN

24行が選択されました。

対象 SQL（a4m8pcc1279qw）の STAT 行が 2 セット出力されました。

実行ごとの cnt 比較

実行 バインド値 操作 cnt（実際の行数）
1 回目 OPEN SORT AGGREGATE / TABLE ACCESS / INDEX RANGE SCAN 1 / 100 / 100
2 回目 CLOSED SORT AGGREGATE / TABLE ACCESS / INDEX RANGE SCAN 1 / 900,000 / 900,000

実行計画の操作（INDEX RANGE SCAN）は両実行で同一です。バインド値が変わっても同じ計画が再利用されており、実行 2 では 900,000 行をインデックス経由でフルスキャンしていることが SESSION_CLOUD_TRACE から確認できます（いわゆるバインドピークの動作になります）。

SESSION_CLOUD_TRACE の STAT 行の制限

STAT 行はオペレーション名までで終わり、card=（推定行数）・cr=（一貫性読み取り）は含まれません（実測: 97 文字）。cnt の変化で操作ごとの実際の行数は確認できます。card= や cr= が必要な場合は Object Storage の .trc ファイルを参照してください（前回記事参照）。

混入した STAT 行の正体

一部はユーザー SQL の初回ハードパース時に Oracle カーネルが再帰的に発行した内部 SQL の STAT 行です。

内部 SQL 目的
SELECT signature FROM sqllog$ SQL シグネチャのセキュリティログ確認
SELECT ... FROM wri$_sqlset_definitions（SYS_AUTO_STS） 自動 SQL チューニングアドバイザによるプラン参照

2 回目の実行（CLOSED）はソフトパース（mis=0）のため、この再帰呼び出しは発生しません。STAT 行の obj=N は数値 ID のみでテーブル名を含まないため、カーソルアドレスで対象 SQL と内部 SQL を区別してください。

5. DBMS_XPLAN との使い分け

観点 DBMS_XPLAN.DISPLAY_CURSOR sql_id フィルタ + SESSION_CLOUD_TRACE
カーソルがキャッシュから消えた後 ❌ 取得不可 ✅ SESSION_CLOUD_TRACE に残存
実行ごとの cnt 履歴 ❌ 最新1回分 ✅ ALL_EXECUTIONS で全実行分
card（推定行数 / E-Rows） ✅ ❌（SESSION_CLOUD_TRACE には含まれない）
cr（一貫性読み取り） ❌ ❌（SESSION_CLOUD_TRACE には含まれない）
ADB で使用可能 ✅ ✅
ALTER SESSION 不要 ✅ ✅
他セッションのカーソル参照 ✅（sql_id 指定時、共有プール残存中） ✅（sid/ser 指定時）

card= や cr= が必要な場合は Object Storage の .trc ファイルと組み合わせます。SQL 一本で手軽に確認するなら SESSION_CLOUD_TRACE が実用的です。

6. まとめ

# 検証項目 結論
1 sql_id フィルタで対象 SQL のみ STAT 行が出力されるか ✅ 対象 SQL の STAT 行が SESSION_CLOUD_TRACE に出力される
2 plan_stat => 'ALL_EXECUTIONS' で実行ごとに STAT 行が出力されるか ✅ 実行ごとに cnt が更新された STAT 行が出力される
3 SESSION_CLOUD_TRACE の STAT 行から実行計画の操作名と cnt を取得できるか ✅ SELECT だけで操作名と cnt を実行履歴として確認できる

sql_id フィルタには 2 つの注意点があります。CHECK_SQL_TRACE_EVENT が 0 を返しても実際にはトレースが機能していること、初回ハードパース時に Oracle 内部 SQL の STAT 行が少数混入することです。

SESSION_CLOUD_TRACE は ADB 固有のビューで、ファイルダウンロードなしに SQL だけで実行計画の操作名と実際の行数を実行履歴として確認できます。断続的に遅い SQL や「2 回目から遅い」問題で DBMS_XPLAN が使えない状況での診断に有効です。

参考

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?