« いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング7 おまけ | トップページ | いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング番外編  »

2011年8月 6日 (土)

いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング8 おまけのおまけ

前回のつづきです。

GROUPING SETSを単純に使っただけでは可読性は向上するが内部で自動的に行われる一時表への展開(WITH句で書くのに似ている)とUNION ALLによる集計時オーバーヘッドが大きくなるようで、WITH句+UNION ALLに書き換えた場合ほど効果はありませんでした。残念。

だったら〜。

ということで、GROUPING SETSは使わず、やってる事がやってる事だから、素直にCUBEを使ってクロス集計しちゃえばオーバーヘッドとなっているWITH句+UNION ALLへの展開も行わないようにすればなって良いんジャマイカ! そうしよう!


で、パーティションは大人の事情でハッシュのままに、WITH句+UNION ALLへの書き換えや、GROUPING SETSで自動的にWITH句+UNION ALLへ展開する方法を止めたのが以下のクエリ。

え〜〜〜〜〜、え〜〜〜〜〜〜。シリアル実行としては最速の結果、6秒台キタ〜〜〜〜!。
可読性は副問合せなどの影響でちょいと落ちますがSQL文は短いのでそれほど影響はないですよね。いままで一番短いのではないでしょうか:)

  1  SELECT
2 CASE
3 WHEN grouping_id(month) = 1 THEN
4 CASE
5 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
6 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
7 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
8 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
9 END
10 ELSE month
11 END AS month
12 ,CASE
13 WHEN grouping_id(shop_code) = 1 THEN 'ALL'
14 ELSE shop_code
15 END AS shop_code
16 ,SUM(sales_figure) AS sales_figure
17 FROM
18 (
19 SELECT
20 SUBSTR(starting_date,1,6) AS month
21 ,shop_code
22 ,SUM(sales_figure) AS sales_figure
23 FROM
24 test2
25 WHERE
26 starting_date BETWEEN '20110101' AND '20110331'
27 GROUP BY
28 SUBSTR(starting_date,1,6)
29 ,shop_code
30 )
31 GROUP BY
32 CUBE(month,shop_code)
33 HAVING
34 shop_code = '1000'
35 OR grouping_id(shop_code) = 1
36 ORDER BY
37 month
38* ,shop_code

8行が選択されました。

経過: 00:00:06.86

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

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1381 | 34525 | 83173 (2)| 00:16:39 | | |
| 1 | SORT ORDER BY | | 1381 | 34525 | 83173 (2)| 00:16:39 | | |
|* 2 | FILTER | | | | | | | |
| 3 | SORT GROUP BY | | 1381 | 34525 | 83173 (2)| 00:16:39 | | |
| 4 | GENERATE CUBE | | 1381 | 34525 | 83173 (2)| 00:16:39 | | |
| 5 | SORT GROUP BY | | 1381 | 34525 | 83173 (2)| 00:16:39 | | |
| 6 | VIEW | | 2758 | 68950 | 83172 (2)| 00:16:39 | | |
| 7 | HASH GROUP BY | | 2758 | 66192 | 83172 (2)| 00:16:39 | | |
| 8 | PARTITION HASH ALL | | 394K| 9242K| 83161 (2)| 00:16:38 | 1 | 4 |
|* 9 | TABLE ACCESS FULL | TEST2 | 394K| 9242K| 83161 (2)| 00:16:38 | 1 | 4 |
---------------------------------------------------------------------------------------------------

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

2 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,
SYS_OP_BITVEC)))=1)
9 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
302587 consistent gets
302556 physical reads
0 redo size
871 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
8 rows processed

お約束、オラクルお任せのパラレル実験。 

