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

2015年1月 1日 (木)

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

JPOUG Advent Calendar 2014 17日目のエントリーで公開したクイズの予想解答編です。
机上ですし、限られた情報しか提示していないので、実際にやってみたら違う...ということも十分ありえます。 (^^;;;;


外部表はどれがいいか予想できると、いろいろな制限のあるチューニング現場では役立つこともあるんです。LEADINGヒント等で結合順を考える場合にも役立ちますyo!
机上の予想なので間違うこともあります。オプティマイザも統計情報から予想しているわけで(Adaptiveな機能を除く)ハズすことも多いですから... (^^

Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) B71277-02 - 7 結合


問題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の場合はそうはいかないですよね)

ただ、結合条件キーはどちらも主キーですし、WHERE a1.id BETWEEN 1 AND 100は、a2表にも適用されることになるでしょうから、a1、a2どちらも最大で100行ヒットする可能性はあります。
どちらでも正解になる可能性はありますが...w (最初から引っ掛けかよw

細かい条件は提示していないので、提示した情報だけで机上で駆動表(外部表)を決めるとすれば、全体のサイズが小さい、a2でいいんじゃないでしょうか?(私ならそうします。机上ですから)
(当初、もう少し詳細な情報を提示しようとしていたのですが忘れてました...なのでa1だと思った方も可能性は五分五分ですね。前提条件不足でした。...ごめんなさい。ごめんなさい。)


問題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が外部表になるはず。

問題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結合になる可能性が高そう)
ヒットするデータ件数から考えると、どちらも無駄なデータアクセスが多くなる可能性は高いのではないでしょうか。

問題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


問題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 ですよね。


問題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.

結合順がほぼ想定できる場合、状況次第で変わりそうな結合順、いろいろありますよね。
オプティマイザの気持ちになって考えてみると、いろいろ気づくことも多いんじゃないかなぁ。と思います。

オプティマイザってソートが嫌いなんだ〜、とか、PGA少ない方が好きなんだ〜とか....


役に立ったか、どうなのか不明なオチになりましたが.....

本年もよろしくお願いいたします。 m(_ _)m


あ、そうそう、一つ忘れてました。

次回は、Oracle Database 12c 12.1.0.2.0を使って試したオプティマイザが選択した駆動表(外部表)がどれだったかのか公開する予定です。


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

|

トラックバック

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

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

コメント

コメントを書く