実行計画は、SQL文のレントゲン写真だ！ No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY) Tweet
さて、実行計画のバリエーションの数だけ、レントゲンはありますよー（まだネタには余裕があるw）
ということで、今回は、12c前後で変化したところを見ておきたいと思います。
タイトルにも書いた
TEMP TABLE TRANSFORMATION
LOAD AS SELECT (CURSOR DURATION MEMORY)
は、12c以降のリリースから見られるWITH CTEで繰り返し利用されることが自明で性能改善につながると想定される場合に、一時表にマテリアライズされたときのオペレーションですよね。
（みなさんご存知だとおもいます。
12c以降のリリースしか利用したことのない方は、気づかないと思いますが、11gまで少々違いました。
TEMP TABLE TRANSFORMATION
LOAD AS SELECT
違いといっても、CURSOR DURATION MEMORY があるかないかなのですがw、細かい改善の一つでだよね。と。
ちょうど良いネタなので、昨年末のAdvent CalendarのSQL文と実行計画でバージョン間のレントゲン写真の差を確認しておきましょう:)
まず、一つまえの 19cから。
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Id=2のオペレーションは、LOAD AS SELECT (CURSOR DURATION MEMORY) ですね。
もう一点、Predicate Informationセクションには、/*+ CACHE ("T1") */ というヒントが内部的に利用されていますね。ふーむ。
また、アダプティブな挙動もレポートされていないようですね。計画自体もindex only scanですし、まあ、想定通りというところですね。
参考までに、OPTIMIZER_DYNAMIC_SAMPLING = 2 と。デフォルトのままです。
CURSOR DURATION MEMORYの挙動については、
SQLチューニング・ガイド cursor-duration一時表
にあるように、シリアル実行では、PGAを利用するようですね。パラレルだと違うのか... でいずれもメモリ上に乗らなくなると、一時セグメントがストレージ上に確保されると記載されているのでTEMP表領域が利用されそうですね。
メモリに余裕があり、かつ、繰り返し参照されるケースでは効果はありそうですよね。ストレージに落ちてしまうと、direct path read from tempが発生するでしょうし、ストレージを繰り返しアクセスするかしないかの違いは大きいかも。
とはいえ、WITH句のCTEで性能改善を狙うケースでポイントになるところは同じなので、その点は忘れないようにしておきたいところですね。（例外は巨大なSQLで可読性向上を目的とした場合ぐらい）
Execution Plan
----------------------------------------------------------
Plan hash value: 3964084889
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 553 | 12 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6640_6FF953 | | | | |
| 3 | WINDOW SORT | | 7 | 119 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 7 | 119 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 7 | 77 | 4 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | SYS_C0012896 | 4 | 24 | 1 (0)| 00:00:01 |
|* 7 | INDEX FAST FULL SCAN | SYS_C0012900 | 2 | 10 | 1 (0)| 00:00:01 |
|* 8 | INDEX FAST FULL SCAN | SYS_C0012898 | 1 | 6 | 1 (0)| 00:00:01 |
| 9 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6641_6FF953 | | | | |
| 10 | WINDOW SORT | | 7 | 371 | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | VIEW | | 7 | 371 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_6FF953 | 7 | 119 | 2 (0)| 00:00:01 |
|* 14 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_6FF953 | 7 | 119 | 2 (0)| 00:00:01 |
|* 16 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_6FF953 | 7 | 119 | 2 (0)| 00:00:01 |
| 18 | VIEW | | 7 | 553 | 2 (0)| 00:00:01 |
| 19 | WINDOW SORT | | 7 | 371 | 2 (0)| 00:00:01 |
|* 20 | FILTER | | | | | |
| 21 | VIEW | | 7 | 371 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6641_6FF953 | 7 | 217 | 2 (0)| 00:00:01 |
|* 23 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6641_6FF953 | 7 | 217 | 2 (0)| 00:00:01 |
|* 25 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6641_6FF953 | 7 | 217 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "NAME"='Steve' AND "ANIMALS"."KIND"<>'Dog' AND
"ANIMALS"."KIND"<>'Snake' OR "NAME"='Hiro' AND "ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR
"NAME"='Larry' AND "ANIMALS"."KIND"<>'Snake')
8 - filter(("OWNERS"."NAME"='Hiro' AND "PETS"."NAME"='Tiger' OR "OWNERS"."NAME"<>'Hiro' AND
"PETS"."NAME"<>'Tiger') AND ("NAME"='Wendy' AND "KIND"='Dog' OR "NAME"='Tiger' AND "KIND"<>'Dog' AND
"KIND"<>'Turtle' OR "NAME"='Lisa' AND "KIND"<>'Snake' AND "KIND"<>'Dog' OR "NAME"='Taro' AND "KIND"<>'Dog'))
11 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6640_6FF953" "T1") "UNKNOWN_PET_OWNERS" WHERE "PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND
NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2"
"PET_NAME","C3" "ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6640_6FF953" "T1") "UNKNOWN_PET_OWNERS" WHERE
"ANIMAL_KIND"=:B2 AND "NUM_OF_ROWS"=1))
14 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
16 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
20 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6641_6FF953" "T1") "TEMP_PET_OWNERS" WHERE "PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT
EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6641_6FF953" "T1") "TEMP_PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
23 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
25 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
922 bytes sent via SQL*Net to client
2090 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
では次、12R1の場合です。(12R2は19cと同じなので省略)
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
基本的にデフォルト設定のままなので、 OPTIMIZER_DYNAMIC_SAMPLING = 2 でもろもろ止めたりしていませんw
12cR2以降との違いの１つめ！
12cR1では、CURSOR DURATION MEMORY オペレーションがありません！！
12cR2以降との違いの２つめ！
/*+ CACHE ("T1") */ではなく、/*+ CACHE_TEMP_TABLE ("T1") */ という一時表専用のヒントが担っている点。
CACHE_TEMP_TABLEヒントって解説がないヒントなのですが、12cR2以降はCACHEヒントという解説のある通常の表と同じヒントに置き換えられいますね。一時表だけ特別なわけではないので統一したのでしょうか（中の人のみぞ知るw）
Note部分にもでてますが、動的統計とプランディレクティブが動いてますね。再起コールが減らないのもその影響のようです。19cの挙動とは興味深い違いですね。データ量が多くなった場合にどう変化するのかなという気はしますがw
ちなみに、この挙動は、11cR2とくらべて、プランディレクティブが無い以外の挙動は同じ。
Execution Plan
----------------------------------------------------------
Plan hash value: 3787387246
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 448 | 13 (8)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6647_566AED | | | | |
| 3 | WINDOW SORT | | 8 | 136 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 8 | 136 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 8 | 88 | 4 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | SYS_C0014925 | 4 | 24 | 1 (0)| 00:00:01 |
|* 7 | INDEX FAST FULL SCAN | SYS_C0014929 | 2 | 10 | 1 (0)| 00:00:01 |
|* 8 | INDEX FAST FULL SCAN | SYS_C0014927 | 1 | 6 | 1 (0)| 00:00:01 |
| 9 | LOAD AS SELECT | SYS_TEMP_0FD9D6648_566AED | | | | |
| 10 | WINDOW SORT | | 7 | 371 | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | VIEW | | 7 | 371 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_566AED | 7 | 119 | 2 (0)| 00:00:01 |
|* 14 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_566AED | 7 | 119 | 2 (0)| 00:00:01 |
|* 16 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_566AED | 7 | 119 | 2 (0)| 00:00:01 |
| 18 | SORT ORDER BY | | 7 | 448 | 3 (34)| 00:00:01 |
|* 19 | FILTER | | | | | |
| 20 | VIEW | | 7 | 448 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_566AED | 7 | 217 | 2 (0)| 00:00:01 |
|* 22 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_566AED | 7 | 217 | 2 (0)| 00:00:01 |
|* 24 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_566AED | 7 | 217 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve'
AND "ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
8 - filter(("OWNERS"."NAME"='Hiro' AND "PETS"."NAME"='Tiger' OR "OWNERS"."NAME"<>'Hiro' AND
"PETS"."NAME"<>'Tiger') AND ("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR
"PETS"."NAME"='Tiger' AND "ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR
"PETS"."NAME"='Lisa' AND "ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR
"PETS"."NAME"='Taro' AND "ANIMALS"."KIND"<>'Dog'))
11 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6647_566AED" "T1") "PET_OWNER_UNKNOWN" WHERE
"PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6647_566AED" "T1") "PET_OWNER_UNKNOWN" WHERE
"ANIMAL_KIND"=:B2 AND "NUM_OF_ROWS"=1))
14 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
16 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
19 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6648_566AED" "T1") "PET_OWNERS" WHERE "PET_NAME"=:B1
AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6648_566AED" "T1") "PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
22 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
24 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 5 Sql Plan Directives used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
36 db block gets
126 consistent gets
2 physical reads
1160 redo size
811 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
そして、最後は、私の環境で最も古いOracle 11.1.0.7
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
わかっちゃいたけど、オプティマイザの進化が見えて嬉しいですねw
なかなか苦しい実行計画ですね。これは。。。w
Index only Scanにはなっていますが、マテリアライズする部分の実行計画が.. オプティマイザありがとう。（主に最新版のほうですけどw）
本題にもどると、このリリースではCURSOR DURATION MEMORYがないのは当然ですが、CACHE_TEMP_TABLEヒントが利用されているという点に関しては、12cR1と同じ
そして、アダプティブな挙動も一切ない自体の実行計画ですね。ここには戻りたくないですよね。みなさんw
アダプティブな挙動はないにしても、再起コールがおおいし、実行計画が行けてないのでヒントで補正してみたくなりますよねw
Plan hash value: 3067991639
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 448 | 14 (15)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | WINDOW SORT | | 8 | 240 | 9 (12)| 00:00:01 |
| 4 | CONCATENATION | | | | | |
| 5 | NESTED LOOPS | | 7 | 210 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 9 | 225 | 7 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 5 | 100 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 7 | 105 | 4 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 4 | 40 | 1 (0)| 00:00:01 |
| 10 | INDEX FULL SCAN | SYS_C009964 | 4 | 20 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | SYS_C009964 | 1 | 5 | 0 (0)| 00:00:01 |
|* 12 | INDEX FAST FULL SCAN| SYS_C009962 | 2 | 10 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SYS_C009962 | 1 | 5 | 0 (0)| 00:00:01 |
|* 14 | INDEX FAST FULL SCAN | SYS_C009960 | 2 | 10 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | SYS_C009960 | 1 | 5 | 0 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 30 | 1 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 2 | 50 | 1 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 20 | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 15 | 0 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 10 | 0 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | SYS_C009962 | 1 | 5 | 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | SYS_C009960 | 1 | 5 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | SYS_C009962 | 4 | 20 | 0 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SYS_C009960 | 4 | 20 | 0 (0)| 00:00:01 |
|* 25 | INDEX FULL SCAN | SYS_C009964 | 2 | 10 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | SYS_C009964 | 1 | 5 | 0 (0)| 00:00:01 |
| 27 | LOAD AS SELECT | | | | | |
| 28 | WINDOW SORT | | 7 | 217 | 2 (0)| 00:00:01 |
|* 29 | FILTER | | | | | |
| 30 | VIEW | | 7 | 217 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 32 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 34 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
| 36 | SORT ORDER BY | | 7 | 448 | 3 (34)| 00:00:01 |
|* 37 | FILTER | | | | | |
| 38 | VIEW | | 7 | 448 | 2 (0)| 00:00:01 |
| 39 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 40 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 41 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 42 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("ANIMALS"."KIND"="ANIMALS"."KIND")
12 - filter("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR "PETS"."NAME"='Tiger' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR "PETS"."NAME"='Lisa' AND
"ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR "PETS"."NAME"='Taro' AND
"ANIMALS"."KIND"<>'Dog')
13 - access("PETS"."NAME"="PETS"."NAME")
filter("PETS"."NAME"<>'Tiger')
14 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
15 - access("OWNERS"."NAME"="OWNERS"."NAME")
filter("OWNERS"."NAME"<>'Hiro')
21 - access("PETS"."NAME"='Tiger')
22 - access("OWNERS"."NAME"='Hiro')
filter(LNNVL("OWNERS"."NAME"<>'Hiro') OR LNNVL("PETS"."NAME"<>'Tiger'))
23 - access("PETS"."NAME"="PETS"."NAME")
24 - access("OWNERS"."NAME"="OWNERS"."NAME")
25 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
26 - access("ANIMALS"."KIND"="ANIMALS"."KIND")
filter("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR "PETS"."NAME"='Tiger' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR "PETS"."NAME"='Lisa' AND
"ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR "PETS"."NAME"='Taro' AND
"ANIMALS"."KIND"<>'Dog')
29 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D660C_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE
"PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D660C_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
32 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
34 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
37 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D660D_11BDA4" "T1") "PET_OWNERS" WHERE "PET_NAME"=:B1 AND
"NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
"NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D660D_11BDA4" "T1") "PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND "NUM_OF_ROWS"=1))
40 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
42 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
統計
----------------------------------------------------------
4 recursive calls
16 db block gets
85 consistent gets
2 physical reads
1156 redo size
807 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
アダプティブな挙動がないにしても、再起コールがおおいし、実行計画が行けてないのでヒントで補正してみたくなりますよねw
NO_EXPANDヒントでCONCATENATIONを抑止してみましたw
それでもイマイチだ。古いオプティマイザとの挙動の違いをみると、ほんと、最新版のオプテマイザの良さが身にしみますw
Plan hash value: 2841796482
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 448 | 13 (8)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | WINDOW SORT | | 7 | 210 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 7 | 210 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 12 | 300 | 8 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 7 | 140 | 4 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 7 | 105 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 4 | 40 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | SYS_C009964 | 4 | 20 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C009964 | 1 | 5 | 0 (0)| 00:00:01 |
|* 11 | INDEX FAST FULL SCAN| SYS_C009962 | 2 | 10 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C009962 | 1 | 5 | 0 (0)| 00:00:01 |
|* 13 | INDEX FAST FULL SCAN | SYS_C009960 | 2 | 10 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | SYS_C009960 | 1 | 5 | 0 (0)| 00:00:01 |
| 15 | LOAD AS SELECT | | | | | |
| 16 | WINDOW SORT | | 7 | 217 | 2 (0)| 00:00:01 |
|* 17 | FILTER | | | | | |
| 18 | VIEW | | 7 | 217 | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 20 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 22 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
| 24 | SORT ORDER BY | | 7 | 448 | 3 (34)| 00:00:01 |
|* 25 | FILTER | | | | | |
| 26 | VIEW | | 7 | 448 | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6625_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 28 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6625_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 30 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6625_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("ANIMALS"."KIND"="ANIMALS"."KIND")
11 - filter("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR "PETS"."NAME"='Tiger' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR "PETS"."NAME"='Lisa' AND
"ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR "PETS"."NAME"='Taro' AND
"ANIMALS"."KIND"<>'Dog')
12 - access("PETS"."NAME"="PETS"."NAME")
13 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve'
AND "ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
14 - access("OWNERS"."NAME"="OWNERS"."NAME")
filter("OWNERS"."NAME"='Hiro' AND "PETS"."NAME"='Tiger' OR "OWNERS"."NAME"<>'Hiro' AND
"PETS"."NAME"<>'Tiger')
17 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6624_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE
"PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6624_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
20 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
22 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
25 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6625_11BDA4" "T1") "PET_OWNERS" WHERE "PET_NAME"=:B1 AND
"NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
"NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6625_11BDA4" "T1") "PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
28 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
30 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
統計
----------------------------------------------------------
4 recursive calls
16 db block gets
84 consistent gets
2 physical reads
1156 redo size
807 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
SQLのレントゲン写真、バージョンと共に基本形wが変化していくので、常に差分と最新の内容を把握しておきたいですよね。いざ、診療するとなったときには役に立つ、はず！！
今回利用したSQL文や表定義およびデータは、誰がどんな名前のペットを飼っているのかな？ 解答編 / JPOUG Advent Calendar Day 23を参照ください。
では、また。
Related article on Mac De Oracle
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 20 / MINUS
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は、SQL文のレントゲン写真だ！ No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
| 固定リンク | 0
コメント