2021.06.10
SQLチューニング（第7回）「サブクエリ動作方式を理解すること」(5/8)
今回は、「サブクエリ動作方式を制御するヒント」についてです。早速、内容に入っていきましょう。
2.1.2.3 サブクエリ動作方式を制御するヒント
今までFilterと結合動作方式に対する長所と短所についてみてきた。 しかし、長所と短所だけ知っていることで、実際にサブクエリを使っているSQLによって発生する性能問題を把握することはできるだろうが、改善方法を適用することが難しいこともある。 したがって原因と問題点把握も重要だが、性能問題発生時の解決方法も共に分かっておかなければならない。
サブクエリーによる性能問題を解決するためには、Filter動作方式と結合動作方式の完璧なな理解が必要である。 さらにSQLの実行計画を制御するヒントの種類と意味、そして使用法まで知っていなければならない。 したがって今回の単元ではサブクエリ動作方式を制御できるヒントがどんなことがあるのかを調べ、実際にSQLにヒントを付与して実行計画を変更してみる。
テーブル2-1はサブクエリの実行計画を制御できるヒントと、その意味を整理した表である。
| Hint名 | 説明 |
|---|---|
| NO_UNNEST | サブクエリをFILTER動作方式で処理する場合、サブクエリにNO_UNNESTヒントを使用する。 |
| UNNEST | FILTER動作方式を選択しないで結合動作方式で処理する場合、サブクエリにUNNESTヒントを使用する。 |
| NL_SJ | EXISTSやIN条件を使った場合、サブクエリにUNNESTとともにNL_SJヒントを使えば、NESTED LOOPS JOIN SEMIで処理するようにすることができる。 |
| HASH_SJ | EXISTSやIN条件を使った場合、サブクエリにUNNESTとともにHASH_SJヒントを付与すれば、HASH JOIN SEMIで処理するように制御することができる。 |
| NL_AJ | NOT EXISTSやNOT IN条件を使った場合、サブクエリにUNNESTとともにNL_AJヒントを使えば、NESTED LOOPS JOIN ANTIで処理するように制御することができる。 |
| HASH_AJ | NOT EXISTSやNOT IN条件を使った場合、サブクエリにUNNESTとともにHASH_AJヒントを使えばHASH JOIN ANTIで処理するように制御することができる。 |
| ORDERED | FROM節に記述された順に実行するように結合順序を定めるヒントである。 しかしサブクエリが存在する場合は、サブクエリが一番最初に実行される。 |
| QB_NAME | QUERY BLOCKの名前を指定する。 |
| SWAP_JOIN_INPUTS | HASH JOIN時、SWAP_JOIN_INPUTSヒントを使用することで、結合順序を変えることができる。 |
| NO_SWAP_JOIN_INPUTS | HASH JOIN時、結合順序が変わる場合、強制的に変えられないように制御することができる。 |
| PUSH_SUBQ | サブクエリを先に実行するように制御するヒントである。 |
上記ヒントを基に実際の例題を通じてサブクエリの動作方式を希望通り制御する実習を進めてみる。
- [例題SQL]
[例題SQL]
ELECT c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >= :b1
AND c6 <= :b2
AND EXISTS ( SELECT 'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
1)例題SQLのサブクエリをFilter動作方式で実行するように制御してみる。
Note. NO_UNNESTヒントを付与すれば、Filter動作方式で実行するように制御することができる
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 t1
WHERE c6 >= :b1
AND c6 <= :b2
AND EXISTS ( SELECT /*+ NO_UNNEST */
'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
----------------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 |
|* 1 | FILTER | | | |
|* 2 | FILTER | | | |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K |
|* 4 | FILTER | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2 | 1 | 10 |
|* 6 | INDEX UNIQUE SCAN | PK_SUQUERY_2 | 1 | |
----------------------------------------------------------------------
2)例題SQLのサブクエリを結合動作方式のうちNested Loops Semi Joinで実行するように制御してみる。
Note. UNNESTとともにNL_SJヒントを付与すれば、NESTED LOOPS SEMI JOINオペレーションが実行されて、意図したようにNested Loops Semi Joinで実行されるように制御することができる。
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 t1
WHERE c6 >= :b1
AND c6 <= :b2
AND EXISTS ( SELECT /*+ UNNEST NL_SJ */
'x'
FROM SUBQUERY_T2 T2
WHERE bt2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
----------------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 28392 |
|* 1 | FILTER | | | |
| 2 | NESTED LOOPS SEMI | | 1352 | 28392 |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K|
|* 4 | TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2 | 42 | 420 |
|* 5 | INDEX UNIQUE SCAN | PK_SUQUERY_2 | 1 | |
----------------------------------------------------------------------
3)例題SQLのサブクエリをHash Join Semi Joinで実行するものの、サブクエリをMain SQLテーブルより先に実行するように制御してみる。
Note. UNNESTとHASH_SJ,SWAP_JOIN_INPUTSヒントを使えば、意図した通りHash Right Semi Joinで実行して、サブクエリブト実行するように実行計画を制御することができる。
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 t1
WHERE c6 >= :b1
AND c6 <= :b2
AND EXISTS ( SELECT /*+ UNNEST HASH_SJ SWAP_JOIN_INPUTS(T2) */
'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 28392 |
|* 1 | FILTER | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1352 | 28392 |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T2 | 1250 | 12500 |
|* 4 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K|
-------------------------------------------------------------
4)例題SQLサブクエリをHash Semi Joinで実行するものの、Main SQLテーブルを先に実行するように制御してみる。
Note. UNNESTとHASH_SJヒントを使えば、Hash Semi Joinで実行するように制御することができる。基本的にSemi JoinはMain SQL側テーブルを先に実行しなければならない。しかしHash Right Semi Joinで実行されれば結合順序が変更されるのでNO_SWAP_JOIN_INPUTSヒントを明示的に使用することで、必ずMain SQL側テーブルを先に実行するように制御することができる。
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 t1
WHERE c6 >= :b1
AND c6 <= :b2
AND EXISTS ( SELECT /*+ UNNEST HASH_SJ NO_SWAP_JOIN_INPUTS(T2) */
'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-----------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 28392 |
|* 1 | FILTER | | | |
|* 2 | HASH JOIN SEMI | | 1352 | 28392 |
|* 3 | TABLE ACCESS FULL| SUBQUERY_T1 | 40000 | 429K|
|* 4 | TABLE ACCESS FULL| SUBQUERY_T2 | 1250 | 12500 |
-----------------------------------------------------------
5)例題SQLをNested Loops Joinで実行するものの、サブクエリブト実行するように制御してみる。
Note.実行計画を制御することの難しいパターンである。 QB_NAMEヒントを使ってQUERY BLOCK名を指定した後、結合順序と結合方法を制御するのに使うことができる。
EXPLAIN PLAN FOR
SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NL(T1@MAIN) */
c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >= :b1
AND c6 <= :b2
AND EXISTS ( SELECT /*+ UNNEST QB_NAME(SUB) */ 'x'
FROM SUBQUERY_T2 t2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
---------------------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1351 | 28371 |
|* 1 | FILTER | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| SUBQUERY_T1 | 1 | 11 |
| 3 | NESTED LOOPS | | 1351 | 28371 |
| 4 | SORT UNIQUE | | 1250 | 12500 |
|* 5 | TABLE ACCESS FULL | SUBQUERY_T2 | 1250 | 12500 |
|* 6 | INDEX RANGE SCAN | SUBQUERY_T1_IDX_01 | 64 | |
---------------------------------------------------------------------------
6)例題SQLをHash Joinで処理するものの、サブクエリブト実行するように制御してみる。
EXPLAIN PLAN FOR
SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_HASH(T1@MAIN) */
c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >= :b1
AND c6 <= :b2
AND EXISTS ( SELECT /*+ UNNEST QB_NAME(SUB) */
'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1351 | 28371 |
|* 1 | FILTER | | | |
|* 2 | HASH JOIN | | 1351 | 28371 |
| 3 | SORT UNIQUE | | 1250 | 12500 |
|* 4 | TABLE ACCESS FULL| SUBQUERY_T2 | 1250 | 12500 |
|* 5 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K|
------------------------------------------------------------
7) NOT EXISTSで作成されたSQLをFilter動作方式でないNested Loops Anti Joinで実行するように制御してみる。
Note. NOT EXISTSの場合Nested Loops Anti Joinで実行するように制御するためにUNNEST,NL_AJヒントを付与すれば良い。
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >= :b1
AND c6 <= :b2
AND NOT EXISTS ( SELECT /*+ UNNEST NL_AJ */
'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
----------------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38648 | 792K|
|* 1 | FILTER | | | |
| 2 | NESTED LOOPS ANTI | | 38648 | 792K|
|* 3 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K|
|* 4 | TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2 | 42 | 420 |
|* 5 | INDEX UNIQUE SCAN | PK_SUQUERY_2 | 1 | |
----------------------------------------------------------------------
8) NOT EXISTSで作成されたSQLをHash Join Anti結合で実行するように制御してみる。
Note. NOT EXISTSの場合UNNEST,HASH_AJヒントを付与すれば実行計画を制御することができる。
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >= :b1
AND c6 <= :b2
AND NOT EXISTS ( SELECT /*+ UNNEST HASH_AJ */
x'
FROM SUBQUERY_T2 t2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4 ) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38648 | 792K|
|* 1 | FILTER | | | |
|* 2 | HASH JOIN RIGHT ANTI| | 38648 | 792K|
|* 3 | TABLE ACCESS FULL | SUBQUERY_T2 | 1250 | 12500 |
|* 4 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K|
-------------------------------------------------------------
9)の下のSQLでサブクエリを先に読んだ後、Nested Loops Joinで実行するようにすることができるか?
SELECT *
FROM emp a
WHERE empno IN ( SELECT max(empno)
FROM emp x
GROUP BY deptno );
結合順序(LEADING)、結合方法(USE_NL)、QUERY BLOCK名指定(QB_NAME)ヒントを使ってSQL実行計画を制御してみる。
EXPLAIN PLAN FOR
SELECT /*+ LEADING(X@SUB) QB_NAME(MAIN) USE_NL(A@MAIN) */
*
FROM emp a
WHERE empno IN ( SELECT /*+ UNNEST QB_NAME(SUB) */
max(empno)
FROM emp x
GROUP BY deptno );
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-----------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 153 |
| 1 | MERGE JOIN | | 3 | 153 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 494 |
| 3 | INDEX FULL SCAN | PK_EMP | 13 | |
|* 4 | SORT JOIN | | 3 | 39 |
| 5 | VIEW | VW_NSO_1 | 3 | 39 |
| 6 | HASH GROUP BY | | 3 | 21 |
| 7 | TABLE ACCESS FULL | EMP | 13 | 91 |
-----------------------------------------------------------------
意図したようには、実行計画が制御できなかった。 Optimizerがサブクエリをインライン ビューに変更するSQL最適化作業を実行したためである。 ビュー名がVM_NSO_1という点でこれを類推することができる。 すなわち、SQLが変更されて、これによってQUERY BLOCK名も変更されて、SQLに使ったQB_NAMEヒントはもちろん、他のヒントが無視された。 そのためLEADINGヒントに誘導した結合順序で実行されなかった。 このような場合From節に羅列された順に結合順序を決めるORDEREDヒントを使えば望むように誘導することができる。 これはLogical Optimizerがサブクエリをインライン ビューに変更する時、From節の最も前に位置させるために可能なことである。 それではORDEREDヒントを利用して実行計画を望むまま制御できるのか確認してみる。
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_NL(A) */
*
FROM emp a
WHERE empno IN ( SELECT /*+ UNNEST */
max(empno)
FROM emp x
GROUP BY deptno );
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-----------------------------------------------------------------
| Id | OPERATION | Name | Rows | Bytes |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 153 |
| 1 | NESTED LOOPS | | 3 | 153 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 |
| 3 | HASH GROUP BY | | 3 | 21 |
| 4 | TABLE ACCESS FULL | EMP | 13 | 91 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 |
|* 6 | INDEX UNIQUE SCAN | PK_EMP | 1 | |
-----------------------------------------------------------------
希望通り実行計画が誘導できた。 しかし、上記SQLはサブクエリが一つ、Main SQLのFrom句にテーブルが一つしかない非常に単純なSQLなので可能だったことである。 もし、サブクエリがいくつかあって、Main SQLのFrom句にもいくつかのテーブルが結合された状態ならば、前でORDEREDヒントで制御して改善した方法の使用は事実上不可能である。 なぜなら、ヒントを使っていくつかのサブクエリ中特定サブクエリを先に読んで、以後残りのサブクエリとFrom句のテーブルの結合順序などを制御するのは事実上不可能なためである。
もし、このように複雑なSQLを制御したい場合はSQLを再作成することである。 SQLのサブクエリをインライン ビューに変更した後、ヒントで実行計画を制御しなければならない。 この方法に対しては次に説明する“サブクエリを活用したSQL性能改善”で詳しく調べてみる。
今までサブクエリを望む実行計画で制御できるヒントとその使用方法に対して調べてみた。
サブクエリはDBサーバーの性能に及ぼす影響力が非常に大きいので格別に気を遣わなければならない対象である。 SQLの業務的性格(抽出件数、照会頻度など)を考慮するのはもちろんで、それに最も適合したサブクエリ動作方式まで考慮したSQLを作成しなければならない。
今回は、ここまでになります。次回は、本文でも説明されている通り「サブクエリを活用したSQL性能改善」に関したになります。ご期待ください。
それでは、See You ^^
