いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #5 Tweet
前回のつづきです。
さて、前回登場したボスキャラなSQL。どう料理しましょーか。
前回は、まずは肩ならし程度のチューニングをしましたがそれほど改善していません。(^^;;;; 元が元ですからね。
このままでは多いく改善することはむーりー、なので構文変更を前提にしましょう。
で、このクエリをよーく見ていたら、店、品目毎の日単位の売上げデータから、店毎の月単位売上げ合計と四半期の売上げ合計、全店の月単位売上げ合計、四半期の売上げ合計を求めるクエリだとわかりました。
本来なら月単位のレンジパーティション表とか、マテリアライズドビューでも用意しておけばすげー簡単な話ではあるんですが、パーティションタイプの変更とかマテリアライズドビューの追加作成とかむーりーと言われてしまう大人の事情が因縁をつけてきますw
でも、じ〜〜〜〜〜〜っと、このSQL文を見ていると改善案が見えてきます。大きな無駄が……………………見えてきますよね。ほーら、ほーらw
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:21.03
実行計画
----------------------------------------------------------
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
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed
03:43:16 SCOTT>
見えてきましたよね、無駄が。
このクエリ、同じ検索範囲のデータを対象に４回もTEST2表にアクセスしています。全表走査２回、改善したとはいえ２回の全ローカル索引のレンジスキャンを行っています。無駄なアクセスが多すぎます。
こんなタイプのUNION ALLやUNION繰り返しタイプのクエリを改善できる方法は……
まず浮かんだのは、WITH句を使って各クエリの共通部分をまとめてしまう方法です。
WITH句に記述したクエリは最初に１度実行され結果を一時セグメントに溜め込みます。その後、一時セグメントに溜め込んだ結果を使い回せばいいので巨大な表本体を何度も参照する必要がなくなります。
そうそう、うまく使えば、都度使い捨てのマテリアライズドビュー（例えが良くないですが気にしないでね）みたいな使い方ができます。
書き換えのポイントは一番大きな範囲で集計する共通部分を見つけ出してWITH句にまとめてしまうこと！
あとはその結果をうまく使い回すこと！ それだけです。 SQL文も共通部分をWITH句にまとめたことで随分読みやすくなりましたよね。 直感的に読みやすい訳ではないですがね。：）
以下、書き換えた結果…
1 WITH
2 t01 AS
3 (
4 SELECT
5 SUBSTR(starting_date,1,6) AS month
6 ,CASE
7 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
8 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
9 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
10 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
11 END AS quarter
12 ,shop_code
13 ,SUM(sales_figure) as sales_figure
14 FROM
15 test2
16 WHERE
17 starting_date BETWEEN '20110101' AND '20110331'
18 GROUP BY
19 SUBSTR(starting_date,1,6)
20 ,SUBSTR(starting_date,5,2)
21 ,shop_code
22 ),
23 t02 AS
24 (
25 SELECT
26 month
27 ,quarter
28 ,shop_code
29 ,sales_figure
30 FROM
31 t01
32 WHERE
33 month BETWEEN '201101' AND '201103'
34 AND shop_code = '1000'
35 )
36 SELECT
37 month
38 ,shop_code
39 ,sales_figure
40 FROM
41 (
42 SELECT
43 month
44 ,shop_code
45 ,SUM(sales_figure) AS sales_figure
46 FROM
47 t02
48 GROUP BY
49 month
50 ,shop_code
51 UNION ALL
52 SELECT
53 quarter AS month
54 ,shop_code
55 ,SUM(sales_figure) AS sales_figure
56 FROM
57 t02
58 GROUP BY
59 quarter
60 ,shop_code
61 UNION ALL
62 SELECT
63 month
64 ,'ALL ' AS shop_code
65 ,SUM(sales_figure) AS sales_figure
66 FROM
67 t01
68 GROUP BY
69 month
70 UNION ALL
71 SELECT
72 quarter AS month
73 ,'ALL ' AS shop_code
74 ,SUM(sales_figure) AS salles_figure
75 FROM
76 t01
77 GROUP BY
78 quarter
79 )
80 ORDER BY
81 month
82* ,shop_code
8行が選択されました。
経過: 00:00:08.35
実行計画
----------------------------------------------------------
Plan hash value: 2861358139
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3692 | 118K| 83282 (2)| 00:16:40 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660A_4A53712A | | | | | | |
| 3 | HASH GROUP BY | | 17550 | 411K| 83172 (2)| 00:16:39 | | |
| 4 | PARTITION HASH ALL | | 394K| 9242K| 83161 (2)| 00:16:38 | 1 | 4 |
|* 5 | TABLE ACCESS FULL | TEST2 | 394K| 9242K| 83161 (2)| 00:16:38 | 1 | 4 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_4A53712A | | | | | | |
|* 7 | VIEW | | 17550 | 497K| 17 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_4A53712A | 17550 | 411K| 17 (0)| 00:00:01 | | |
| 9 | SORT ORDER BY | | 3692 | 118K| 93 (8)| 00:00:02 | | |
| 10 | VIEW | | 3692 | 118K| 92 (7)| 00:00:02 | | |
| 11 | UNION-ALL | | | | | | | |
| 12 | HASH GROUP BY | | 2758 | 68950 | 27 (4)| 00:00:01 | | |
| 13 | VIEW | | 17550 | 428K| 26 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_4A53712A | 17550 | 497K| 26 (0)| 00:00:01 | | |
| 15 | HASH GROUP BY | | 920 | 20240 | 27 (4)| 00:00:01 | | |
| 16 | VIEW | | 17550 | 377K| 26 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_4A53712A | 17550 | 497K| 26 (0)| 00:00:01 | | |
| 18 | HASH GROUP BY | | 13 | 260 | 18 (6)| 00:00:01 | | |
| 19 | VIEW | | 17550 | 342K| 17 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_4A53712A | 17550 | 411K| 17 (0)| 00:00:01 | | |
| 21 | HASH GROUP BY | | 1 | 17 | 18 (6)| 00:00:01 | | |
| 22 | VIEW | | 17550 | 291K| 17 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_4A53712A | 17550 | 411K| 17 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')
7 - filter("MONTH">='201101' AND "MONTH"<='201103' AND "SHOP_CODE"='1000')
統計
----------------------------------------------------------
4 recursive calls
28 db block gets
302642 consistent gets
302570 physical reads
1156 redo size
875 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed
04:15:55 SCOTT>
アクセスしているブロック数は約１／２に減少し処理時間も２０秒台から８秒台へ改善することができました。バンザーイ！w
ついでなので、オラクル任せのパラレルクエリだとどうなるか試してみました。（CPUたくさんあるんでw)
アクセスブロック数は少々増えますが、パラレルの良さが出ています。２秒台になりましたー。しかしーw 、影響が大きいのでこのときは参考出品で、ここまでできるよーって話のネタだけにしときました :)
1 WITH
2 t01 AS
3 (
4 SELECT
5 SUBSTR(starting_date,1,6) AS month
6 ,CASE
7 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
8 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
9 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
10 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
11 END AS quarter
12 ,shop_code
13 ,SUM(sales_figure) as sales_figure
14 FROM
15 test2
16 WHERE
17 starting_date BETWEEN '20110101' AND '20110331'
18 GROUP BY
19 SUBSTR(starting_date,1,6)
20 ,SUBSTR(starting_date,5,2)
21 ,shop_code
22 ),
23 t02 AS
24 (
25 SELECT
26 month
27 ,quarter
28 ,shop_code
29 ,sales_figure
30 FROM
31 t01
32 WHERE
33 month BETWEEN '201101' AND '201103'
34 AND shop_code = '1000'
35 )
36 SELECT /*+ PARALLEL */
37 month
38 ,shop_code
39 ,sales_figure
40 FROM
41 (
42 SELECT
43 month
44 ,shop_code
45 ,SUM(sales_figure) AS sales_figure
46 FROM
47 t02
48 GROUP BY
49 month
50 ,shop_code
51 UNION ALL
52 SELECT
53 quarter AS month
54 ,shop_code
55 ,SUM(sales_figure) AS sales_figure
56 FROM
57 t02
58 GROUP BY
59 quarter
60 ,shop_code
61 UNION ALL
62 SELECT
63 month
64 ,'ALL ' AS shop_code
65 ,SUM(sales_figure) AS sales_figure
66 FROM
67 t01
68 GROUP BY
69 month
70 UNION ALL
71 SELECT
72 quarter AS month
73 ,'ALL ' AS shop_code
74 ,SUM(sales_figure) AS salles_figure
75 FROM
76 t01
77 GROUP BY
78 quarter
79 )
80 ORDER BY
81 month
82* ,shop_code
8行が選択されました。
経過: 00:00:02.98
実行計画
----------------------------------------------------------
Plan hash value: 1065177875
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3692 | 118K| 5787 (2)| 00:01:10 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 17550 | 411K| 5772 (2)| 00:01:10 | | | Q1,01 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6626_4A53712A | | | | | | | Q1,01 | PCWP | |
| 5 | HASH GROUP BY | | 17550 | 411K| 5772 (2)| 00:01:10 | | | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 17550 | 411K| 5772 (2)| 00:01:10 | | | Q1,01 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 17550 | 411K| 5772 (2)| 00:01:10 | | | Q1,00 | P->P | HASH |
| 8 | HASH GROUP BY | | 17550 | 411K| 5772 (2)| 00:01:10 | | | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 394K| 9242K| 5770 (2)| 00:01:10 | 1 | 4 | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL | TEST2 | 394K| 9242K| 5770 (2)| 00:01:10 | 1 | 4 | Q1,00 | PCWP | |
| 11 | PX COORDINATOR | | | | | | | | | | |
| 12 | PX SEND QC (RANDOM) | :TQ20000 | 17550 | 497K| 2 (0)| 00:00:01 | | | Q2,00 | P->S | QC (RAND) |
| 13 | LOAD AS SELECT | SYS_TEMP_0FD9D6627_4A53712A | | | | | | | Q2,00 | PCWP | |
|* 14 | VIEW | | 17550 | 497K| 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 17550 | 411K| 2 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6626_4A53712A | 17550 | 411K| 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 17 | PX COORDINATOR | | | | | | | | | | |
| 18 | PX SEND QC (ORDER) | :TQ30005 | 3692 | 118K| 13 (39)| 00:00:01 | | | Q3,05 | P->S | QC (ORDER) |
| 19 | SORT ORDER BY | | 3692 | 118K| 13 (39)| 00:00:01 | | | Q3,05 | PCWP | |
| 20 | PX RECEIVE | | 3692 | 118K| 12 (34)| 00:00:01 | | | Q3,05 | PCWP | |
| 21 | PX SEND RANGE | :TQ30004 | 3692 | 118K| 12 (34)| 00:00:01 | | | Q3,04 | P->P | RANGE |
| 22 | BUFFER SORT | | 3692 | 118K| | | | | Q3,04 | PCWP | |
| 23 | VIEW | | 3692 | 118K| 12 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 24 | UNION-ALL | | | | | | | | Q3,04 | PCWP | |
| 25 | HASH GROUP BY | | 2758 | 68950 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 26 | PX RECEIVE | | 2758 | 68950 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 27 | PX SEND HASH | :TQ30000 | 2758 | 68950 | 3 (34)| 00:00:01 | | | Q3,00 | P->P | HASH |
| 28 | HASH GROUP BY | | 2758 | 68950 | 3 (34)| 00:00:01 | | | Q3,00 | PCWP | |
| 29 | VIEW | | 17550 | 428K| 2 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 30 | PX BLOCK ITERATOR | | 17550 | 497K| 2 (0)| 00:00:01 | | | Q3,00 | PCWC | |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6627_4A53712A | 17550 | 497K| 2 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 32 | HASH GROUP BY | | 920 | 20240 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 33 | PX RECEIVE | | 920 | 20240 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 34 | PX SEND HASH | :TQ30001 | 920 | 20240 | 3 (34)| 00:00:01 | | | Q3,01 | P->P | HASH |
| 35 | HASH GROUP BY | | 920 | 20240 | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 36 | VIEW | | 17550 | 377K| 2 (0)| 00:00:01 | | | Q3,01 | PCWP | |
| 37 | PX BLOCK ITERATOR | | 17550 | 497K| 2 (0)| 00:00:01 | | | Q3,01 | PCWC | |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6627_4A53712A | 17550 | 497K| 2 (0)| 00:00:01 | | | Q3,01 | PCWP | |
| 39 | HASH GROUP BY | | 13 | 260 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 40 | PX RECEIVE | | 13 | 260 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 41 | PX SEND HASH | :TQ30002 | 13 | 260 | 3 (34)| 00:00:01 | | | Q3,02 | P->P | HASH |
| 42 | HASH GROUP BY | | 13 | 260 | 3 (34)| 00:00:01 | | | Q3,02 | PCWP | |
| 43 | VIEW | | 17550 | 342K| 2 (0)| 00:00:01 | | | Q3,02 | PCWP | |
| 44 | PX BLOCK ITERATOR | | 17550 | 411K| 2 (0)| 00:00:01 | | | Q3,02 | PCWC | |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6626_4A53712A | 17550 | 411K| 2 (0)| 00:00:01 | | | Q3,02 | PCWP | |
| 46 | HASH GROUP BY | | 1 | 17 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 47 | PX RECEIVE | | 1 | 17 | 3 (34)| 00:00:01 | | | Q3,04 | PCWP | |
| 48 | PX SEND HASH | :TQ30003 | 1 | 17 | 3 (34)| 00:00:01 | | | Q3,03 | P->P | HASH |
| 49 | HASH GROUP BY | | 1 | 17 | 3 (34)| 00:00:01 | | | Q3,03 | PCWP | |
| 50 | VIEW | | 17550 | 291K| 2 (0)| 00:00:01 | | | Q3,03 | PCWP | |
| 51 | PX BLOCK ITERATOR | | 17550 | 411K| 2 (0)| 00:00:01 | | | Q3,03 | PCWC | |
| 52 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6626_4A53712A | 17550 | 411K| 2 (0)| 00:00:01 | | | Q3,03 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101' AND
"STARTING_DATE">='20110101')
14 - filter("MONTH">='201101' AND "MONTH"<='201103' AND "SHOP_CODE"='1000')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 16
統計
----------------------------------------------------------
348 recursive calls
5435 db block gets
318993 consistent gets
309118 physical reads
310176 redo size
875 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
33 sorts (memory)
0 sorts (disk)
8 rows processed
04:31:33 SCOTT>
パラレルクエリの話は一旦横に置いといて（月単位のレンジパーティションならもっと良い結果がでそうですがそれはまた別途ということで…
WITH句以外にもGROUPING SETも効果がありそうではあります。次回は、それらを試して結果を比較してみようかと…。
つづく。
これまでのあらずじ…
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #1
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #2
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #3
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #4
| 固定リンク | 0
コメント