Oracle SQL Hinting Tips / #JoelKallmanDay Tweet
Joel Kallman Day 2023 : Announcement
の通り、本ポストは、Joel Kallman Day 2023 向けのブログポストでもあります。詳細はTimeのブログ参照のこと。
Previously on Mac De Oracle
前回は、rebuild index後のindexサイズって結局、create indexのexplainで見積もれるんだよね、というお話でした。
今回から数回、OracleのSQL ヒントの使い方テクニックでも紹介して行こうかなと思っています。
まず、最近のヒント周りの劇的な進化というか便利になったこと、最近といっても、数ヶ月とかいう単位ではないのですけどもw
Oracle 19c から hint usage report が表示されるようになりました。
19.3.3.4 ヒント使用状況レポート: 例
https://docs.oracle.com/cd/F19136_01/tgsql/influencing-the-optimizer.html#GUID-1697E7CA-9DD0-4C0D-9BC9-E4E17334C0AA
これ、今までなんでなかったの？ という感じはあります。（全くなかったわけではないのですが、よほどのことがなけれそこまでトレースしなかったというのが一番の理由でしょうね。取得および、確認方法が面倒だったのでw）
最近は、ヒントの種類も、使用頻度も多くなってきたことが影響しているのでしょうか。。。ヒント使用状況レポートがほしいという要望が多くなったのでしょうかね？
ただ、hint usage report で簡単に確認できるようになったのは良いことだと思うのですが、その副作用というような現象に気づくことも、それなりに多くなってきました。。。
その副作用とは、ヒントが、Unusedとなるような記述は避けるべき！ のような話です
（風の便り程度で実際そうなっている状況に遭遇はしていないのですが）
それ以来、
ヒントの書き方の標準を決める際に、過度に、strictになって、逆にヒントを使いこなせない状況になったりしていないだろうか？
もしくは、その兆候があるのではないかと心配になることがあります。
昔から使われてきた柔軟なヒント記述方法が、変に制限されたりすることのないようにしてほしいものだとは思います。
（そう意味も込めて、マニュアルにも書いてるからね、この方法というのも点も強調しつつ、簡単な検証方法も合わせて紹介しています）
USE_HASHヒント
https://docs.oracle.com/cd/F19136_01/sqlrf/Comments.html#GUID-FA1147B3-BCAA-41F9-B6A2-8DEDABF1C021
USE_NLヒント
https://docs.oracle.com/cd/F19136_01/sqlrf/Comments.html#GUID-56DAA0EC-54BB-4E9D-9049-BCEA934F7A89
マニュアルにも例が記載されていますが、USE_NLやUSE_HASHヒントには、内部表（HASH結合の場合は、プローブ表）を指定します、単一表指定、複数表指定どちらも可能です。
例えば、A表とB表を2表を結合する例で言えば、
Nested Loop Join(NLJ)の駆動表、Hash Join(HJ)のビルド表が、A表だとして、
/*+ LEADING(A) USE_NL(B) */
/*+ LEADING(A) USE_HASH(B) */
のようにヒントを書くことができます。LEADINGヒントで駆動表（NLJの場合）または、ビルド表（HJの場合）を指定し、内部表または、ビルド表をUSE_NL（NLJの場合）または、USE_HASH（HJの場合）で指定する。
では、マニュアルにも記載されている以下のような記述は、どのような意味なのか、みなさん、お分かりでしょうか？
/*+ USE_NL(A B) */ 同じ意味ですが、 /*+ USE_NL(A) USE_NL(B) */
/*+ USE_HASH(A B) */ 同じ意味ですが、 /*+ USE_HASH(A) USE_HASH(B) */
A表とB表の2表ですから、どちらかが、駆動表（NLJの場合）、または、ビルド表（HJの場合）で、どちらかが、内部表（NLJの場合）、または、プローブ表（HJの場合）となります。
これらのヒントに指定した表は、指定しても無視される表（駆動表または、ビルド表）、つまり、Unused となる表も含めて記載しています。（エラーではないく、使われないだけだからですが）
あえて、そうする意味はなんでしょう。みなさんお分かりでしょうか？
答えは、結合順序はオプティマイザの判断に任せ、結合方法だけを、NLJ や HJ にしたい！！！！！
ということです。
意外にこのような状況は多くあります。
要するに、駆動表や、ビルド表は状況に応じて柔軟に変えてもらって良いが、結合方法だけは、絶対、NLJにしたい。もしくは、HJにしたいというケースですね！
（チューニングで呼ばれて行った先で、業務観点からどの表が駆動表や、ビルド表ですか？ と聞いて、即答してくれないと、おじさん困ってしまうんですねw わからんと言われてたら、リスク覚悟で現状のデータ量から決めるか、このように決めないで、オプティマイザに任せる。みなさんもオプティマイザを信じてください！ と、言ったもののw、統計情報取得止められてたりすると、まあ、信じられないみたいな状況もあるわけです。はい。wwwww)
他のTech Tipsとして、結合順は状況に応じて人の手で書き換えること（前述したように担当者も結合順を把握していないとか、一旦、固定したけども、やはり間違ってたケースや、経年で傾向が変わったので変更したい etc.）を想定して、ヒント修正によるミスのリスクを最小化するため。（修正範囲をLEADINGヒントだけにして、変更箇所を最小にしたい場合です）
駆動表または、ビルド表の変更前
/*+ LEADING(A B) USE_NL(A B) */
/*+ LEADING(A B) USE_HASH(A B) */
/*+ LEADING(A) USE_NL(A B) */
/*+ LEADING(A) USE_HASH(A B) */
駆動表または、ビルド表の変更後
/*+ LEADING(B A) USE_NL(A B) */
/*+ LEADING(B A) USE_HASH(A B) */
/*+ LEADING(B) USE_NL(A B) */
/*+ LEADING(B) USE_HASH(A B) */
これらのヒントは、LEADINGヒントの結合順を変更するだけで、駆動表やビルド表を切り替えることができます。USE_NL、USE_HASHヒントは変更する必要がありません。（ヒント指定時のミスの発生箇所を最小化できるわけです＝変更箇所を少なくした）
しかし、以下のように記述していた場合はどうでしょう？
駆動表や、ビルド表を変更する場合、LEADING/USE_NL/USE_HASH全てのヒントを適切に修正する必要があります。
変更前
/*+ LEADING(A) USE_NL(B) */
/*+ LEADING(A) USE_HASH(B) */
変更後
/*+ LEADING(B) USE_NL(A) */
/*+ LEADING(B) USE_HASH(A) */
変更し忘れたことによりヒントが無効となり、遅延に繋がってしまったということは意外に多いです。修正しているのは、人ですからね。間違いはあります。修正する箇所と量を最小化すれば、ミスの発生リスクは減らせます。
もう一つの効果として、可読性が向上する（個人的な感覚かもしれませんが）のではないかと考えています。
ということで長い前置きはこれぐらいにして挙動を確認してみましょう。
結合順はオプティマイザ任せにして固定せず、結合方法だけをHJにした場合、Hint Usage Reportにはどうのようにレポートされるのか、また実行計画は想定通りなのか等を確認してみましょう。
Oracle Database 21cを利用して検証します。ちなみに、この方法は、Oracle 11gの頃から実戦で利用されている方法です。（マニュアルにも例が記載されているので、将来的にも挙動が変わることはないでしょう。影響でかいですからね。挙動が変わるとw）
なお、隠しパラメータのカスタマイズはせず、インストール時のままです。
SCOTT@orclpdb1> select banner from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
検証用の表とデータ登録、統計情報取得、データ件数確認
（なお、本検証で利用したスクリプトはこのブログの後半に記載してあります）
SCOTT@orclpdb1> @hinting_tech1
表が削除されました。
経過: 00:00:00.84
表が作成されました。
経過: 00:00:00.19
表が削除されました。
経過: 00:00:00.51
表が作成されました。
経過: 00:00:00.07
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:06.41
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:13.93
1 BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_A', cascade=>true, no_invalidate=>false);
3* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:01.71
1 BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_B', cascade=>true, no_invalidate=>false);
3* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:01.16
1* SELECT COUNT(1) FROM table_a
COUNT(1)
----------
50000
経過: 00:00:00.01
1* SELECT COUNT(1) FROM table_b
COUNT(1)
----------
100000
経過: 00:00:00.01
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.03
何もチューニングしていないオリジナルのSQLと実行計画を確認しておきます。少量のデータに絞って、結合（索引あり）しています。
駆動表がTABLE_Aで、NLJしていることが確認できます。
1 SELECT
2 *
3 FROM
4 table_a a
5 INNER JOIN table_b b
6 ON
7 a.id = b.id
8* AND a.id BETWEEN :s AND :e
経過: 00:00:00.04
実行計画
----------------------------------------------------------
Plan hash value: 1883513077
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 | 489K| 203 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 125 | 489K| 203 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 125 | 489K| 203 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_A | 125 | 244K| 78 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C009317 | 225 | | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C009318 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | TABLE_B | 1 | 2006 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:E)>=TO_NUMBER(:S))
5 - access("A"."ID">=TO_NUMBER(:S) AND "A"."ID"<=TO_NUMBER(:E))
6 - access("A"."ID"="B"."ID")
filter("B"."ID">=TO_NUMBER(:S) AND "B"."ID"<=TO_NUMBER(:E))
Note
-----
- this is an adaptive plan
統計
----------------------------------------------------------
73 recursive calls
21 db block gets
143 consistent gets
8 physical reads
4108 redo size
8944 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
5 rows processed
NLJをヒント使って、HJに強制します。索引アクセスを行わないようにFULLヒントも併用していますが、LEADINGヒントは利用せず、ビルド表の決定はオプティマイザに任せています。
実行結果とヒント使用状況レポートを確認すると、オプティマイザの判断により、TABLE_Aがビルド表となっため、USE_HASH(A)がUnusedとしてレポートされてますが、残るヒントは利用され、NLJからHJへ変更が行われています。
Unusedは最終的に利用されなかったという意味で、ヒント構文場問題があるわけではなくヒントとしては正しいが、最終的に利用されなかった。このケースではビルド表となった表TABLE_Aを指定していた、USE_HASH(A)が利用されなかったということですね。
理由は、USE_HASH/USE_NLに指定する表は、プローブ表または、内部表となっているためで、このケースではオプティマイザが最終的にTABLE_Aをビルド表としたためUSE_HASH(A)が利用されなかったということを意味しています。
1 SELECT
2 /*+
3 FULL(a)
4 FULL(b)
5 USE_HASH(a)
6 USE_HASH(b)
7 */
8 *
9 FROM
10 table_a a
11 INNER JOIN table_b b
12 ON
13 a.id = b.id
14* AND a.id BETWEEN :s AND :e
経過: 00:00:00.24
実行計画
----------------------------------------------------------
Plan hash value: 3176705182
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TABLE_A | 125 | 244K| 4678 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TABLE_B | 250 | 489K| 9105 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:E)>=TO_NUMBER(:S))
2 - access("A"."ID"="B"."ID")
3 - filter("A"."ID">=TO_NUMBER(:S) AND "A"."ID"<=TO_NUMBER(:E))
4 - filter("B"."ID">=TO_NUMBER(:S) AND "B"."ID"<=TO_NUMBER(:E))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$58A6D7F6 / "A"@"SEL$1"
U - USE_HASH(a)
統計
----------------------------------------------------------
30 recursive calls
0 db block gets
50366 consistent gets
50282 physical reads
0 redo size
8944 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5 rows processed
USE_HASH(a b)という記載ですが、前述のヒントと同じ意味です。この例のような指定方法だと、ヒント使用状況レポートの読み方の理解が重要になります。
U - USE_HASH(a b) というレポートがありますが、これだけだと、ヒント全体が利用されなかったのか？ と思われるかもしれませんが、
もう一つ上の、3 - SEL$58A6D7F6 / "A"@"SEL$1"に着目する必要があります。 該当ヒントの A が利用されなかったということを意味しています。
1 SELECT
2 /*+
3 FULL(a)
4 FULL(b)
5 USE_HASH(a b)
6 */
7 *
8 FROM
9 table_a a
10 INNER JOIN table_b b
11 ON
12 a.id = b.id
13* AND a.id BETWEEN :s AND :e
経過: 00:00:00.23
実行計画
----------------------------------------------------------
Plan hash value: 3176705182
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TABLE_A | 125 | 244K| 4678 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TABLE_B | 250 | 489K| 9105 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:E)>=TO_NUMBER(:S))
2 - access("A"."ID"="B"."ID")
3 - filter("A"."ID">=TO_NUMBER(:S) AND "A"."ID"<=TO_NUMBER(:E))
4 - filter("B"."ID">=TO_NUMBER(:S) AND "B"."ID"<=TO_NUMBER(:E))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$58A6D7F6 / "A"@"SEL$1"
U - USE_HASH(a b)
統計
----------------------------------------------------------
24 recursive calls
0 db block gets
50298 consistent gets
50282 physical reads
0 redo size
8944 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
この検証では、LEADINGヒントを利用せず、オプティマイザ任せにしてあります。もう一つの検証として、オプティマイザが駆動表というかビルド表にする表を変えたケースも確認しておきたいですよね！ ヒント指定の思惑通りの挙動になるか。。。確認は必要ですよからねw （実案件ではここまで確認はしませんけどもw 本記事の目的がその確認ですのでw）
データをからにして、行数を逆にして、駆動表というかビルド表が変わるように仕掛けておきます。
1* truncate table table_a
表が切り捨てられました。
経過: 00:00:00.44
1* truncate table table_b
表が切り捨てられました。
経過: 00:00:00.13
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:07.02
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:13.49
1 BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_A', cascade=>true, no_invalidate=>false);
3* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:03.19
1 BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_B', cascade=>true, no_invalidate=>false);
3* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:01.54
1* SELECT COUNT(1) FROM table_a
COUNT(1)
----------
100000
経過: 00:00:00.02
1* SELECT COUNT(1) FROM table_b
COUNT(1)
----------
50000
経過: 00:00:00.01
何もしていないオプティマイザ任せの実行計画は、NLJで、狙い通りにTABLE_Bが駆動表に切り替わっています。（うまく行ってよかったw）
1 SELECT
2 *
3 FROM
4 table_a a
5 INNER JOIN table_b b
6 ON
7 a.id = b.id
8* AND b.id BETWEEN :s AND :e
経過: 00:00:00.05
実行計画
----------------------------------------------------------
Plan hash value: 3627193911
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 | 489K| 203 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 125 | 489K| 203 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 125 | 489K| 203 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_B | 125 | 244K| 78 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C009318 | 225 | | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C009317 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | TABLE_A | 1 | 2006 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:E)>=TO_NUMBER(:S))
5 - access("B"."ID">=TO_NUMBER(:S) AND "B"."ID"<=TO_NUMBER(:E))
6 - access("A"."ID"="B"."ID")
filter("A"."ID">=TO_NUMBER(:S) AND "A"."ID"<=TO_NUMBER(:E))
Note
-----
- this is an adaptive plan
統計
----------------------------------------------------------
86 recursive calls
0 db block gets
113 consistent gets
7 physical reads
0 redo size
8944 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
5 rows processed
先ほどと同じヒントを指定しました。LEADINGヒントはないので、オプティマイザは、TABLE_Bをビルド表として選択しました。狙い通りですね。
ということは、今度は、USE_HASH(B)がUnusedになるはずです。ビルド表になりましたからね。先ほどは、プローブ表だったわけですが。
ヒント使用状況レポートの詳細にはいかのようにレポートされました。USE_HASH(A B)という表よりは直感的に理解しやすいと思いますが、B Unusedですよということですね。
3 - SEL$58A6D7F6 / "B"@"SEL$1"
U - USE_HASH(b)
1 SELECT
2 /*+
3 FULL(a)
4 FULL(b)
5 USE_HASH(a)
6 USE_HASH(b)
7 */
8 *
9 FROM
10 table_a a
11 INNER JOIN table_b b
12 ON
13 a.id = b.id
14* AND b.id BETWEEN :s AND :e
経過: 00:00:00.22
実行計画
----------------------------------------------------------
Plan hash value: 3989351480
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TABLE_B | 125 | 244K| 4678 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TABLE_A | 250 | 489K| 9105 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:E)>=TO_NUMBER(:S))
2 - access("A"."ID"="B"."ID")
3 - filter("B"."ID">=TO_NUMBER(:S) AND "B"."ID"<=TO_NUMBER(:E))
4 - filter("A"."ID">=TO_NUMBER(:S) AND "A"."ID"<=TO_NUMBER(:E))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$58A6D7F6 / "B"@"SEL$1"
U - USE_HASH(b)
統計
----------------------------------------------------------
113 recursive calls
0 db block gets
50787 consistent gets
50526 physical reads
0 redo size
8944 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
25 sorts (memory)
0 sorts (disk)
5 rows processed
USE_HASH(a b)という表記にした例です意味は同じですが、USE_HASH(a b)全体がUnusedなのかと勘違いしそうですよね。よーく見てみましょう。SEL$58A6D7F6 / "B"@"SEL$1" とあり、 USE_HASH(a b)のうち、B が Unusedであることが確認できます。
3 - SEL$58A6D7F6 / "B"@"SEL$1"
U - USE_HASH(a b)
1 SELECT
2 /*+
3 FULL(a)
4 FULL(b)
5 USE_HASH(a b)
6 */
7 *
8 FROM
9 table_a a
10 INNER JOIN table_b b
11 ON
12 a.id = b.id
13* AND b.id BETWEEN :s AND :e
経過: 00:00:00.23
実行計画
----------------------------------------------------------
Plan hash value: 3989351480
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 125 | 489K| 13783 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TABLE_B | 125 | 244K| 4678 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TABLE_A | 250 | 489K| 9105 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:E)>=TO_NUMBER(:S))
2 - access("A"."ID"="B"."ID")
3 - filter("B"."ID">=TO_NUMBER(:S) AND "B"."ID"<=TO_NUMBER(:E))
4 - filter("A"."ID">=TO_NUMBER(:S) AND "A"."ID"<=TO_NUMBER(:E))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$58A6D7F6 / "B"@"SEL$1"
U - USE_HASH(a b)
統計
----------------------------------------------------------
5 recursive calls
0 db block gets
50541 consistent gets
50526 physical reads
0 redo size
8944 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
本検証で利用したスクリプト（ hinting_tech1.sql ）
drop table table_a purge
/
create table table_a
(
id number primary key
, dummy_str varchar2(2000)
)
/
drop table table_b purge
/
create table table_b
(
id number primary key
, dummy_str varchar2(2000)
)
/
BEGIN
FOR i IN 1..50000 LOOP
INSERT INTO table_a VALUES(i,LPAD('*',2000,'*'));
IF MOD(i,100) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
/
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO table_b VALUES(i,LPAD('*',2000,'*'));
IF MOD(i,100) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_A', cascade=>true, no_invalidate=>false);
END;
.
l
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_B', cascade=>true, no_invalidate=>false);
END;
.
l
/
SELECT COUNT(1) FROM table_a
.
l
/
SELECT COUNT(1) FROM table_b
.
l
/
REM **** Original - No Hint - 1-0 ****
VARIABLE s NUMBER
VARIABLE e NUMBER
BEGIN
:s := 1;
:e := 5;
END;
.
/
SELECT
*
FROM
table_a a
INNER JOIN table_b b
ON
a.id = b.id
AND a.id BETWEEN :s AND :e
.
l
set autot trace exp stat
/
set autot off
REM **** pattern 1-1 ****
SELECT
/*+
FULL(a)
FULL(b)
USE_HASH(a)
USE_HASH(b)
*/
*
FROM
table_a a
INNER JOIN table_b b
ON
a.id = b.id
AND a.id BETWEEN :s AND :e
.
l
set autot trace exp stat
/
set autot off
REM **** pattern 1-2 ****
SELECT
/*+
FULL(a)
FULL(b)
USE_HASH(a b)
*/
*
FROM
table_a a
INNER JOIN table_b b
ON
a.id = b.id
AND a.id BETWEEN :s AND :e
.
l
set autot trace exp stat
/
set autot off
truncate table table_a
.
l
/
truncate table table_b
.
l
/
BEGIN
FOR i IN 1..50000 LOOP
INSERT INTO table_b VALUES(i,LPAD('*',2000,'*'));
IF MOD(i,100) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
/
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO table_a VALUES(i,LPAD('*',2000,'*'));
IF MOD(i,100) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_A', cascade=>true, no_invalidate=>false);
END;
.
l
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'TABLE_B', cascade=>true, no_invalidate=>false);
END;
.
l
/
SELECT COUNT(1) FROM table_a
.
l
/
SELECT COUNT(1) FROM table_b
.
l
/
REM **** Original - No Hint - 2-0 ****
SELECT
*
FROM
table_a a
INNER JOIN table_b b
ON
a.id = b.id
AND b.id BETWEEN :s AND :e
.
l
set autot trace exp stat
/
set autot off
REM **** pattern 2-1 ****
SELECT
/*+
FULL(a)
FULL(b)
USE_HASH(a)
USE_HASH(b)
*/
*
FROM
table_a a
INNER JOIN table_b b
ON
a.id = b.id
AND b.id BETWEEN :s AND :e
.
l
set autot trace exp stat
/
set autot off
REM **** pattern 2-2 ****
SELECT
/*+
FULL(a)
FULL(b)
USE_HASH(a b)
*/
*
FROM
table_a a
INNER JOIN table_b b
ON
a.id = b.id
AND b.id BETWEEN :s AND :e
.
l
set autot trace exp stat
/
set autot off
やっと、涼しくなってきたけど、涼しくなるのが急すぎて、まじで季節の変化が急激になってきたなぁと。
では、また。
最近のコメント