問題１
次に示すSQL文の駆動表（外部表）はどれでしょうか？
表と索引create table a1 ( id number primary key , data varchar2(1000)) nologging/create table a2 ( id number primary key , data varchar2(1000)) nologging/統計情報TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR-------------- -------------- ---------- ------------- -----------------A1 SYS_C0010377 10000 10000 295A2 SYS_C0010378 2000 2000 59 SELECT /* SQL01 */ /*+ MONITOR USE_NL(a1 a2) */ *FROM a1 INNER JOIN a2 ON a1.id = a2.idWHERE a1.id BETWEEN 1 AND 100/
|
私の答え(予想）
Nested Loop結合かつ、INNER JOINですから駆動表（外部表）はデータセットの小さい方。
(Nested Loop結合でOUTER JOINだと結合順は固定されるので駆動表は見つけやすいですが...INNER JOINの場合はそうはいかないですよね。ちなみに、最近のOracleだと、OUTER JOINでHash結合だと外部表を入れれかえて最適化するケースもあります。いずれどこかでそのネタをやると思います。）
ただ、結合条件キーはどちらも主キーですし、WHERE a1.id BETWEEN 1 AND 100は、a2表にも適用されることになるでしょうから、a1、a2どちらも最大で100行ヒットする可能性はあります。
どちらでも正解になる可能性はありますが...w （最初から引っ掛けかよw
細かい条件は提示していないので、提示した情報だけで机上で駆動表（外部表）を決めるとすれば、全体のサイズが小さい、a2でいいんじゃないでしょうか？（私ならそうします。机上ですから）
（当初、もう少し詳細な情報を提示しようとしていたのですが忘れてました...なのでa1だと思った方も可能性は五分五分ですね。前提条件不足でした。...ごめんなさい。ごめんなさい。）
Oracle Database 12c 12.1.0.2.0の動き
以下、SQLモニタリングレポートをみると、駆動表は、 a2表でした。 :)
ちなみに、a1を駆動表にした場合は、若干Buffer Getsが増加していました。誤差の範囲ですが...
SQL Monitoring Report
Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.00 | 0.00 | 3 | 37 |
=======================================
SQL Plan Monitoring Details (Plan Hash Value=3695212685)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 20 | | |
| 1 | NESTED LOOPS | | 20 | 23 | 1 | +0 | 1 | 20 | | |
| 2 | NESTED LOOPS | | 20 | 23 | 1 | +0 | 1 | 20 | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | A2 | 20 | 3 | 1 | +0 | 1 | 20 | | |
| 4 | INDEX RANGE SCAN | SYS_C0010378 | 20 | 2 | 1 | +0 | 1 | 20 | | |
| 5 | INDEX UNIQUE SCAN | SYS_C0010377 | 1 | | 1 | +0 | 20 | 20 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | A1 | 1 | 1 | 1 | +0 | 20 | 20 | | |
=====================================================================================================================================================
|
問題２
次に示すSQL文の駆動表（外部表）はどれでしょうか？
(表と索引、および、統計情報は問題１と同じです。)
SELECT /* SQL02 */ /*+ MONITOR USE_HASH(a1 a2) */ *FROM a1 INNER JOIN a2 ON a1.id = a2.id/
|
私の答え(予想）
Hash結合でINNER JOINかつ、WHERE句はないのでこの問題は簡単ですよね! （これは迷わないはず！！！）
Hash結合も外部表はデータセットの小さい方ですから...この場合だと、a2が外部表になるはず。
Oracle Database 12c 12.1.0.2.0の動き
以下、SQLモニタリングレポートから....外部表は、予想通りのa2。
SQL Monitoring Report
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.01 | 0.01 | 0.00 | 135 | 497 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1713954154)
==================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
==================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 2000 | | | |
| 1 | HASH JOIN | | 2000 | 121 | 1 | +0 | 1 | 2000 | 1M | | |
| 2 | TABLE ACCESS FULL | A2 | 2000 | 19 | 1 | +0 | 1 | 2000 | | | |
| 3 | TABLE ACCESS FULL | A1 | 10000 | 102 | 1 | +0 | 1 | 10000 | | | |
==================================================================================================================================
|
問題３
次に示すSQL文の駆動表（外部表）はどれでしょうか？
なお、D1とD2の多重度は、D1:D2 = 1:100
(個人的にUMLの多重度表記のほうが好きなので、UML表記の多重度で記述します。）
表と索引create table d1 ( id number , data varchar2(1000)) nologging/alter table d1 add constraint pk_d1 primary key (id) using index nologging/create table d2 ( id number not null , seq# number not null , data varchar2(1000)) nologging/alter table d2 add constraint pk_d2 primary key (id, seq#) using index nologging/統計情報TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR-------------- -------------- ---------- ------------- -----------------D1 PK_D1 200 200 9D2 PK_D2 20000 20000 870 SELECT /* SQL03 */ /*+ MONITOR */ *FROM d2WHERE EXISTS ( SELECT 1 FROM d1 WHERE d1.id = d2.id AND d1.id IN (1,5) )/
|
私の答え（予想）
これはちょっと意地悪な問題ですが、わかる人ならわかるはず。だと思って（信じて）作った問題です。 :)
最近のオプティマイザは、相関副問合せを可能であれば結合に書き換える（unnest）傾向が強いのをご存知でしょうか？
となれば、方向はだいだい見えてきます。
d1.id IN (1,5)から最大で2件ヒットすると予想できますよね。
さらに、Nested Loop結合に書き換え、d2を内部表として結合できれば無駄がない。
つまり、駆動表は d1が理想的なはず。
d2を外部表にしてしまうと、WHERE条件がないので結合に置き換えてしまうとHash結合または、d2を全表走査して相関副問合せを都度実行のいづれかになってしまうでしょうね。(unnestの傾向が強いのでHash結合になる可能性が高そう）
ヒットするデータ件数から考えると、どちらも無駄なデータアクセスが多くなる可能性は高いのではないでしょうか。
Oracle Database 12c 12.1.0.2.0の動き
おおお〜。予想通り、相関副問合せは、unnestされて結合に書き換えられています。 さらに 12cの新機能であるAdaptive Joinになっています。興味深い。
で本題である、外部表はどうなっているかというと....pk_d1をindex only scanしてNested Loop結合になっています。つまり、駆動表は、予想通り、d1になっています。Yahoo!
id=1でHash結合がありますが、試行はしたようですが、実際にはNested Loop結合だったようです。
SQL Monitoring Report
Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.01 | 0.01 | 15 | 44 |
=======================================
SQL Plan Monitoring Details (Plan Hash Value=804853015)
==============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 0 | | |
| 1 | HASH JOIN | | 200 | 5 | 1 | +0 | 1 | 0 | | |
| 2 | NESTED LOOPS | | 200 | 5 | 1 | +0 | 1 | 200 | | |
| 3 | NESTED LOOPS | | 200 | 5 | 1 | +0 | 1 | 200 | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 1 | | |
| 5 | INLIST ITERATOR | | | | 1 | +0 | 1 | 2 | | |
| 6 | INDEX UNIQUE SCAN | PK_D1 | 2 | 1 | 1 | +0 | 2 | 2 | | |
| 7 | INDEX RANGE SCAN | PK_D2 | 1 | 1 | 1 | +0 | 2 | 200 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | D2 | 100 | 2 | 1 | +0 | 200 | 200 | | |
| 9 | INLIST ITERATOR | | | | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED | D2 | 100 | 2 | | | | | | |
| 11 | INDEX RANGE SCAN | PK_D2 | 1 | 1 | | | | | | |
==============================================================================================================================================
|
問題４
次に示すSQL文の駆動表（外部表）はどれでしょうか？
(表と索引、および、統計情報は問題３と同じです。)
SELECT /* SQL04 */ /*+ MONITOR */ *FROM d1 INNER JOIN d2 ON d1.id > d2.id AND d2.id BETWEEN 1 AND 5 AND d2.seq# BETWEEN 2 AND 4/
|
私の答え（予想）
結合条件が ">" であることに気付きましたか？ ;)
これは、Nested Loop結合にも、Hash結合にもなりません。 Merge(sort/merge)結合が選択されます。
内部表は索引が利用できないのでソートが発生します。（ソートは避けられない）
外部表では索引を利用してソート処理が回避可能であれば、回避する。
つまりソート処理の影響を最小にしようとするはず、なので...
d1:d2=1:100という比率からd2のソート処理を重いと判断し回避するために外部表にするだろうなぁ〜〜。
私は、d2が外部表になるだろうと予想しています。
（直積じゃないMerge結合なんて最近お目にかかったことないのですが...)
余談）
Hash結合のない時代のOracleで、Merge結合の実行計画を見せられて、なんでソートしてるんですかね〜と、某ベンダーの方に質問されて一瞬固まったことを思い出したw
Oracle Database 12c 12.1.0.2.0の動き
良かったw。 予想どおりソートを回避したようで、 外部表は、 d2ですね。
SQL Monitoring Report
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.02 | 0.01 | 0.01 | 198 | 48 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=705618498)
=============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
=============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 2955 | | | |
| 1 | MERGE JOIN | | 3966 | 10 | 1 | +0 | 1 | 2955 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | D2 | 20 | 4 | 1 | +0 | 1 | 15 | | | |
| 3 | INDEX RANGE SCAN | PK_D2 | 20 | 3 | 1 | +0 | 1 | 15 | | | |
| 4 | SORT JOIN | | 199 | 6 | 1 | +0 | 15 | 2955 | 145K | | |
| 5 | TABLE ACCESS FULL | D1 | 199 | 5 | 1 | +0 | 1 | 199 | | | |
=============================================================================================================================================
|
問題５
次に示すSQL文の駆動表（外部表）はどれでしょうか？
表と索引定義create table b1 ( id number , data varchar2(1000)) nologging/alter table b1 add constraint pk_b1 primary key(id) using index nologging/create table b3 ( id number , seq# number , data varchar2(1000)) nologging/alter table b3 add constraint pk_b3 primary key (id, seq#) using index nologging/create table b2 ( id number , seq# number , subseq# number , data varchar2(1000)) nologging/alter table b2 add constraint pk_b2 primary key (id ,seq#, subseq#) using index nologging/統計情報TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR-------------- -------------- ---------- ------------- -----------------B1 PK_B1 20000 20000 870B2 PK_B2 5000 5000 228B3 PK_B3 500 500 23ERDと多重度b1 : b3 = 1 : 0..2b3 : b2 = 1 : 0..10(UML表記の多重度で記述しています。） SELECT /* SQL05 */ /*+ MONITOR USE_HASH(b1 b3 b2) */ *FROM b1 INNER JOIN b3 ON b1.id = b3.id INNER JOIN b2 ON b3.id = b2.id AND b3.seq# = b2.seq#/
|
私の答え（予想）
INNER JOINでHash結合かつ、WHERE句もないので簡単な問題ですよね。
外部表は、b3
ついでに、続けると、 b3とb2を結合すると最大で5000件、b3とb1を結合した場合、最大で500件なので、 結合順として理想的なのは、 b3, b1, b2 ですよね。
Oracle Database 12c 12.1.0.2.0の動き
結果は、予想通りでした〜。これは簡単だから！。
SQL Monitoring Report
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.06 | 0.04 | 0.02 | 335 | 1476 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=3711653655)
===================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 5000 | | | |
| 1 | HASH JOIN | | 5000 | 315 | 1 | +0 | 1 | 5000 | 1M | | |
| 2 | HASH JOIN | | 500 | 247 | 1 | +0 | 1 | 500 | 1M | | |
| 3 | TABLE ACCESS FULL | B3 | 500 | 9 | 1 | +0 | 1 | 500 | | | |
| 4 | TABLE ACCESS FULL | B1 | 20000 | 238 | 1 | +0 | 1 | 20000 | | | |
| 5 | TABLE ACCESS FULL | B2 | 5000 | 68 | 1 | +0 | 1 | 5000 | | | |
===================================================================================================================================
|
問題６
次に示すSQL文の駆動表（外部表）はどれでしょうか？
(表と索引、および、統計情報は問題５と同じです。)
SELECT /* SQL06 */ /*+ MONITOR USE_HASH(b1 b3 b2) */ *FROM b1 LEFT OUTER JOIN b3 ON b1.id = b3.id LEFT OUTER JOIN b2 ON b3.id = b2.id AND b3.seq# = b2.seq#/
|
私の答え（予想）
OUTER JOINなのですが、Hash結合なので、多分、予想通りにはならないだろうな〜〜と。
最近のオプティマイザは外部結合かつHash結合の場合、外部表と内部表をデータセットサイズに応じて入れ替える傾向が強いんですよね。PGAの使用サイズが小さくなるように....
とは言っても、机上の話なので、入れ替えないとしたらどうなるだろう..と、外部結合なので、内部表は、そのまま内部表として結合したほうがわかりやすいといえばわかりやすい。
（統計情報に乖離がある場合には入れ替えないほうが良い結果だったりすることもあります。）
注）()内を先に結合するものとします。
入れ替えなかった場合は、記述したままなので、(外部表、内部表)=外部表、内部表ということで、 (b1, b3), b2
データセットの小さい順に従えば、外部表と、内部表をいれかえて、b3, b1この結果セットの最大件数は元の外部表がb1ですから20,250件、b2が5,000件なので、また外部表と内部表を入れ替えて、結果として、b2, (b3, b1)ってことに...
(内部的に結合順が入れ替えられ、Right Joinに置き換えられることが多いんですよね〜いつ頃からだろう...10gあたりか。）
で、机上ならどちらを取るかということですが、机上では、ひとまず、SQL文の通りとしておくケースが多いです。あくまで私の場合ですよ。（難しいんですよ、これ。外れる可能性大w）
オプティマイザが内部的に外部表と内部表を入れ替えて問題がなければよし、問題があれば、内部的な入れ替えをヒントで止めるというのが私の基本方針なので、この辺りは、チューナーさんのさじ加減次第じゃないかなぁ、と思っています。
あはは、むずかし過ぎたか... 机上だと orz.
Oracle Database 12c 12.1.0.2.0の動き
結果を見てみると〜〜〜〜〜っ。恐れていたw 外部表と内部表の置き換え操作(LEFT OUTERがRIGHT OUTER）になってますね〜っ。あはは、見事に、外れました....orz.
SQL Monitoring Report
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.34 | 0.21 | 0.13 | 1651 | 2648 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1453650298)
======================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
======================================================================================================================================
| 0 | SELECT STATEMENT | | | | 4 | +0 | 1 | 24750 | | | |
| 1 | HASH JOIN RIGHT OUTER | | 203K | 316 | 4 | +0 | 1 | 24750 | 3M | | |
| 2 | TABLE ACCESS FULL | B2 | 5000 | 68 | 1 | +0 | 1 | 5000 | | | |
| 3 | HASH JOIN RIGHT OUTER | | 20250 | 247 | 4 | +0 | 1 | 20250 | 1M | | |
| 4 | TABLE ACCESS FULL | B3 | 500 | 9 | 1 | +0 | 1 | 500 | | | |
| 5 | TABLE ACCESS FULL | B1 | 20000 | 238 | 4 | +0 | 1 | 20000 | | | |
======================================================================================================================================
|
問題ごとの解説をおまけで何回かに分けて、書いた方が良さそうだなw
ということで、おまけエントリーを書くかも...し、れ、な、い。
・
机上SQLチューニング、クイズ！ 駆動表（外部表）はどれだ！！！！・
机上SQLチューニング、クイズ！ 駆動表（外部表）はどれだ！！！！ （予想解答編）
最近のコメント