« rownum使って満足しちゃってると..... #4 | トップページ | Oracle Database 12c R1 12.1.0.1.0 released! »

2013年6月23日 (日)

rownum使って満足しちゃってると.....おまけ

rownumを使っているチューニングしました! (キリっ

と、しているのに以下の例ようにハッシュ結合が行われ残念な結果になっていることってありませんか? (私はよく目にします (@@)

rownumの弱点を説明している実行計画をよーーーく見返してください。 ハッシュ結合にはしていません

なぜだか分かりますか?

11:14:44 SCOTT> l
1 select *
2 from
3 tc join tb
4 on
5 tc.version = tb.version
6 and tc.branch# = tb.branch#
7 join ta
8 on
9 tb.version = ta.version
10 where
11 tc.version between 1 and 10
12* and rownum <= 1000
11:14:44 SCOTT> /

1000行が選択されました。

経過: 00:00:59.08

実行計画
----------------------------------------------------------
Plan hash value: 2217783216

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 4923K| | 201K (1)| 00:40:14 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | HASH JOIN | | 1001 | 4928K| | 201K (1)| 00:40:14 |
| 3 | TABLE ACCESS BY INDEX ROWID | TA | 10 | 10040 | | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_TA | 10 | | | 1 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 2002 | 3947K| 1161M| 201K (1)| 00:40:14 |
|* 6 | TABLE ACCESS FULL | TC | 1190K| 1148M| | 141K (1)| 00:28:20 |
| 7 | TABLE ACCESS BY INDEX ROWID| TB | 10001 | 9844K| | 1456 (0)| 00:00:18 |
|* 8 | INDEX RANGE SCAN | PK_TB | | | | 26 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=1000)
2 - access("TB"."VERSION"="TA"."VERSION")
4 - access("TA"."VERSION">=1 AND "TA"."VERSION"<=10)
5 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
6 - filter("TC"."VERSION"<=10 AND "TC"."VERSION">=1)
8 - access("TB"."VERSION">=1 AND "TB"."VERSION"<=10)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
514382 consistent gets
514341 physical reads
0 redo size
1050353 bytes sent via SQL*Net to client
1245 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed

ハッシュ結合などは典型的なのですが、操作を始めるとその操作が終了するまで次の操作に移行しないんですよ。

上記の例では赤い部分のハッシュ結合が終了するまでCOUNT STOPKEY操作(Id=1の部分)つまり、rownumの効果が発揮できないわけです。(残念!


結合がありrownumを使って行数制限している場合には、”大抵の場合" Nested Loop結合にしたほうがいいんですよ! OLTP系のようにレスポンスを重視する傾向が強い場合は... (弱点もありますが...

レスポンス重視かスループット重視か、オプティマイザが実行計画を組み立てる際に影響をパラメータがありますよね、  レースカーのフロントウイングやりやウィングのように立てたり寝かしたりするようなパラメータが.... :)

話がそれてしまいましたが...w

11:13:28 SCOTT> l
1 select /*+ leading(tc) use_nl(tc tb ta) */ *
2 from
3 tc join tb
4 on
5 tc.version = tb.version
6 and tc.branch# = tb.branch#
7 join ta
8 on
9 tb.version = ta.version
10 where
11 tc.version between 1 and 10
12* and rownum <= 1000
11:13:29 SCOTT> /

1000行が選択されました。

経過: 00:00:00.29

実行計画
----------------------------------------------------------
Plan hash value: 2389411418

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 2952K| 3798 (1)| 00:00:46 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1001 | 2955K| 3798 (1)| 00:00:46 |
| 4 | NESTED LOOPS | | 2002 | 3947K| 2796 (1)| 00:00:34 |
| 5 | TABLE ACCESS BY INDEX ROWID| TC | 4004 | 3953K| 571 (0)| 00:00:07 |
|* 6 | INDEX RANGE SCAN | PK_TC | | | 13 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| TB | 1 | 1008 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_TB | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_TA | 1 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | TA | 1 | 1004 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=1000)
6 - access("TC"."VERSION">=1 AND "TC"."VERSION"<=10)
8 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
filter("TB"."VERSION"<=10 AND "TB"."VERSION">=1)
9 - access("TB"."VERSION"="TA"."VERSION")
filter("TA"."VERSION">=1 AND "TA"."VERSION"<=10)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2418 consistent gets
0 physical reads
0 redo size
1050353 bytes sent via SQL*Net to client
1245 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed

>結合がありrownumを使って行数制限している場合には、”大抵の場合" Nested Loop結合にしたほうがいいんですよ! OLTP系の場合は特に. (弱点もありますが...

と書きましたが、”大抵の場合”と書いたのには理由があります。

あえてハッシュ結合にする場合もあるんです。 え!、え!、え〜〜〜〜〜っ!w

”常に遅いけど、Nested Loop結合+rownumにした場合の弱点”は絶対避けたいような場合です。

Nested Loop結合+rownumにした場合の弱点の処理時間が、ハッシュ結合+rownumにした場合の処理時間を大幅に上回り、想定しているバッチ処理時間を超過してしまう可能性が高い場合がそれです。

状況に合わせて使い分けたいですよね。机上だけだと難しいのですが、実マシン、実データで検証を繰り返していれば対処できる問題だと思います。


rownum使って満足しちゃってると..... #1
rownum使って満足しちゃってると..... #2
rownum使って満足しちゃってると..... #3
rownum使って満足しちゃってると..... #4

|

トラックバック

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

この記事へのトラックバック一覧です: rownum使って満足しちゃってると.....おまけ:

コメント

コメントを書く