チューニング

Oracle の実行計画

Oracle 実行計画の読み方。DBMS_XPLAN・AUTOTRACE・V$SQL_PLAN・TKPROF・ヒント（12c 以降）。

EXPLAIN PLAN FOR と DBMS_XPLAN

Oracle での実行計画の王道は EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY の 2 段構えです:

EXPLAIN PLAN FOR <SQL> で計画を PLAN_TABLE に格納（クエリは実行されない）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) で整形表示

出力は Id / Operation / Name / Rows / Bytes / Cost / Time の列を持つ表形式で、インデントで木構造を表現します。末尾に Predicate Information セクションが付き、「どの述語が access（索引検索条件）で、どれが filter（取得後フィルタ）か」が明記されます。ここを読むと、期待どおりインデックスシークされているかが一発で分かります。

sql

EXPLAIN PLAN FOR
SELECT o.id, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID' AND u.plan = 'PRO';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 出力例 (抜粋)
-- ---------------------------------------------------------------------
-- | Id | Operation                    | Name           | Rows | Cost |
-- ---------------------------------------------------------------------
-- |  0 | SELECT STATEMENT             |                |  120 |   18 |
-- |  1 |  NESTED LOOPS                |                |  120 |   18 |
-- |  2 |   TABLE ACCESS BY INDEX ROWID| ORDERS         |  120 |    6 |
-- |* 3 |    INDEX RANGE SCAN          | IDX_ORDERS_STS |  500 |    2 |
-- |  4 |   TABLE ACCESS BY INDEX ROWID| USERS          |    1 |    1 |
-- |* 5 |    INDEX UNIQUE SCAN         | PK_USERS       |    1 |    0 |
-- ---------------------------------------------------------------------
-- Predicate Information (identified by operation id):
--    3 - access("O"."STATUS"='PAID')
--    5 - access("U"."ID"="O"."USER_ID")

AUTOTRACE で実行と統計を一度に見る

SQL*Plus / SQLcl なら SET AUTOTRACE ON が一番お手軽です。クエリを実行しながらプラン + 統計（consistent gets / physical reads / sorts / redo size など）を一括で出してくれます。

AUTOTRACE ON: 結果 + プラン + 統計
AUTOTRACE TRACEONLY: 結果を画面に出さず、プラン + 統計のみ。大量結果で便利
AUTOTRACE TRACEONLY EXPLAIN: 実行せずプランのみ
事前に PLUSTRACE ロール付与と PLAN_TABLE 作成が必要（?/rdbms/admin/utlxplan.sql）

consistent gets（論理読み取り）の減少は、SQL Server の logical reads と同じくチューニング効果の一番シンプルな指標です。

sql

-- セッションで有効化
SET AUTOTRACE ON

SELECT COUNT(*) FROM orders WHERE status = 'PAID';

-- 出力末尾 (統計セクション抜粋)
-- Statistics
-- ----------------------------------------------------------
--     0  recursive calls
--     0  db block gets
--   142  consistent gets     <-- これが論理読み取り
--     0  physical reads
--     1  rows processed

実際に実行された計画を V$SQL_PLAN で見る

EXPLAIN PLAN FOR は推定ですが、本番で実際に使われた計画は共有プールにキャッシュされています。DBMS_XPLAN.DISPLAY_CURSOR で取り出すのが実務の定番:

DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, format): 指定した SQL_ID の実計画を表示
format => 'ALLSTATS LAST': 直近実行の E-Rows / A-Rows / A-Time / Buffers を表示（STATISTICS_LEVEL=ALL または /*+ GATHER_PLAN_STATISTICS */ が必要）
SQL_ID は V$SQL / V$SQLAREA から検索、または直前に実行したクエリなら sql_id => NULL で取得可

A-Rows（実測）と E-Rows（推定）の乖離が大きい箇所が、統計情報・ヒストグラム・相関列の問題の手がかりです。

sql

-- 対象クエリにヒントを付けて実行
SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*) FROM orders WHERE status = 'PAID';