お〜〜〜〜〜〜〜〜〜〜〜〜っ。キター、1.2秒台〜〜〜〜〜。 パーティションタイプのミスという痛恨の設計ミスを帳消にしてあげたかも。(それは嘘。
そもそも、アクセスするブロック数を減らすのはハッシュパーティションのままではもう、むーりーw 限界です。

  1  SELECT /*+ PARALLEL */
2 CASE
3 WHEN grouping_id(month) = 1 THEN
4 CASE
5 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
6 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
7 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
8 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
9 END
10 ELSE month
11 END AS month
12 ,CASE
13 WHEN grouping_id(shop_code) = 1 THEN 'ALL'
14 ELSE shop_code
15 END AS shop_code
16 ,SUM(sales_figure) AS sales_figure
17 FROM
18 (
19 SELECT
20 SUBSTR(starting_date,1,6) AS month
21 ,shop_code
22 ,SUM(sales_figure) AS sales_figure
23 FROM
24 test2
25 WHERE
26 starting_date BETWEEN '20110101' AND '20110331'
27 GROUP BY
28 SUBSTR(starting_date,1,6)
29 ,shop_code
30 )
31 GROUP BY
32 CUBE(month,shop_code)
33 HAVING
34 shop_code = '1000'
35 OR grouping_id(shop_code) = 1
36 ORDER BY
37 month
38* ,shop_code

8行が選択されました。

経過: 00:00:01.24

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

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,03 | P->P | RANGE |
|* 6 | FILTER | | | | | | | | Q1,03 | PCWC | |
| 7 | SORT GROUP BY | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,03 | PCWP | |
| 8 | PX RECEIVE | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,03 | PCWP | |
| 9 | PX SEND HASH | :TQ10002 | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,02 | P->P | HASH |
| 10 | GENERATE CUBE | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,02 | PCWC | |
| 11 | SORT GROUP BY | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,02 | PCWP | |
| 12 | PX RECEIVE | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,02 | PCWP | |
| 13 | PX SEND HASH | :TQ10001 | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,01 | P->P | HASH |
| 14 | SORT GROUP BY | | 1381 | 34525 | 5773 (2)| 00:01:10 | | | Q1,01 | PCWP | |
| 15 | VIEW | | 2758 | 68950 | 5772 (2)| 00:01:10 | | | Q1,01 | PCWP | |
| 16 | HASH GROUP BY | | 2758 | 66192 | 5772 (2)| 00:01:10 | | | Q1,01 | PCWP | |
| 17 | PX RECEIVE | | 2758 | 66192 | 5772 (2)| 00:01:10 | | | Q1,01 | PCWP | |
| 18 | PX SEND HASH | :TQ10000 | 2758 | 66192 | 5772 (2)| 00:01:10 | | | Q1,00 | P->P | HASH |
| 19 | HASH GROUP BY | | 2758 | 66192 | 5772 (2)| 00:01:10 | | | Q1,00 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 394K| 9242K| 5770 (2)| 00:01:10 | 1 | 4 | Q1,00 | PCWC | |
|* 21 | TABLE ACCESS FULL | TEST2 | 394K| 9242K| 5770 (2)| 00:01:10 | 1 | 4 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

6 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,SYS_OP_BITVEC)))=1)
21 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101' AND
"STARTING_DATE">='20110101')

Note
-----
- automatic DOP: Computed Degree of Parallelism is 16


統計
----------------------------------------------------------
96 recursive calls
0 db block gets
304109 consistent gets
302556 physical reads
0 redo size
871 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
65 sorts (memory)
0 sorts (disk)
8 rows processed


今一度、最初のイケてないクエリと比較。あ〜、ダメダメだ〜。やっぱり無駄が多すぎですね!

  1  SELECT
