Oracle実行計画の読み方とチューニング手法｜結合方式・結合順序を実測で徹底解説

はじめに

Oracleデータベースのパフォーマンス問題に直面したとき、最も重要になるのが「実行計画（Execution Plan）」の解析です。AWRやASHで負荷の高いSQLを特定することはできますが、「なぜ遅いのか？」という根本原因を突き止めるには、実行計画の理解が不可欠です。

本記事では、Oracle実行計画の基本から、実務で使える解析・チューニングの考え方までを体系的に解説します。

実行計画とは何か

OracleはSQLを受け取ると、そのまま実行するのではなく、内部で最も効率的と判断した処理手順に変換してから実行します。この処理手順が「実行計画」です。

実行計画では主に以下のような内容が決定されます。

  • テーブルへのアクセス方法（フルスキャン or インデックス）
  • テーブルの結合順序
  • 結合方式（ネステッドループ、ハッシュ結合など）

同じSQLでも、データ量や統計情報によって実行計画は変化し、性能に大きな差が生まれます。たとえば、インデックスを利用した場合は数ミリ秒で終わる処理でも、フルスキャンが選択されることで数秒以上かかることがあります。

SQLの性能問題が発生した際は、AWRやASHによる分析に加えて実行計画を確認することで、処理ボトルネックを特定し、より効果的なチューニングにつなげることができます。

AWRおよびASHの分析方法は以下でまとめていますので、ぜひ参考にしてみてください。
Oracle AWRレポートの見方と解析ポイントを徹底解説｜パフォーマンス改善・トラブル調査に必須
Oracle ASH解析の実践ノウハウ｜CPU・I/O・ロック問題の切り分け手法

実行計画取得方法

それでは早速、実行計画を取得してみましょう。ここでは、解析用にテスト用のテーブルを作成して実行計画を取得してみます。

SQL> CREATE TABLE test_table AS
SELECT
 LEVEL AS id,
 DBMS_RANDOM.STRING('A', 100) AS data
FROM dual
CONNECT BY LEVEL <= 1000000;

Table created.

SQL>

DBMS_XPLAN.DISPLAY_CURSORによる実行計画の実測確認

DBMS_XPLAN.DISPLAY_CURSOR 関数を使うことで、実行済みSQLの実行計画の予測と実測を表示することができます。パフォーマンス問題が発生した際には、DBMS_XPLAN.DISPLAYよりもDBMS_XPLAN.DISPLAY_CURSOR を使用することを推奨します。
さらにDBMS_XPLAN.DISPLAYとは異なり、SQL_ID指定で情報取得することも可能です。

以下のコマンドで実行計画を取得します。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘cp7rzu66ff0qt’, NULL, ‘ALLSTATS LAST’));

・第一引数（‘cp7rzu66ff0qt’）
取得対象のSQL_IDを指定します。SQL_IDを指定せずにNULLを記載すると、直前に実行したSQLの実行計画が出力されます。

・第二引数（‘NULL’）
child cursor番号を指定する項目です。NULLを記載すると、すべてのchild cursorが対象となります。

・第三引数（‘ALLSTATS LAST‘）
表示フォーマットを指定する項目です。
ここで指定しているALLSTATS LASTとは、実行計画に「実際の実行結果（実測値）」を表示するためのオプションです。具体的には、各処理ごとの 実行行数（A-Rows）やI/O量 などが確認でき、オプティマイザの見積もり（E-Rows）とのズレを把握できます。SQLチューニングでは、推定ではなく実測を確認することが重要なため、実務では必須のオプションです。

続いて、解析対象のテーブルに対してSQLを実行します。/*+ gather_plan_statistics */ * というヒント句をつけることで、実行統計の実測値を測定することができます。

SQL> SELECT /*+ gather_plan_statistics */ *
FROM test_table
WHERE data LIKE 'ABC%';

先ほど実行したSQLのSQLIDを確認します。「cp7rzu66ff0qt」がSQL_IDです。

SQL> SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%test_table%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
7bs0s1umbq307
SELECT /*+ gather_plan_statistics */ * FROM test_table WHERE data LIKE 'ABC%'

SQL>

SQL_ID指定でDBMS_XPLAN.DISPLAY_CURSOR関数により実行計画を取得します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7bs0s1umbq307', NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7bs0s1umbq307, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM test_table WHERE data LIKE
'ABC%'