-- 直近実行の実計画 + 実測行数
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id       => NULL,
    cursor_child_no => 0,
    format       => 'ALLSTATS LAST'));

-- SQL_ID で検索したいとき
SELECT sql_id, child_number, executions, elapsed_time
FROM v$sql
WHERE sql_text LIKE '%orders%status%PAID%'
ORDER BY last_active_time DESC;

SQL Trace と TKPROF で徹底調査

より深い調査には SQL Trace + TKPROF。実行された SQL・待機イベント・バインド値・再帰 SQL までトレースファイルに落とし、tkprof コマンドで整形レポート化します。

ALTER SESSION SET sql_trace = TRUE;（または DBMS_MONITOR.SESSION_TRACE_ENABLE）
調査対象を実行
トレースファイルを V$DIAG_INFO の Default Trace File から取得
シェルで tkprof trace.trc report.txt sort=exeela などで整形

Oracle 固有の強みは待機イベントの粒度。CPU を食っているのか、db file sequential read（単一ブロック読み）で詰まっているのか、log file sync（COMMIT 待ち）なのかが分かるので、DB 本体の問題なのか IO サブシステムの問題なのかを切り分けられます。

sql

-- セッションでトレース開始
ALTER SESSION SET tracefile_identifier = 'my_probe';
ALTER SESSION SET sql_trace = TRUE;

-- 調査対象を実行
SELECT /* probe */ COUNT(*) FROM orders WHERE status = 'PAID';

ALTER SESSION SET sql_trace = FALSE;

-- トレースファイルの場所
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- シェル側で整形
-- $ tkprof /u01/.../xxx_my_probe.trc report.txt sort=exeela

カーディナリティフィードバックと適応計画

Oracle 12c 以降は推定と実測の乖離を自動で学習する仕組みが入っています:

Cardinality Feedback（11gR2〜）: 1 回目の実行で推定が大きく外れた場合、2 回目以降のために補正値をキャッシュ
Adaptive Plans（12c〜）: 結合を Nested Loops と Hash Join の両方用意しておき、実際の行数を見て実行中に切り替える
Automatic Reoptimization: 実行後に誤推定を検知し、次回実行で計画を差し替え

便利な反面、2 回目で計画が変わるため「昨日は速かったのに今日は遅い」現象の原因になることも。Production で急変があれば V$SQL_REOPTIMIZATION_HINTS や DBA_SQL_PLAN_BASELINES を確認し、必要なら SQL Plan Baseline で計画を固定します。

オプティマイザヒントで計画を誘導する

Oracle は他 RDBMS よりヒント文化が強く、コメント形式 /*+ ... */ でオプティマイザに指示を出せます。乱用は禁物ですが、緊急時や「どうしても計画を固定したい」場面で有効です:

/*+ INDEX(o idx_orders_status) */: 指定インデックスを使う
/*+ FULL(o) */: フルスキャンを強制
/*+ USE_NL(o u) */ / /*+ USE_HASH(o u) */: 結合方式を指定
/*+ LEADING(o u) */: 駆動表を指定
/*+ PARALLEL(8) */: 並列度を指定
/*+ GATHER_PLAN_STATISTICS */: 前節のとおり A-Rows 取得用

ヒントは「オプティマイザへの命令」であり、一度埋めたらDB 統計が変わっても動き続けるので、塩漬け化しやすい副作用があります。まずは統計情報再収集・インデックス設計・クエリ書き換えで直すのが本筋で、ヒントは最後の手段として位置付けるのが健全です。

sql

-- インデックスを使わせる
SELECT /*+ INDEX(o idx_orders_status) */ *
FROM   orders o
WHERE  o.status = 'PAID';

-- 駆動表と結合方式を指定
SELECT /*+ LEADING(o u) USE_NL(u) */
       o.id, u.name
FROM   orders o
JOIN   users  u ON u.id = o.user_id
WHERE  o.status = 'PAID';