2 month
3 ,shop_code
4 ,sales_figure
5 FROM
6 (
7 SELECT
8 SUBSTR(starting_date,1,6) AS month
9 ,shop_code
10 ,SUM(sales_figure) AS sales_figure
11 FROM
12 test2
13 WHERE
14 starting_date BETWEEN '20110101' AND '20110331'
15 AND shop_code = '1000'
16 GROUP BY
17 SUBSTR(starting_date,1,6)
18 ,shop_code
19 UNION ALL
20 SELECT
21 CASE
22 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
23 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
24 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
25 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
26 END AS month
27 ,shop_code
28 ,SUM(sales_figure) AS salles_figure
29 FROM
30 test2
31 WHERE
32 starting_date BETWEEN '20110101' AND '20110331'
33 AND shop_code = '1000'
34 GROUP BY
35 CASE
36 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
37 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
38 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
39 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
40 END
41 ,shop_code
42 UNION ALL
43 SELECT
44 SUBSTR(starting_date,1,6) AS month
45 ,'ALL ' AS shop_code
46 ,SUM(sales_figure) AS sales_figure
47 FROM
48 test2
49 WHERE
50 starting_date BETWEEN '20110101' AND '20110331'
51 GROUP BY
52 SUBSTR(starting_date,1,6)
53 UNION ALL
54 SELECT
55 CASE
56 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
57 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
58 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
59 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
60 END AS month
61 ,'ALL ' AS shop_code
62 ,SUM(sales_figure) AS salles_figure
63 FROM
64 test2
65 WHERE
66 starting_date BETWEEN '20110101' AND '20110331'
67 GROUP BY
68 CASE
69 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
70 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
71 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
72 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
73 END
74 )
75 ORDER BY
76 month
77* ,shop_code

8行が選択されました。

経過: 00:00:20.92

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

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Pstart| Pstop|
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 726 | 166K (2)| 00:33:18 | | |
| 1 | SORT ORDER BY | | 22 | 726 | 166K (2)| 00:33:18 | | |
| 2 | VIEW | | 22 | 726 | 166K (2)| 00:33:18 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | HASH GROUP BY | | 3 | 72 | 43 (3)| 00:00:01 | | |
| 5 | PARTITION HASH ALL | | 303 | 7272 | 42 (0)| 00:00:01 | 1 | 4 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST2 | 303 | 7272 | 42 (0)| 00:00:01 | 1 | 4 |
|* 7 | INDEX RANGE SCAN | PK_TEST2 | 303 | | 19 (0)| 00:00:01 | 1 | 4 |
| 8 | HASH GROUP BY | | 7 | 119 | 43 (3)| 00:00:01 | | |
| 9 | PARTITION HASH ALL | | 303 | 5151 | 42 (0)| 00:00:01 | 1 | 4 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST2 | 303 | 5151 | 42 (0)| 00:00:01 | 1 | 4 |
|* 11 | INDEX RANGE SCAN | PK_TEST2 | 303 | | 19 (0)| 00:00:01 | 1 | 4 |
| 12 | HASH GROUP BY | | 3 | 57 | 83172 (2)| 00:16:39 | | |
| 13 | PARTITION HASH ALL | | 394K| 7316K| 83161 (2)| 00:16:38 | 1 | 4 |
|* 14 | TABLE ACCESS FULL | TEST2 | 394K| 7316K| 83161 (2)| 00:16:38 | 1 | 4 |
| 15 | HASH GROUP BY | | 9 | 108 | 83172 (2)| 00:16:39 | | |
| 16 | PARTITION HASH ALL | | 394K| 4621K| 83161 (2)| 00:16:38 | 1 | 4 |
|* 17 | TABLE ACCESS FULL | TEST2 | 394K| 4621K| 83161 (2)| 00:16:38 | 1 | 4 |
-------------------------------------------------------------------------------------------------------------------

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

