GaragebandとiMovieで



検索

  • Google
フォト

Twitter

オンラインショッピング


2023年2月
日 月 火 水 木 金 土
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28        

気まぐれ写真館

  • spider lily - 白と赤の彼岸花（曼珠沙華）

カテゴリー

Oracle

Apple

その他

  • ブログ de ノベルティ
    ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告



RSS

ブログ:ココログ

当サイトについて

プライバシーポリシー
プライバシーポリシー

「個人情報の保護に関する法律」に基づき、「個人情報保護の基本方針（プライバシーポリシー）」を
定め個人情報の適切な管理・保護に努めることを表明します。

個人情報保護の基本方針（プライバシーポリシー）

1.　個人情報の利用目的をできるだけ特定し、特定された利用目的の達成に必要な範囲を超えて利用
　　することはありません。
2.　個人情報を偽りその他不正な手段による取得しません。取得したときは、本人に速やかに利用目的
　　を通知又は公表します。
3.　個人情報を利用目的の範囲内で正確かつ最新の内容に保つように努めます。
4.　個人情報の漏洩や滅失を防ぐために、必要かつ適切な安全管理措置を講じます。
5.　あらかじめ本人の同意を得ないで第三者に個人情報を提供することはありません。
　　ただし、法令に基づく場合、人の生命、身体又は財産の保護に必要な場合、公衆衛生・児童の健全育成に
　　特に必要な場合、国等に協力する場合は除きます。

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

2015年1月 2日 (金) / Author : Hiroshi Sekiguchi.

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

問題１
次に示す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 |          |                 |
=====================================================================================================================================================

問題２
次に示す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                 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 |           |        |       |          |          |                 |
==============================================================================================================================================


問題４
次に示す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               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 |       |          |                 |
===================================================================================================================================


問題６
次に示す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チューニング、クイズ！　駆動表（外部表）はどれだ！！！！ （予想解答編）

投稿日時 2015年1月 2日 (金) 10時38分 Oracle, チューニング, SQL, Oracle Database 12c release 1, パフォーマンス, Index Only Scan | |

トラックバック


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

コメント

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

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

コメントを書く