OracleのSQLが遅い原因を特定して爆速にする方法【インデックス・実行計画・ヒント句】

「このSQLなんで遅いの？」——現場でよくある相談です。原因を特定せずに闇雲にインデックスを追加しても逆効果になることも。本記事では遅いSQLの原因特定→改善の流れを体系的に解説します。

1. 遅いSQLを特定する方法 {#find-slow-sql}

現在実行中の遅いSQLをリアルタイムで確認

SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.STATUS,
    ROUND(sq.ELAPSED_TIME / 1000000, 1) AS 経過秒,
    ROUND(sq.CPU_TIME / 1000000, 1)     AS CPU秒,
    sq.SQL_TEXT
FROM
    V$SESSION s
    JOIN V$SQL sq ON s.SQL_ID = sq.SQL_ID
WHERE
    s.STATUS = 'ACTIVE'
    AND s.USERNAME IS NOT NULL
ORDER BY
    経過秒 DESC;

過去の重いSQLをSQLIDで確認（V$SQL）

SELECT
    SQL_ID,
    ROUND(ELAPSED_TIME / 1000000 / EXECUTIONS, 2) AS 平均実行秒,
    EXECUTIONS                                      AS 実行回数,
    ROUND(ELAPSED_TIME / 1000000, 1)               AS 合計秒,
    ROUND(CPU_TIME / 1000000, 1)                   AS CPU時間秒,
    DISK_READS                                      AS 物理読み込み,
    BUFFER_GETS                                     AS バッファGetS,
    SUBSTR(SQL_TEXT, 1, 80)                        AS SQL先頭
FROM
    V$SQL
WHERE
    EXECUTIONS > 0
ORDER BY
    平均実行秒 DESC
FETCH FIRST 20 ROWS ONLY;

💡 DISK_READSが多い → フルスキャンが疑われる
💡 BUFFER_GETSが多い → インデックスや結合が非効率の可能性

2. 実行計画を読んで原因を突き止める {#execution-plan}

EXPLAINで実行計画を確認する

EXPLAIN PLAN FOR
SELECT e.ENAME, d.DNAME
FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO
WHERE e.SAL > 2000;

-- 結果を見やすく表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

出力例：

---------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     5 |    7 |
|*  1 |  HASH JOIN                  |         |     5 |    7 |
|   2 |   TABLE ACCESS FULL         | DEPT    |     4 |    3 |  ← ⚠️ フルスキャン
|*  3 |   TABLE ACCESS FULL         | EMP     |     5 |    3 |  ← ⚠️ フルスキャン
---------------------------------------------------------------
Predicate Information:
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("E"."SAL">2000)

主要オペレーションの意味

実際に実行した計画を確認する（より精度が高い）

-- SQL_IDを指定して実際の実行計画を確認
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR('sql_idをここに入力', 0, 'ALLSTATS LAST')
);

3. インデックスが使われない5つの理由 {#no-index}

インデックスがあるのに使われない！——よくある落とし穴を解説します。

① 列に関数を使っている

-- ❌ インデックス無効（TO_CHARで列を変換している）
WHERE TO_CHAR(HIREDATE, 'YYYY') = '2023'

-- ✅ インデックス有効
WHERE HIREDATE >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
  AND HIREDATE <  TO_DATE('2024-01-01', 'YYYY-MM-DD')

② 暗黙的な型変換が起きている

-- ❌ EMPNO（NUMBER）に文字列を渡している
WHERE EMPNO = '7369'   -- Oracleが内部でTO_NUMBER変換→インデックス無効

-- ✅ 正しい型で渡す
WHERE EMPNO = 7369

③ LIKE句が前方一致でない

-- ❌ 後方・中間一致はインデックス不可
WHERE ENAME LIKE '%SMITH%'
WHERE ENAME LIKE '%ITH'

-- ✅ 前方一致のみインデックス有効
WHERE ENAME LIKE 'SMITH%'

④ NULL条件の扱い

-- ❌ IS NULL / IS NOT NULLはインデックスが効かない場合がある
WHERE MGR IS NULL

-- ✅ 関数インデックスで対応可
CREATE INDEX IDX_EMP_MGR_NULL ON EMP(NVL(MGR, -1));
WHERE NVL(MGR, -1) = -1

⑤ 統計情報が古い / カーディナリティが低い

選択率が低いカラム（男女フラグ等）はインデックスよりフルスキャンの方が速いとオプティマイザが判断することがあります。→ 後述の統計情報セクションへ。

4. インデックスを作成・最適化する {#create-index}

基本のインデックス作成

-- 単一列インデックス
CREATE INDEX IDX_EMP_SAL ON EMP(SAL);

-- 複合インデックス（カーディナリティが高い列を先頭に）
CREATE INDEX IDX_EMP_DEPT_SAL ON EMP(DEPTNO, SAL);

-- 既存インデックスの確認
SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS, STATUS
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMP';

複合インデックスの列順序が重要

-- インデックス: IDX_EMP_DEPT_SAL (DEPTNO, SAL)
-- ✅ 先頭列(DEPTNO)を使用 → インデックス有効
WHERE DEPTNO = 10 AND SAL > 2000

-- ✅ 先頭列のみ → インデックス有効（部分的）
WHERE DEPTNO = 10

-- ❌ 先頭列なし → インデックス無効
WHERE SAL > 2000

カーディナリティとは、その列に存在する値の種類の数のことです。顧客IDや注文番号のようにほぼ重複がない列は「カーディナリティが高い」、性別や状態フラグのように種類が少ない列は「カーディナリティが低い」と言います。複合インデックスでは、先頭の列でいかに検索範囲を絞れるかが性能を左右するため、カーディナリティの高い列を先頭に配置するのが基本原則です。

5. ヒント句で実行計画を強制変更する {#hints}

オプティマイザが間違った計画を選ぶときの最終手段です。

よく使うヒント句一覧

-- フルスキャンを強制
SELECT /*+ FULL(e) */ * FROM EMP e WHERE SAL > 2000;

-- 特定インデックスを強制使用
SELECT /*+ INDEX(e IDX_EMP_SAL) */ * FROM EMP e WHERE SAL > 2000;

-- ハッシュ結合を強制
SELECT /*+ USE_HASH(e d) */ e.ENAME, d.DNAME
FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO;

-- ネステッドループを強制
SELECT /*+ USE_NL(e d) */ e.ENAME, d.DNAME
FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO;

-- 並列実行（大量データ処理に有効）
SELECT /*+ PARALLEL(e, 4) */ * FROM EMP e;

⚠️ 注意: ヒント句は統計情報を正しく保てば不要になることが多いです。根本原因（統計情報・インデックス設計）を解消することを優先してください。

6. 統計情報が古いと遅くなる理由と対処 {#statistics}

統計情報の確認

-- テーブルの統計情報の最終収集日
SELECT
    TABLE_NAME,
    NUM_ROWS,
    LAST_ANALYZED
FROM
    USER_TABLES
WHERE
    TABLE_NAME = 'EMP';

統計情報を手動で収集する

-- 特定テーブルの統計情報を収集
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname   => 'SCOTT',
    tabname   => 'EMP',
    cascade   => TRUE,   -- インデックス統計も同時収集
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

⚠️ 大テーブルへの統計収集は業務オフ時間に実行してください。実行中はSQLパフォーマンスが低下する場合があります。

7. よくある遅いパターンとチューニング例 {#patterns}

パターン①：IN句に大量の値

-- ❌ 遅い（IN句にサブクエリが非効率なケース）
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'TOKYO')

-- ✅ EXISTS句に書き換え（大テーブルに有効）
WHERE EXISTS (
    SELECT 1 FROM DEPT d
    WHERE d.DEPTNO = e.DEPTNO AND d.LOC = 'TOKYO'
)

パターン②：DISTINCT の多用

-- ❌ DISTINCT は全行のソートが発生
SELECT DISTINCT DEPTNO FROM EMP

-- ✅ GROUP BYに書き換え（インデックスが効きやすい）
SELECT DEPTNO FROM EMP GROUP BY DEPTNO

パターン③：NOT IN に NULL が混ざる

-- ❌ サブクエリにNULLが含まれると全件ヒットしない
WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT)

-- ✅ NOT EXISTS に書き換え
WHERE NOT EXISTS (
    SELECT 1 FROM DEPT d WHERE d.DEPTNO = e.DEPTNO
)

パターン④：ROWNUM と ORDER BYの順序問題

-- ❌ ORDER BYの前にROWNUMが適用される
SELECT * FROM EMP WHERE ROWNUM <= 10 ORDER BY SAL DESC;

-- ✅ サブクエリでソートしてからROWNUM
SELECT * FROM (
    SELECT * FROM EMP ORDER BY SAL DESC
) WHERE ROWNUM <= 10;

8. まとめ：チューニング手順チェックリスト {#summary}

遅いSQLのチューニング手順
│
├── Step1: 遅いSQLを特定（V$SQL, V$SESSION）
│
├── Step2: 実行計画を確認（EXPLAIN PLAN / DBMS_XPLAN）
│         │
│         ├── TABLE ACCESS FULL → インデックスを検討
│         ├── 高コスト結合 → 結合順序・型を確認
│         └── Rowsの見積もりがズレている → 統計情報を収集
│
├── Step3: インデックスが効かない原因を調査
│         └── 関数・型変換・LIKE前後方一致・NULL条件を確認
│
├── Step4: 改善策の実施
│         ├── インデックス作成/再作成
│         ├── 統計情報の再収集
│         ├── SQL書き換え
│         └── ヒント句（最終手段）
│
└── Step5: 改善後の実行計画・実行時間を再確認

コスト削減の優先順位：

1. インデックスを正しく使えるようにSQLを修正（最優先）
2. 統計情報を最新にする
3. インデックスを追加・見直す
4. ヒント句を使う（最終手段）

関連記事：

• Oracle 実行計画の確認方法
• Oracleテーブル定義の確認方法