7 - access("SHOP_CODE"='1000' AND "STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110331')
filter(SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101')
11 - access("SHOP_CODE"='1000' AND "STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110331')
filter(SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101')
14 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')
17 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
606978 consistent gets
605112 physical reads
0 redo size
875 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed

前回の結果なのでここには載せませんが、GROUPSING SETSで書き換えた場合や、WITH句+UNION ALLで書き換えた場合でも一時表への書き込み及び一時表からの読み込みは複数回発生し、それがボディーブローのように効いていたんよね。:)

これもお約束、そもそもパーティションタイプが月単位のレンジパーティションだったら〜(タラレバw)どんな結果になるか。

キター、シリアル実行でも4秒台、4秒切りそうな勢いです:)
月単位のレンジパーティションにより無駄なブロックを読み込む必要がなくなったことが効いてますね。

  1  SELECT
2 CASE
3 WHEN grouping_id(month) = 1 THEN
4 CASE
5 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
6 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
7 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
8 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
9 END
10 ELSE month
11 END AS month
12 ,CASE
13 WHEN grouping_id(shop_code) = 1 THEN 'ALL'
14 ELSE shop_code
15 END AS shop_code
16 ,SUM(sales_figure) AS sales_figure
17 FROM
18 (
19 SELECT
20 SUBSTR(starting_date,1,6) AS month
21 ,shop_code
22 ,SUM(sales_figure) AS sales_figure
23 FROM
24 test3
25 WHERE
26 starting_date BETWEEN '20110101' AND '20110331'
27 GROUP BY
28 SUBSTR(starting_date,1,6)
29 ,shop_code
30 )
31 GROUP BY
32 CUBE(month,shop_code)
33 HAVING
34 shop_code = '1000' OR
35 grouping_id(shop_code) = 1
36 ORDER BY
37 month
38* ,shop_code

8行が選択されました。

経過: 00:00:04.04

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

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | | 52177 (2)| 00:10:27 | | |
| 1 | SORT ORDER BY | | 1 | 32 | | 52177 (2)| 00:10:27 | | |
|* 2 | FILTER | | | | | | | | |
| 3 | SORT GROUP BY | | 1 | 32 | | 52177 (2)| 00:10:27 | | |
| 4 | GENERATE CUBE | | 1 | 32 | | 52177 (2)| 00:10:27 | | |
| 5 | SORT GROUP BY | | 1 | 32 | | 52177 (2)| 00:10:27 | | |
| 6 | VIEW | | 80894 | 2527K| | 52171 (2)| 00:10:27 | | |
| 7 | HASH GROUP BY | | 80894 | 1342K| 428M| 52171 (2)| 00:10:27 | | |
| 8 | PARTITION RANGE ITERATOR | | 15M| 258M| | 20630 (2)| 00:04:08 | 1 | 3 |
|* 9 | TABLE ACCESS FULL | TEST3 | 15M| 258M| | 20630 (2)| 00:04:08 | 1 | 3 |
-----------------------------------------------------------------------------------------------------------------

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

2 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,SYS_OP_BITVEC)
))=1)
9 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
73961 consistent gets
25665 physical reads
0 redo size
871 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
8 rows processed

これまた恒例になった、オラクル任せのパラレル処理〜〜。ついにでました、1秒切りそうな処理時間w

  1  SELECT /*+ PARALLEL */
2 CASE
3 WHEN grouping_id(month) = 1 THEN
4 CASE
5 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
6 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
7 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
8 WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
9 END
10 ELSE month
11 END AS month
12 ,CASE
13 WHEN grouping_id(shop_code) = 1 THEN 'ALL'
14 ELSE shop_code
15 END AS shop_code
16 ,SUM(sales_figure) AS sales_figure
17 FROM
18 (
19 SELECT
20 SUBSTR(starting_date,1,6) AS month
21 ,shop_code
22 ,SUM(sales_figure) AS sales_figure
23 FROM
24 test3
25 WHERE
26 starting_date BETWEEN '20110101' AND '20110331'
27 GROUP BY
28 SUBSTR(starting_date,1,6)
29 ,shop_code
30 )
31 GROUP BY
32 CUBE(month,shop_code)
33 HAVING
34 shop_code = '1000' OR
35 grouping_id(shop_code) = 1
36 ORDER BY
37 month
38* ,shop_code