Plan hash value: 3979868219

---------------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      5 |00:00:00.20 |   15390 |  15385 |
|*  1 |  TABLE ACCESS FULL| TEST_TABLE |      1 |    169 |      5 |00:00:00.20 |   15390 |  15385 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DATA" LIKE 'ABC%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


23 rows selected.

SQL>

以下の情報を確認することが可能です。

項目意味
SQL_IDSQL_ID
child numberSQL_IDに紐づく「子カーソル（実行計画）」の識別番号
Plan hash value実行計画の構造を一意に識別するためのハッシュ値
Id実行計画内の各処理ステップに付けられた番号
Operation実行される処理内容
※インデントが右にある処理から実行されていることを意味します。
Name対象オブジェクト名
Startsその処理が何回実行されたか
E-Rowsオプティマイザの見積もり行数
A-Rows実際に処理された行数
A-Time実際にかかった時間
Buffers論理I/O（メモリ＋キャッシュアクセス）
チューニングの際には本項目を注視してください。
Reads物理I/O（ディスクからの読み込み）

特にチェックすべきはE-Rows（見積もり）とA-Rows（実測）の差です。この差がパフォーマンス問題の調査に重要な情報となります。

DBMS_XPLAN.DISPLAYによる実行計画の確認

DBMS_XPLAN.DISPLAYは、EXPLAIN PLANで取得した実行計画の予測を表示するための関数です。現場では、こちらの方法で実行計画を取得することもよくありますが、あくまで予測であり実測を表示しているわけではない点に注意してください。

以下のコマンドで実行計画を取得します。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => ‘ALL’));

・第一引数（format => ‘ALL’）
format=’ALL’を指定することで、実行計画の詳細情報（コストやPredicate（フィルタ条件）、オプティマイザ情報など）を確認することができます。ただし、EXPLAIN PLANベースの情報であるため、実測値は含まれない点に注意が必要です。

それでは実際に実行計画を取得していきましょう。EXPLAIN PLAN FOR というコマンドを実行することで、SQLを実行せずにオプティマイザが選択する実行計画（予測）を取得することができます。

SQL> EXPLAIN PLAN FOR
SELECT * FROM test_table WHERE data LIKE 'ABC%';

Explained.

SQL>

続いて、DBMS_XPLAN.DISPLAY により実行計画を取得します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3979868219

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   169 |   332K|  4179   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TABLE |   169 |   332K|  4179   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / TEST_TABLE@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DATA" LIKE 'ABC%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "TEST_TABLE"."ID"[NUMBER,22], "DATA"[VARCHAR2,4000]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

27 rows selected.

SQL>

以下の情報を確認することが可能です。

項目意味
Plan hash value実行計画の構造を一意に識別するためのハッシュ値
Id実行計画内の各処理ステップに付けられた番号
Operation実行される処理内容
※インデントが右にある処理から実行されていることを意味します。
Name対象オブジェクト名
Rowsオプティマイザの見積もり行数
Bytes返却データの見積もりサイズ
Cost処理コスト（相対値）
(%CPU)コストに占めるCPU割合の予測値 ※CPU使用率とは異なる
Time推定実行時間

実行計画の分析方法

実行計画はツリー構造になっており、下（Operationのインデントが右の処理）から上（Operationのインデントが左の処理）に処理が流れます。上から読むと誤解するため注意が必要です。実例を用いて分析方法をご説明します。

テーブルへのアクセス方法

Oracleの実行計画では、テーブルへのアクセス方法がOperation項目に表示されます。先ほどの例では、TABLE ACCESS FULL が発生していることが確認できます。

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   169 |   332K|  4179   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TABLE |   169 |   332K|  4179   (1)| 00:00:01 |
--------------------------------------------------------------------------------

代表的なテーブルアクセス方法は以下の通りです。

