SQLパッチを使いましょう (結論)
SQLパッチとは？
SQL の実行計画をコントロールする方法のひとつです。実行計画をコントロールするシンプルで代表的な方法としてヒント句の使用がありますが、SQL の修正が必要で、パッケージ製品などの SQL には使用できません。
そんな時に使用できるのが SQLパッチです。SQLパッチを使用すれば、SQL を修正することなく実行計画をコントロールすることが可能です。
やってTRY
実際にやってみます。なお、SQLパッチは 12.2 から使用可能です。
サンプルとして使用する SQL は下記で、オプティマイザに任せると結合方法は HASH JOIN です。
SQL> r
1* select t1.cust_first_name,t2.country_name from sh.customers t1 join sh.countries t2 on t id = t2.country_id fetch first 10 rows only
CUST_FIRST_NAME COUNTRY_NAME
-------------------- ----------------------------------------
Abigail Italy
Abigail Italy
Abigail Italy
Abigail Italy
Abigail Brazil
Abigail Japan
Abigail Brazil
Abigail Japan
Abigail Italy
Abigail United Kingdom
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3158641956
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | 5 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 470 | 5 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 390 | 5 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 10 | 390 | 5 (0)| 00:00:01 |★
| 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 10 | 120 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
3 - access("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
802 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQLパッチを試す前に、ヒント句で実行計画をコントロールできることを確認します。
問題なく NESTED LOOP, MERGE JOIN にできました。
NESTED LOOP に変更 (ヒント句)
SQL> select /*+ USE_NL(t1) LEADING(t2) */ t1.cust_first_name,t2.country_name from sh.customer sh.countries t2 on t1.country_id = t2.country_id fetch first 10 rows only;
CUST_FIRST_NAME COUNTRY_NAME
-------------------- ----------------------------------------
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 639545901
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | 6 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 470 | 6 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 12 | 324 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 12 | 324 | 6 (0)| 00:00:01 |★
| 4 | TABLE ACCESS FULL | COUNTRIES | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | CUSTOMERS | 12 | 144 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
5 - filter("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
789 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
MERGE JOIN に変更 (ヒント句)
SQL> select /*+ USE_MERGE(t1) LEADING(t2) */ t1.cust_first_name,t2.country_name from sh.custo in sh.countries t2 on t1.country_id = t2.country_id fetch first 10 rows only;
CUST_FIRST_NAME COUNTRY_NAME
-------------------- ----------------------------------------
Zylia Singapore
Kermit Singapore
Algernon Singapore
Moriah Singapore
Astrid Singapore
Beatrice Singapore
Cameron Singapore
Poppy Singapore
Radbourne Singapore
Jayden Singapore
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 88915563
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | | 682 (1)|
|* 1 | VIEW | | 10 | 470 | | 682 (1)|
|* 2 | WINDOW NOSORT STOPKEY | | 12 | 504 | | 682 (1)|
| 3 | MERGE JOIN | | 12 | 504 | | 682 (1)|★
| 4 | TABLE ACCESS BY INDEX ROWID| COUNTRIES | 1 | 15 | | 2 (0)|
| 5 | INDEX FULL SCAN | COUNTRIES_PK | 1 | | | 1 (0)|
|* 6 | SORT JOIN | | 55500 | 650K| 2200K| 680 (1)|
| 7 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 650K| | 423 (1)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
6 - access("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
filter("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1523 consistent gets
1148 physical reads
0 redo size
823 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
それでは SQLパッチを試します。
SQLパッチの作成にはDBMS_SQLDIAG.CREATE_SQL_PATCHを使用します。
SQLパッチで指定するヒント句 (hint_text) には問い合わせブロック名 (ここでは@SEL$1) が必要となる点に注意します。
SQLパッチの作成
SQL> select sql_id,sql_text from v$sqltext
2 where sql_text like '%select t1.cust_first_name,t2.country_name%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------
d7tgw5dj0yf8p select t1.cust_first_name,t2.country_name from sh.customers t1 j
SQL>
SQL> DECLARE
2 l_patch_name VARCHAR2(32767);
3 BEGIN
4 l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
5 sql_id=>'d7tgw5dj0yf8p',
6 name=>'sqlpatch_test1',
7 hint_text=>'USE_NL(@SEL$1 t1) LEADING(@SEL$1 t2)');
8 dbms_output.put_line(l_patch_name);
9 END;
10 /
PL/SQL procedure successfully completed.
サンプルSQL を実行すると、実行計画が NESTED LOOP になっていることがわかります。
PHV もヒント句で実行計画を NESTED LOOP にした時と同じ値 (639545901) になっていることがわかります。
また、作成した SQLパッチを使用したことが Note に出力されています。
NESTED LOOP に変更 (SQLパッチ)
SQL> select t1.cust_first_name,t2.country_name from sh.customers t1 join sh.countries t2 on t1.country_id = t2.country_id fetch first 10 rows only;
CUST_FIRST_NAME COUNTRY_NAME
-------------------- ----------------------------------------
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 639545901 ★
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | 6 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 470 | 6 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 12 | 324 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 12 | 324 | 6 (0)| 00:00:01 |★
| 4 | TABLE ACCESS FULL | COUNTRIES | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | CUSTOMERS | 12 | 144 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
5 - filter("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
Note
-----
- SQL patch "sqlpatch_test1" used for this statement ★
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
11 consistent gets
1 physical reads
0 redo size
789 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
MERGE JOIN も試してみます。
作成した SQLパッチの削除にはDBMS_SQLDIAG.DROP_SQL_PATCHを使用します。
MERGE JOIN に変更 (SQLパッチ)
SQL> exec DBMS_SQLDIAG.DROP_SQL_PATCH(name=>'sqlpatch_test1');
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_patch_name VARCHAR2(32767);
3 BEGIN
4 l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
5 sql_id=>'d7tgw5dj0yf8p',
6 name=>'sqlpatch_test1',
7 hint_text=>'USE_MERGE(@SEL$1 t1) LEADING(@SEL$1 t2)');
8 dbms_output.put_line(l_patch_name);
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> select t1.cust_first_name,t2.country_name from sh.customers t1 join sh.countries t2 on t1.country_id = t2.country_id fetch first 10 rows only;
CUST_FIRST_NAME COUNTRY_NAME
-------------------- ----------------------------------------
Zylia Singapore
Kermit Singapore
Algernon Singapore
Moriah Singapore
Astrid Singapore
Beatrice Singapore
Cameron Singapore
Poppy Singapore
Radbourne Singapore
Jayden Singapore
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 88915563 ★
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | | 682 (1)| 00:00:01 |
|* 1 | VIEW | | 10 | 470 | | 682 (1)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 12 | 504 | | 682 (1)| 00:00:01 |
| 3 | MERGE JOIN | | 12 | 504 | | 682 (1)| 00:00:01 |★
| 4 | TABLE ACCESS BY INDEX ROWID| COUNTRIES | 1 | 15 | | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | COUNTRIES_PK | 1 | | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 55500 | 650K| 2200K| 680 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 650K| | 423 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
6 - access("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
filter("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
Note
-----
- SQL patch "sqlpatch_test1" used for this statement ★
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
1528 consistent gets
1 physical reads
132 redo size
823 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>
NESTED LOOP と MERGE JOIN の SQLパッチを両方作るとどうなる？
NESTED LOOP と MERGE JOIN の SQLパッチを両方作るとどうなるか気になったので試してみます。
(先に作った SQLパッチが優先される OR 後に作った SQLパッチが優先される OR 両方無視される)
いずれの予想も外れ、エラーとなりました 
SQL> DECLARE
2 l_patch_name VARCHAR2(32767);
3 BEGIN
4 l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
5 sql_id=>'d7tgw5dj0yf8p',
6 name=>'sqlpatch_test2',
7 hint_text=>'USE_NL(@SEL$1 t1) LEADING(@SEL$1 t2)');
8 dbms_output.put_line(l_patch_name);
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-13830: SQL profile or patch with category DEFAULT already exists for this
SQL statement
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 18232
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 512
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 568
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1801
ORA-06512: at line 4
SQL>
SQLパッチが存在する状態でヒント句を付けるとどうなる？
こちらも気になったので試してみます。
MERGE JOIN の SQLパッチを作成済みなので、SQL に NESTED LOOP のヒント句を付けてみます。
結果としては、SQL に付与した NESTED LOOP のヒント句が有効になりました。
SQL> select t1.cust_first_name,t2.country_name from sh.customers t1 join sh.countries t2 on t1.country_id = t2.country_id fetch first 10 rows only;
CUST_FIRST_NAME COUNTRY_NAME
-------------------- ----------------------------------------
Zylia Singapore
Kermit Singapore
Algernon Singapore
Moriah Singapore
Astrid Singapore
Beatrice Singapore
Cameron Singapore
Poppy Singapore
Radbourne Singapore
Jayden Singapore
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 88915563
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | | 682 (1)| 00:00:01 |
|* 1 | VIEW | | 10 | 470 | | 682 (1)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 12 | 504 | | 682 (1)| 00:00:01 |
| 3 | MERGE JOIN | | 12 | 504 | | 682 (1)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| COUNTRIES | 1 | 15 | | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | COUNTRIES_PK | 1 | | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 55500 | 650K| 2200K| 680 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 650K| | 423 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
6 - access("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
filter("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
Note
-----
- SQL patch "sqlpatch_test1" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1523 consistent gets
0 physical reads
0 redo size
823 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select /*+ USE_NL(t1) LEADING(t2) */ t1.cust_first_name,t2.country_name from sh.customers t1 join sh.countries t2 on t1.country_id = t2.country_id fetch first 10 rows only;
CUST_FIRST_NAME COUNTRY_NAME
-------------------- ----------------------------------------
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
Abner United States of America
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 639545901
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | 6 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 470 | 6 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 12 | 324 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 12 | 324 | 6 (0)| 00:00:01 |★
| 4 | TABLE ACCESS FULL | COUNTRIES | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | CUSTOMERS | 12 | 144 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
5 - filter("T1"."COUNTRY_ID"="T2"."COUNTRY_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
789 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>
以上、SQLパッチのご紹介でした。
パッケージ製品でオラクルDBを使用する際には、SQL の性能トラブルに備えて押さえておくと良いかと思います。
(更改のときなどに、移行から漏れないよう注意しましょう)
マニュアル↓