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

2011年8月 2日 (火)

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

前回、思いつきでGROUPING SETSに書き換えたら予想外に結果が悪かったわけですが、よーく考えたら事前にもうちょっとサマってしまえばいけるんじゃないかと気づいて少々書き換えてみました、
悲惨だった前回の結果に比べたら随分改善しましたが、それでも元に戻った程度ですね。(^^;;;;;;

やはり、いくない。

以下、前回、一番処理時間が長くなったケース。単純にGROUPING SETSに書き換えた場合、元のクエリより遅い。アクセスブロック数は3/4程度なんですがね。処理時間がねぇ〜

 1  SELECT
2 CASE
3 WHEN quarter IS NULL THEN month
4 ELSE quarter
5 END AS month
6 ,CASE
7 WHEN grouping_id = 1 THEN 'ALL'
8 ELSE shop_code
9 END AS shop_code
10 ,sales_figure
11 FROM (
12 SELECT
13 grouping_id(shop_code) as grouping_id
14 ,quarter
15 ,month
16 ,shop_code
17 ,SUM(sales_figure) AS sales_figure
18 FROM
19 (
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 quarter
27 ,SUBSTR(starting_date,1,6) AS month
28 ,shop_code
29 ,sales_figure
30 FROM
31 test2
32 WHERE
33 SUBSTR(starting_date,1,6) BETWEEN '201101' AND '201103'
34 )
35 GROUP BY GROUPING SETS (
36 (month, shop_code),
37 (quarter, shop_code),
38 (month),
39 (quarter)
40 )
41 )
42 WHERE
43 shop_code = '1000'
44 OR grouping_id = 1
45 ORDER BY
46 month
47* ,shop_code

8行が選択されました。

経過: 00:00:34.88

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

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 711K| 33M| | 93048 (3)| 00:18:37 | | |
| 1 | SORT ORDER BY | | 711K| 33M| 43M| 93048 (3)| 00:18:37 | | |
| 2 | VIEW | | 711K| 33M| | 84288 (3)| 00:16:52 | | |
| 3 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6636_4A53D793 | | | | | | | |
| 5 | PARTITION HASH ALL | | 15M| 351M| | 84280 (3)| 00:16:52 | 1 | 4 |
|* 6 | TABLE ACCESS FULL | TEST2 | 15M| 351M| | 84280 (3)| 00:16:52 | 1 | 4 |
| 7 | LOAD AS SELECT | SYS_TEMP_0FD9D6637_4A53D793 | | | | | | | |
| 8 | SORT GROUP BY ROLLUP | | 1 | 33 | | 3 (34)| 00:00:01 | | |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6636_4A53D793 | 1 | 33 | | 2 (0)| 00:00:01 | | |
| 10 | LOAD AS SELECT | SYS_TEMP_0FD9D6637_4A53D793 | | | | | | | |
| 11 | SORT GROUP BY ROLLUP | | 1 | 23 | | 3 (34)| 00:00:01 | | |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6636_4A53D793 | 1 | 23 | | 2 (0)| 00:00:01 | | |
| 13 | VIEW | | 1 | 50 | | 2 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6637_4A53D793 | 1 | 50 | | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------

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

6 - filter(SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101')
14 - filter("SYS_TEMP_0FD9D6637_4A53D793"."C1"='1000' OR
BIN_TO_NUM(SYS_OP_VECBIT(SYS_OP_NUMTORAW("SYS_TEMP_0FD9D6637_4A53D793"."D0"),1))=1)


統計
----------------------------------------------------------
761 recursive calls
52432 db block gets
405879 consistent gets
405750 physical reads
1944 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

意外な結果になってしまったので、WITH句は利用していないのですが今回試したのは事前にある程度サマったケース。
コードサイズも一番小さくて可読性もよいと思うんだけどね〜。
アクセスしているブロック数は1/2でいい感じなのですが、処理時間は今回ネタにしたクエリ(後述)より少々良い程度。ブロック数は半減したのに処理時間がさほど改善していないのはCPUへの負荷が考えていた以上に高いのでしょうかね。(それはまた別途しらべておきますか)

  1  SELECT
2 CASE
3 WHEN quarter IS NULL THEN month
4 ELSE quarter
5 END AS month
6 ,CASE
7 WHEN grouping_id(shop_code) = 1 THEN 'ALL'
8 ELSE shop_code
9 END AS shop_code
10 ,SUM(sales_figure) AS sales_figure
11 FROM
12 (
13 SELECT
14 CASE
15 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
16 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
17 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
18 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
19 END AS quarter
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 CASE
29 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
30 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
31 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
32 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
33 END
34 ,SUBSTR(starting_date,1,6)
35 ,shop_code
36 )
37 GROUP BY GROUPING SETS (
38 (month, shop_code),
39 (quarter, shop_code),
40 (month),
41 (quarter)
42 )
43 HAVING
44 shop_code = '1000'
45 OR grouping_id(shop_code) = 1
46 ORDER BY
47 month
48* ,shop_code

8行が選択されました。

経過: 00:00:19.69

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

--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2758 | 134K| 83181 (2)| 00:16:39 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D66D1_4A54AE09 | | | | | | |
| 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_0FD9D66D2_4A54AE09 | | | | | | |
| 7 | SORT GROUP BY ROLLUP | | 1 | 33 | 3 (34)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66D1_4A54AE09 | 1 | 33 | 2 (0)| 00:00:01 | | |
| 9 | LOAD AS SELECT | SYS_TEMP_0FD9D66D2_4A54AE09 | | | | | | |
| 10 | SORT GROUP BY ROLLUP | | 1 | 23 | 3 (34)| 00:00:01 | | |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66D1_4A54AE09 | 1 | 23 | 2 (0)| 00:00:01 | | |
| 12 | SORT ORDER BY | | 2758 | 134K| 3 (34)| 00:00:01 | | |
| 13 | VIEW | | 1 | 50 | 2 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66D2_4A54AE09 | 1 | 50 | 2 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------

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

5 - filter("SYS_TBL_$2$"."STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101' AND "SYS_TBL_$2$"."STARTING_DATE">='20110101')
14 - filter("SYS_TEMP_0FD9D66D2_4A54AE09"."C1"='1000' OR
BIN_TO_NUM(SYS_OP_VECBIT(SYS_OP_NUMTORAW("SYS_TEMP_0FD9D66D2_4A54AE09"."D0"),1))=1)


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


前々回、WITH句とunion allだけでチューニングしたクエリと比べてみるとブロック数は同じ程度でもCPUコストの差は実行計画からもある程度読み取れますね(参考程度ですが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
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


ということで、おまけはおしまい、次回へつづく。(どーやってまとめんるんだこれw)

追記(2011/8/3)
次回は、今回チューニングしたGROUPING SETSを利用した文をさらにチューニングするというおまけのおまけ。(ほんと、どーやってまとめんだこれ!)


これまでのあらずじ…

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


|

トラックバック

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

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

コメント

コメントを書く