8行が選択されました。

経過: 00:00:01.09

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

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | | 13834 (2)| 00:02:47 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,03 | P->P | RANGE |
|* 6 | FILTER | | | | | | | | | Q1,03 | PCWC | |
| 7 | SORT GROUP BY | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,03 | PCWP | |
| 8 | PX RECEIVE | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,03 | PCWP | |
| 9 | PX SEND HASH | :TQ10002 | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,02 | P->P | HASH |
| 10 | GENERATE CUBE | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,02 | PCWC | |
| 11 | SORT GROUP BY | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,02 | PCWP | |
| 12 | PX RECEIVE | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,02 | PCWP | |
| 13 | PX SEND HASH | :TQ10001 | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,01 | P->P | HASH |
| 14 | SORT GROUP BY | | 1 | 32 | | 13834 (2)| 00:02:47 | | | Q1,01 | PCWP | |
| 15 | VIEW | | 80894 | 2527K| | 13831 (2)| 00:02:46 | | | Q1,01 | PCWP | |
| 16 | HASH GROUP BY | | 80894 | 1342K| 428M| 13831 (2)| 00:02:46 | | | Q1,01 | PCWP | |
| 17 | PX RECEIVE | | 80894 | 1342K| | 13831 (2)| 00:02:46 | | | Q1,01 | PCWP | |
| 18 | PX SEND HASH | :TQ10000 | 80894 | 1342K| | 13831 (2)| 00:02:46 | | | Q1,00 | P->P | HASH |
| 19 | HASH GROUP BY | | 80894 | 1342K| 428M| 13831 (2)| 00:02:46 | | | Q1,00 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 15M| 258M| | 5726 (2)| 00:01:09 | 1 | 3 | Q1,00 | PCWC | |
|* 21 | TABLE ACCESS FULL | TEST3 | 15M| 258M| | 5726 (2)| 00:01:09 | 1 | 3 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------

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

6 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,SYS_OP_BITVEC)))=1)
21 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')

Note
-----
- automatic DOP: Computed Degree of Parallelism is 4


統計
----------------------------------------------------------
24 recursive calls
0 db block gets
74326 consistent gets
73923 physical reads
0 redo size
871 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
8 rows processed


以上、いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング 無事に終了。(おわらすのか〜w)


おまけのおまけまでチューニングしハッシュパーティションのままでも6秒台までになることは確認しましたが、実際に提示したクエリは、UNION ALLで記述された無駄の多いダメダメクエリをWITH句とUNION ALLに書き換えた8秒台の結果となるクエリでした。

なぜ、今回のおまけのおまけで載せた最速クエリを提示しなかったか。。。。。その理由は・・・・・・、8秒台であればユーザと調整可能なレベルだったから、元クエリを書いた方に書き換え方を伝えやすかった(UNION ALLを残しつつチューニング)という、これまた大人の事情が絡んでいます;)。

いま以上にチューニングできるとはわかっていても、余力を残しつつ許容できるレベルでチューニングを止めるという”チューニングのゴール”は大切ですよ。(チューニングに割ける時間もお金も限りがありますから)

8秒台のクエリもデータ量や処理量が増加した際、再び問題視される時期が来るかもしれません。その時は、GROUP BY+CUBEにすれば喜ばれますよ。月単位のレンジパーティションに切り替えてあげるだけでも喜ばれますよ(より工数掛かりますが)。チューニング余地は見切れていたほうがいいですよね。


追記、なにか忘れてるー、と思ったらPIVOTの事すっかり忘れてたw。どうしようw

さて、次のネタのキーワードは、「悩ませ過ぎは及ばざるがごとし」w にしようかな。




これまでのあらずじ…

いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #1
いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #2
いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #3
いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #4
いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #5
いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #6
いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #7 おまけ

|

トラックバック

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

この記事へのトラックバック一覧です: いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング8 おまけのおまけ:

コメント

コメントを書く