« 机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編) | トップページ | Meetup! JPOUG @ Oracle CloudWorld 2015 »

2015年1月 2日 (金)

机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (実解答編)

JPOUG Advent Calendar 2014 17日目のエントリーで公開したクイズを実解答編です。
実際にOracle Database 12c 12.1.0.2.0を使ってどの表を駆動表(外部表)にするか確認してみます。


予想はあたるか、外れるか...どのような結果が待っているのか..ワクワクします。 :-)
Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) B71277-02 - 7 結合


前回の予想も参考に....机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編)

問題1
次に示す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 295
A2 SYS_C0010378 2000 2000 59

SELECT
/* SQL01 */
/*+
MONITOR
USE_NL(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
WHERE
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 | | |
=====================================================================================================================================================

問題2
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題1と同じです。)

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 | | | |
==================================================================================================================================


問題3
次に示す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 9
D2 PK_D2 20000 20000 870

SELECT
/* SQL03 */
/*+
MONITOR
*/
*
FROM
d2
WHERE
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 | | | | | | |
==============================================================================================================================================


問題4
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題3と同じです。)

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 | | | |
=============================================================================================================================================


問題5
次に示す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 870
B2 PK_B2 5000 5000 228
B3 PK_B3 500 500 23

ERDと多重度
b1 : b3 = 1 : 0..2
b3 : b2 = 1 : 0..10
(UML表記の多重度で記述しています。
20141221_120733

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 | | | |
===================================================================================================================================


問題6
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題5と同じです。)

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チューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編)

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/60908897

この記事へのトラックバック一覧です: 机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (実解答編):

コメント

初歩的な質問で申し訳ございませんが、教えてください。
(INNER JOINでは)なぜ駆動表はデータセットの小さい方が選択されるのでしょうか?
小さい方を内部表にして、内部表全体がメモリに乗るようにした方が効率が良いように思えたのですが。

投稿: rzpqyo | 2015年1月 5日 (月) 22時43分

コメントを書く