テーブルアクセス方法説明処理速度
TABLE ACCESS FULLテーブル全件を順番に読み込む方式
・データ量が多いと処理遅延が発生しやすい（I/O増大）
・データ量が少ないテーブルや全量を取得する処理であればTABLE ACCESS FULLでも問題ない		低
TABLE ACCESS BY INDEX ROWIDインデックスでROWIDを取得し、そのROWIDを使ってテーブルから行を取得する方式
・INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID の流れになることが一般的
・インデックスアクセス＋テーブルアクセスの2段階
・必要な行だけ取得できる
・ランダムI/Oが発生しやすい		中
INDEX RANGE SCANインデックスの一部範囲をスキャンする方式
・範囲検索に最適
・インデックス順にデータ取得可能
・ソートを省略できる場合あり		高
INDEX UNIQUE SCAN主キーやユニークインデックスを使って1件だけ取得する方式
・最速レベルのアクセス
・1行のみ取得		高

参考資料：Optimizer Access Paths

試しにインデックスを作成して、TABLE ACCESS FULLが発生しないように変更してみましょう。
まず以下の通り、インデックスを作成してください。

SQL> CREATE INDEX idx_test_data ON test_table(data);

Index created.

SQL>

先ほど同じSQLを実行後、実行計画を取得するとTABLE ACCESS BY INDEX ROWID BATCHEDに代わっており、インデックスが有効に動作していることが確認できました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1gg8f9byv1b2t', NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1gg8f9byv1b2t, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM test_table WHERE data LIKE
'ABC%'

