Search

Top 60 Oracle Blogs

Recent comments

Parallel Queryのチューニング

適当に考えて作った問題のSQL:

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )


少しやり方を変えてみる:


SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%'
5 union
6 select A.* from lineitem A, lineitem_org B, lineitem_comp C
7 where A.l_orderkey = B.l_orderkey
8 and A.l_orderkey = C.l_orderkey
9 and A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%'
10 union
11 select A.* from lineitem A, lineitem_org B, lineitem_comp C
12 where A.l_orderkey = B.l_orderkey
13 and A.l_orderkey = C.l_orderkey
14 and A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%'
15 union
16 select A.* from lineitem A, lineitem_org B, lineitem_comp C
17 where A.l_orderkey = B.l_orderkey
18 and A.l_orderkey = C.l_orderkey
19 and A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%'
20 union
21 select A.* from lineitem A, lineitem_org B, lineitem_comp C
22 where A.l_orderkey = B.l_orderkey
23 and A.l_orderkey = C.l_orderkey
24 and A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%'
25 /

レコードが選択されませんでした。

経過: 00:00:35.94


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

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 2697M| | 1035K (87)| 03:27:08 | | |
| 1 | SORT UNIQUE | | 17M| 2697M| 2868M| 1035K (87)| 03:27:08 | | |
| 2 | UNION-ALL | | | | | | | | |
|* 3 | HASH JOIN | | 2432K| 340M| 87M| 61557 (2)| 00:12:19 | | |
|* 4 | HASH JOIN | | 599K| 80M| 39M| 39831 (2)| 00:07:58 | | |
| 5 | PARTITION RANGE ALL| | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
|* 6 | TABLE ACCESS FULL | LINEITEM | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
| 7 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 80M| | 18849 (2)| 00:03:47 | | |
| 8 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 34M| | 12190 (1)| 00:02:27 | | |
|* 9 | HASH JOIN | | 2497K| 419M| 102M| 93414 (1)| 00:18:41 | | |
| 10 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 34M| | 12190 (1)| 00:02:27 | | |
|* 11 | HASH JOIN | | 615K| 99M| 15M| 70731 (1)| 00:14:09 | | |
|* 12 | TABLE ACCESS FULL | LINEITEM_ORG | 299K| 12M| | 18934 (2)| 00:03:48 | | |
| 13 | PARTITION RANGE ALL| | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
| 14 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
|* 15 | HASH JOIN | | 4929K| 691M| 148M| 65572 (2)| 00:13:07 | | |
| 16 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 80M| | 18849 (2)| 00:03:47 | | |
|* 17 | HASH JOIN | | 1216K| 154M| 39M| 30903 (2)| 00:06:11 | | |
| 18 | PARTITION RANGE ALL| | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
|* 19 | TABLE ACCESS FULL | LINEITEM | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
| 20 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 34M| | 12190 (1)| 00:02:27 | | |
|* 21 | HASH JOIN | | 2497K| 419M| 102M| 93414 (1)| 00:18:41 | | |
| 22 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 34M| | 18773 (1)| 00:03:46 | | |
|* 23 | HASH JOIN | | 615K| 99M| 15M| 64149 (1)| 00:12:50 | | |
|* 24 | TABLE ACCESS FULL | LINEITEM_COMP | 299K| 12M| | 12351 (3)| 00:02:29 | | |
| 25 | PARTITION RANGE ALL| | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
| 26 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
|* 27 | HASH JOIN | | 4929K| 827M| 102M| 98597 (1)| 00:19:44 | | |
| 28 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 34M| | 18773 (1)| 00:03:46 | | |
|* 29 | HASH JOIN | | 1215K| 197M| 15M| 64149 (1)| 00:12:50 | | |
|* 30 | TABLE ACCESS FULL | LINEITEM_COMP | 299K| 12M| | 12351 (3)| 00:02:29 | | |
| 31 | PARTITION RANGE ALL| | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
| 32 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
-----------------------------------------------------------------------------------------------------------------

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

3 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
4 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<"B"."L_COMMITDATE")
6 - filter("A"."L_COMMENT" LIKE '%obata%')
9 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
11 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"B"."L_COMMITDATE")
12 - filter("B"."L_COMMENT" LIKE '%tanaka%' AND "B"."L_COMMENT" IS NOT NULL)
15 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")
17 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
19 - filter("A"."L_COMMENT" LIKE '%ichiro%')
21 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
23 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"C"."L_COMMITDATE")
24 - filter("C"."L_COMMENT" LIKE '%tanaka%' AND "C"."L_COMMENT" IS NOT NULL)
27 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
29 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
30 - filter("C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL)


統計
----------------------------------------------------------
313 recursive calls
0 db block gets
732359 consistent gets
213779 physical reads
0 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed


おぉ!Non ParallelでParallel度=2とほぼ同じ実行時間の36秒になった。

そうか!!!Parallel度=2は、1 slaveでシーケンシャルにReadするだけだから、結果としてこれと同じような動きになるんだ。

USE_CONCATヒントを入れOR条件をUNION ALLに変えてもらうようにすると:

No Hint

DOP MI:SS physical reads x8K(block size)
no parallel 14:33.61 990101 7.55GB
parallel 2 00:32.26 637866 4.87GB
parallel 3 04:38.49 229981 1.8GB
parallel 4 03:48.20 229981 1.8GB
parallel 8 02:58.65 229981 1.8GB

USE_CONCAT

DOP MI:SS physical reads x8K(block size)
no parallel 00:30.57 285878 2.23GB
parallel 2 00:16.03 357962 2.8GB
parallel 3 00:12.57 357962 2.8GB
parallel 4 00:10.68 357962 2.8GB
parallel 8 00:07.90 357962 2.8GB

ちゃんと、Parallel度の増加とともに実行時間は減りました。

Parallel Queryを使っていると、Hintを入れるという感覚を忘れてしまう。
でも、このパターンはAd-Hoc SQLの定番なんだよな。