Plan hash value: 1957514202

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |      5 |00:00:00.01 |       9 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TABLE    |      1 |      5 |      5 |00:00:00.01 |       9 |
|*  2 |   INDEX RANGE SCAN                  | IDX_TEST_DATA |      1 |      5 |      5 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DATA" LIKE 'ABC%')
       filter("DATA" LIKE 'ABC%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

テーブルの結合順序

テーブルの結合順序についてもオプティマイザの判断が入るポイントとなります。

どのような順序でテーブルが結合されているのか確認してみましょう。検証用のテーブルを下記の通り作成してください。

SQL> CREATE TABLE t1 AS
SELECT LEVEL id, MOD(LEVEL, 10) col1
FROM dual CONNECT BY LEVEL <= 100000;

Table created.

SQL> CREATE TABLE t2 AS
SELECT LEVEL id, MOD(LEVEL, 1000) col1
FROM dual CONNECT BY LEVEL <= 100000;

Table created.

SQL> CREATE TABLE t3 AS
SELECT LEVEL id, MOD(LEVEL, 10000) col1
FROM dual CONNECT BY LEVEL <= 100000;

Table created.

SQL>

続いて、インデックスを作成します。

SQL> CREATE INDEX idx_t1 ON t1(col1);

Index created.

SQL> CREATE INDEX idx_t2 ON t2(col1);

Index created.

SQL> CREATE INDEX idx_t3 ON t3(col1);

Index created.

SQL>

それでは3つのテーブルを結合してレコードをSELECTしてみましょう。

SQL> SELECT /*+ gather_plan_statistics */
*
FROM t1
JOIN t2 ON t1.col1 = t2.col1
JOIN t3 ON t2.col1 = t3.col1
WHERE t3.col1 = 1;

しばらくしてSQL処理が完了したら、SQL_IDを特定して実行計画を取得してみましょう。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('35nt8qtu7twau', NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  35nt8qtu7twau, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM t1 JOIN t2 ON t1.col1 =
t2.col1 JOIN t3 ON t2.col1 = t3.col1 WHERE t3.col1 = 1

Plan hash value: 1751087805

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |        |      1 |        |     10M|00:00:03.70 |    2066 |       |       |          |
|*  1 |  HASH JOIN                            |        |      1 |     10M|     10M|00:00:03.70 |    2066 |  1506K|  1506K|  502K (0)|
|*  2 |   HASH JOIN                           |        |      1 |   1000 |   1000 |00:00:00.01 |     207 |  1995K|  1995K|  704K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T3     |      1 |     10 |     10 |00:00:00.01 |      12 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | IDX_T3 |      1 |     10 |     10 |00:00:00.01 |       2 |       |       |          |
|*  5 |    TABLE ACCESS FULL                  | T2     |      1 |    100 |    100 |00:00:00.01 |     195 |       |       |          |
|*  6 |   TABLE ACCESS FULL                   | T1     |      1 |  10311 |  10000 |00:00:00.03 |    1803 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."COL1"="T2"."COL1")
   2 - access("T2"."COL1"="T3"."COL1")
   4 - access("T3"."COL1"=1)
   5 - filter("T2"."COL1"=1)
   6 - filter("T1"."COL1"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


32 rows selected.

Elapsed: 00:00:00.06
SQL>

処理の流れは以下の通りとなります。
1. T3テーブルに対してINDEX RANGE SCANを実行 (Id=4)
2. T3テーブルにてTABLE ACCESS BY INDEX ROWID BATCHEDを実行 (Id=3)
3. T2テーブルに対してTABLE ACCESS FULLを実行 (Id=5)
4. T3テーブルとT2テーブルをHASH JOIN (Id=2)
5. T1テーブルに対してTABLE ACCESS FULLを実行 (Id=6)
6. T2テーブルとT1テーブルをHASH JOIN (Id=1)

小さいテーブルからスキャンして結合していくことで、途中で扱うデータ量が少なくなり、処理時間を短くすることができます。Oracleのオプティマイザによって、最適な実行計画が作成されています。A-Timeを確認すると、3.7秒で完了していることが分かります。

それでは、結合の順序を変更したらどうなるか試してみましょう。ヒント句 LEADING を付けることで、T1⇔T2、T2⇔T3の順番で結合してみます。

SQL> SELECT /*+ LEADING(t1 t2 t3) gather_plan_statistics */
*
FROM t1
JOIN t2 ON t1.col1 = t2.col1
JOIN t3 ON t2.col1 = t3.col1
WHERE t3.col1 = 1;

実行計画を取得します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  06fdjvt2xs26p, child number 0
-------------------------------------
SELECT /*+ LEADING(t1 t2 t3) gather_plan_statistics */ * FROM t1 JOIN
t2 ON t1.col1 = t2.col1 JOIN t3 ON t2.col1 = t3.col1 WHERE t3.col1 = 1

Plan hash value: 3474823176

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |        |      1 |        |     10M|00:00:11.12 |     488 |       |       |          |
|   1 |  MERGE JOIN                           |        |      1 |     10M|     10M|00:00:11.12 |     488 |       |       |          |
|*  2 |   HASH JOIN                           |        |      1 |   1031K|   1000K|00:00:01.16 |     476 |  2161K|  2161K| 1339K (0)|
|*  3 |    TABLE ACCESS FULL                  | T1     |      1 |  10311 |  10000 |00:00:00.01 |     181 |       |       |          |
|*  4 |    TABLE ACCESS FULL                  | T2     |      1 |    100 |    100 |00:00:00.01 |     295 |       |       |          |
|*  5 |   SORT JOIN                           |        |   1000K|     10 |     10M|00:00:02.46 |      12 |  2048 |  2048 | 2048  (0)|
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED| T3     |      1 |     10 |     10 |00:00:00.01 |      12 |       |       |          |
|*  7 |     INDEX RANGE SCAN                  | IDX_T3 |      1 |     10 |     10 |00:00:00.01 |       2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."COL1"="T2"."COL1")
   3 - filter("T1"."COL1"=1)
   4 - filter("T2"."COL1"=1)
   5 - access("T2"."COL1"="T3"."COL1")
       filter("T2"."COL1"="T3"."COL1")
   7 - access("T3"."COL1"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


34 rows selected.

SQL>

T1テーブルとT2テーブルが先にHASH JOINしていることが見て取れます。また、A-Timeが11.12秒となっており、先ほどの3.7秒と比較するとかなり遅くなっていることが分かります。適切な順番でテーブルを結合することが重要だということが分かっていただけたかと思います。

※本検証から分かる通り、オプティマイザが選択したHASH JOINの方が、実際には高速であり、ヒント句による強制変更は必ずしも性能改善につながるとは限りません。

※A-Time（実行時間）は環境やキャッシュ状態に依存するため、チューニングではBuffers（論理I/O）に着目することも重要です。

※本記事の検証結果はあくまで一例であり、データ分布・統計情報・メモリサイズ（PGA）などの環境要因によって最適な結合方式は変化します。

結合方式

結合方式であるJOINの種類は処理速度に直結します。代表的な結合方式は以下となります。

結合方式説明強いパターン弱いパターン
NESTED LOOPS JOIN
（ネステッドループ結合）		1行ずつ繰り返し結合する方式データ量小 × インデックスあり大量データ
HASH JOIN
（ハッシュ結合）		小さいテーブルでハッシュテーブルを作り、大きいテーブルをスキャンして結合する方式データ量大 × インデックスなしメモリ不足
SORT MERGE JOIN
（ソートマージ結合）		両方のテーブルをソートしてからマージソート済み・範囲条件ソートコスト

結合方式を変更することで、処理速度が変わることを検証してみましょう。
まず、以下の通り検証用のテーブルを作成してください。

SQL> CREATE TABLE t_small AS
SELECT level AS id, 'X' AS val
FROM dual CONNECT BY level <= 1000;

Table created.

SQL> CREATE TABLE t_large AS
SELECT mod(level,1000) AS id, rpad('X',100,'X') AS val
FROM dual CONNECT BY level <= 1000000;

Table created.

SQL>

続いて、t_largeテーブルに対してインデックスを作成します。

SQL> CREATE INDEX idx_large_id ON t_large(id);

Index created.

SQL>

それでは結合を含んだSELECT文を実行してみましょう。

SQL> SELECT /*+ gather_plan_statistics */
* FROM t_small s JOIN t_large l ON s.id = l.id;

実行計画を取得します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5p184dfzymm8a', NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5p184dfzymm8a, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM t_small s JOIN t_large l ON
s.id = l.id

Plan hash value: 1258660132

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |    999K|00:00:03.53 |   80960 |       |       |          |
|*  1 |  HASH JOIN         |         |      1 |   1166K|    999K|00:00:03.53 |   80960 |  1856K|  1856K| 1560K (0)|
|   2 |   TABLE ACCESS FULL| T_SMALL |      1 |   1000 |   1000 |00:00:00.01 |       4 |       |       |          |
|   3 |   TABLE ACCESS FULL| T_LARGE |      1 |   1175K|   1000K|00:00:02.11 |   80956 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."ID"="L"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan


26 rows selected.

Elapsed: 00:00:00.29
SQL>

HASH JOINが選択されたことが確認できました。また、A-Timeから3.53秒かかっていることが確認できます。

続いて、LEADING句を追加して、NESTED LOOPS JOINを適用してみます。

SQL> SELECT /*+ gather_plan_statistics LEADING(s) USE_NL(l) */
* FROM t_small s JOIN t_large l ON s.id = l.id;

実行計画を取得します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('c3mxu3mfa7zpk', NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c3mxu3mfa7zpk, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics LEADING(s) USE_NL(l) */ * FROM
t_small s JOIN t_large l ON s.id = l.id

Plan hash value: 2280289917

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |    999K|00:00:16.58 |    1071K|
|   1 |  NESTED LOOPS                |              |      1 |   1166K|    999K|00:00:16.58 |    1071K|
|   2 |   NESTED LOOPS               |              |      1 |   1166K|    999K|00:00:04.33 |   72537 |
|   3 |    TABLE ACCESS FULL         | T_SMALL      |      1 |   1000 |   1000 |00:00:00.01 |    1002 |
|*  4 |    INDEX RANGE SCAN          | IDX_LARGE_ID |   1000 |   1166 |    999K|00:00:02.91 |   71535 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T_LARGE      |    999K|   1166 |    999K|00:00:11.44 |     999K|
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("S"."ID"="L"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


27 rows selected.

Elapsed: 00:00:00.09

NESTED LOOPSになっていることが確認できました。HASH JOINの際は3.53秒で終わっていましたが、NESTED LOOPSになって16.58秒もかかっていることが分かります。

Nested Loopでは、外側1000件に対して内側テーブルへ約100万回アクセスが発生し、
ランダムI/Oが増大したため、HASH JOINよりも大幅に遅くなっています。

まとめ

本記事では、Oracle実行計画の基本から、実務での分析・チューニング手法まで解説しました。

重要なポイントは以下の通りです。

・実行計画はSQL性能の本質を示す
・EXPLAIN PLANではなく実測（DISPLAY_CURSOR）を確認する
・E-RowsとA-Rowsの差から見積もり精度を評価する
・Buffers（論理I/O）を重視する
・結合順序・結合方式は性能に大きく影響する
・ヒント句は最終手段であり、オプティマイザの判断が正しい場合も多い

実行計画を正しく読み解くことで、SQLのボトルネックを特定し、
効果的なパフォーマンス改善につなげることが可能になります。

スポンサーリンク

コメント