« 2011年6月 | トップページ | 2011年8月 »

2011年7月24日 (日)

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

2011/7/28追記
GROUPING SETSで異常に遅かったのは使い方わるいなーと m(_ _)m WITH句で一時表1つ使うのと同じぐらいにはなるかもね。と(次回書きます)

前回のつづきでーす。

前回のは、元々がイケテない、かつ、大人の事情でパーティションタイプ変更とか、マテリアライズドビューとか、パラレルクエリーもだーめー。と言いつつ、なんとか、できる限り早くしてくれーという、匂いの元を絶たないで別の匂いでごまかしてくれー的なことだったのですが、いろいろと考えた末、WITH句を使えばなんとかいい感じになりました。でも、元が元だけにほぼ限界ですね。

大人の事情で男らしくずばーっとパーティションタイプ変更することができないとか言ってた方々も、この結果をもって、交渉のテーブルにつけるんじゃないかな。検討祈る :)
この結果で納得してもらえるかは感知しませんからねw 最後の手としてパーティションタイプ変更を残しておいてもいいいと思いますし。;)

ということで、今回は、他の方法との比較編。

前回、grouping setsでもいけそーな感じがすると書いたのですが、grouping setsの場合裏の動きはOracleにお任せなので…

まあ、見てみましょう。


最初は、シンプルにgrouping setsに置き換えて見ました。(深く考えてないので余計な部分はまだまだありそうですがw )

実行計画と処理時間、アクセスブロック数みて少々意外な結果に驚いたんですよ。 

ダメダメだった元のクエリより処理時間は長くなってる><。 アクセスしているブロック数は随分少なくなっているんですが。CPUへの負荷が高いのか(詳細は別途調べるとして)とにかく前より遅くなっちゃNGです。ただ、クエリ自体は随分読みやすい :)

grouping setsで置き換えた今回のクエリ、実行計画を見るとWITH句で書き換えたときのようものにかなり似ていますが、WITH句で書き換えた場合と大きく異なるのは内部で一時表に置き換えられるタイミングにありそうですね。WITH句は本体のクエリとは別で、しかも一番先に実行されますが、以下の実行計画をみると違いますよね。WITH句で書き換えた実行計画と比較するとその違いがよくわかります。

  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

GROUPING SETSで内部的に行われる一時表作成の効率が今ひとつなのであれば、やはり明示的にWITH句で宣言してからやったほうがいいのかな〜ということで、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 SELECT
24 CASE
25 WHEN quarter IS NULL THEN month
26 ELSE quarter
27 END AS month
28 ,CASE
29 WHEN grouping_id = 1 THEN 'ALL'
30 ELSE shop_code
31 END AS shop_code
32 ,sales_figure
33 FROM (
34 SELECT
35 grouping_id(shop_code) as grouping_id
36 ,quarter
37 ,month
38 ,shop_code
39 ,SUM(sales_figure) AS sales_figure
40 FROM
41 t01
42 GROUP BY GROUPING SETS (
43 (month, shop_code),
44 (quarter, shop_code),
45 (month),
46 (quarter)
47 )
48 )
49 WHERE
50 shop_code = '1000'
51 OR grouping_id = 1
52 ORDER BY
53 month
54* ,shop_code

8行が選択されました。

経過: 00:00:11.83

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

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2758 | 134K| 83181 (2)| 00:16:39 | | |
| 1 | SORT ORDER BY | | 2758 | 134K| 83181 (2)| 00:16:39 | | |
| 2 | VIEW | | 2758 | 134K| 83180 (2)| 00:16:39 | | |
| 3 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6650_4A53D793 | | | | | | |
| 5 | HASH GROUP BY | | 17550 | 411K| 83172 (2)| 00:16:39 | | |
| 6 | PARTITION HASH ALL | | 394K| 9242K| 83161 (2)| 00:16:38 | 1 | 4 |
|* 7 | TABLE ACCESS FULL | TEST2 | 394K| 9242K| 83161 (2)| 00:16:38 | 1 | 4 |
| 8 | LOAD AS SELECT | SYS_TEMP_0FD9D6651_4A53D793 | | | | | | |
| 9 | SORT GROUP BY ROLLUP | | 1 | 33 | 3 (34)| 00:00:01 | | |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6650_4A53D793 | 1 | 33 | 2 (0)| 00:00:01 | | |
| 11 | LOAD AS SELECT | SYS_TEMP_0FD9D6651_4A53D793 | | | | | | |
| 12 | SORT GROUP BY ROLLUP | | 1 | 23 | 3 (34)| 00:00:01 | | |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6650_4A53D793 | 1 | 23 | 2 (0)| 00:00:01 | | |
| 14 | VIEW | | 1 | 50 | 2 (0)| 00:00:01 | | |
|* 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6651_4A53D793 | 1 | 50 | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------

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

7 - 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')
15 - filter("SYS_TEMP_0FD9D66B3_4A54062A"."C1"='1000' OR
BIN_TO_NUM(SYS_OP_VECBIT(SYS_OP_NUMTORAW("SYS_TEMP_0FD9D66B3_4A54062A"."D0"),1))=1)


統計
----------------------------------------------------------
358 recursive calls
53 db block gets
302715 consistent gets
302589 physical reads
1712 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句だけで行ったチューニング同様に2つの一時表を作ることにしましょー :)
WITH句で2つの一時表を準備(本体の表へは1度だけアクセス)、かつ、grouping sets を使いunion allの利用は最小限にしてみました。w

読みやすくなったし、速度的にも WITH句で2つの一時表を使う場合と変らないし、GROUPING SETSだけだと今回のような問題は解決しづらいかもしれませんね、今のところ。
可読性の向上にはかなり効果あるとおもいます、GROUPING SETSって。


  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 CASE
38 WHEN quarter IS NULL THEN month
39 ELSE quarter
40 END AS month
41 ,shop_code
42 ,sales_figure
43 FROM (
44 SELECT
45 quarter
46 ,month
47 ,shop_code
48 ,SUM(sales_figure) AS sales_figure
49 FROM
50 t02
51 GROUP BY GROUPING SETS (
52 (month, shop_code),
53 (quarter, shop_code)
54 )
55 UNION ALL
56 SELECT
57 quarter
58 ,month
59 ,'ALL' AS shop_code
60 ,SUM(sales_figure) AS sales_figure
61 FROM
62 t01
63 GROUP BY GROUPING SETS (
64 (month),
65 (quarter)
66 )
67 )
68 ORDER BY
69 month
70* ,shop_code

8行が選択されました。

経過: 00:00:08.34

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

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 595 | 83223 (2)| 00:16:39 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D667D_4A53D793 | | | | | | |
| 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 | SORT ORDER BY | | 17 | 595 | 51 (10)| 00:00:01 | | |
| 7 | VIEW | | 17 | 595 | 50 (8)| 00:00:01 | | |
| 8 | UNION-ALL | | | | | | | |
| 9 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 10 | LOAD AS SELECT | SYS_TEMP_0FD9D667E_4A53D793 | | | | | | |
|* 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667D_4A53D793 | 1 | 24 | 17 (0)| 00:00:01 | | |
| 12 | LOAD AS SELECT | SYS_TEMP_0FD9D667F_4A53D793 | | | | | | |
| 13 | HASH GROUP BY | | 1 | 33 | 3 (34)| 00:00:01 | | |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667E_4A53D793 | 1 | 33 | 2 (0)| 00:00:01 | | |
| 15 | LOAD AS SELECT | SYS_TEMP_0FD9D667F_4A53D793 | | | | | | |
| 16 | HASH GROUP BY | | 1 | 23 | 3 (34)| 00:00:01 | | |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667E_4A53D793 | 1 | 23 | 2 (0)| 00:00:01 | | |
| 18 | VIEW | | 1 | 37 | 2 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667F_4A53D793 | 1 | 37 | 2 (0)| 00:00:01 | | |
| 20 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 21 | LOAD AS SELECT | SYS_TEMP_0FD9D6680_4A53D793 | | | | | | |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667D_4A53D793 | 17550 | 411K| 17 (0)| 00:00:01 | | |
| 23 | LOAD AS SELECT | SYS_TEMP_0FD9D6681_4A53D793 | | | | | | |
| 24 | HASH GROUP BY | | 1 | 27 | 3 (34)| 00:00:01 | | |
| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6680_4A53D793 | 1 | 27 | 2 (0)| 00:00:01 | | |
| 26 | LOAD AS SELECT | SYS_TEMP_0FD9D6681_4A53D793 | | | | | | |
| 27 | HASH GROUP BY | | 1 | 17 | 3 (34)| 00:00:01 | | |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6680_4A53D793 | 1 | 17 | 2 (0)| 00:00:01 | | |
| 29 | VIEW | | 1 | 31 | 2 (0)| 00:00:01 | | |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6681_4A53D793 | 1 | 31 | 2 (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')
11 - filter("SYS_TBL_$2$"."C0">='201101' AND "SYS_TBL_$2$"."C0"<='201103' AND "SYS_TBL_$2$"."C2"='1000')


統計
----------------------------------------------------------
790 recursive calls
82 db block gets
302843 consistent gets
302593 physical reads
3964 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
1 sorts (memory)
0 sorts (disk)
8 rows processed

次回は、月毎のレンジパーティションで同じ対処をしたらとこまで改善するのか確認しておきましょう。




これまでのあらずじ…

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

| | コメント (0) | トラックバック (0)

2011年7月22日 (金)

Lionがでたとおもったら、VirtualBox 4.1も出てたのね。

http://www.virtualbox.org/wiki/Changelog

20110722_13624

Lion入れるより先にVirtualBox 4.1にしてみたw

| | コメント (0) | トラックバック (0)

2011年7月19日 (火)

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

前回のつづきです。

さて、前回登場したボスキャラな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>

見えてきましたよね、無駄が。

このクエリ、同じ検索範囲のデータを対象に4回もTEST2表にアクセスしています。全表走査2回、改善したとはいえ2回の全ローカル索引のレンジスキャンを行っています。無駄なアクセスが多すぎます。
こんなタイプのUNION ALLやUNION繰り返しタイプのクエリを改善できる方法は……

まず浮かんだのは、WITH句を使って各クエリの共通部分をまとめてしまう方法です。

WITH句に記述したクエリは最初に1度実行され結果を一時セグメントに溜め込みます。その後、一時セグメントに溜め込んだ結果を使い回せばいいので巨大な表本体を何度も参照する必要がなくなります。
そうそう、うまく使えば、都度使い捨てのマテリアライズドビュー(例えが良くないですが気にしないでね)みたいな使い方ができます。


書き換えのポイントは一番大きな範囲で集計する共通部分を見つけ出して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>

アクセスしているブロック数は約1/2に減少し処理時間も20秒台から8秒台へ改善することができました。バンザーイ!w

ついでなので、オラクル任せのパラレルクエリだとどうなるか試してみました。(CPUたくさんあるんでw)
アクセスブロック数は少々増えますが、パラレルの良さが出ています。2秒台になりましたー。しかしー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) | トラックバック (0)

2011年7月18日 (月)

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

前回のつづきです。

このクエリ、前回、大人の事情縛りでチューニング対象だったクエリがアクセスしている表と同じ表を、またまた残念な方法でアクセスしています。ボスキャラ登場って感じですw

いくら最近のオプティマイザが賢くても、勿体ないお化けを絵に描いたようなクエリじゃ最適化なんてできねっす。しかもパーティションタイプは前回の通り、starting_date列でハッシュパーティションorz.
かつ、検索範囲は、全店舗(1300店)、全品目(140品目)の365日分の3ヶ月分、つまり、全データの1/4を検索するサブクエリが2つ、索引をうまく使えばよさげなタイプのサブクエリが2つunion allで統合されています。

前回のクエリのようにファンクション索引でなんとかできるような相手ではありません。しかも読みにくい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.24

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

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 143 | 4719 | 205K (2)| 00:41:05 | | |
| 1 | SORT ORDER BY | | 143 | 4719 | 205K (2)| 00:41:05 | | |
| 2 | VIEW | | 143 | 4719 | 205K (2)| 00:41:05 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | HASH GROUP BY | | 3 | 72 | 19054 (1)| 00:03:49 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST2 | 2675 | 64200 | 19053 (1)| 00:03:49 | ROWID | ROWID |
|* 6 | INDEX SKIP SCAN | PK_TEST2 | 11266 | | 11197 (1)| 00:02:15 | | |
| 7 | HASH GROUP BY | | 57 | 969 | 19054 (1)| 00:03:49 | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST2 | 2675 | 45475 | 19053 (1)| 00:03:49 | ROWID | ROWID |
|* 9 | INDEX SKIP SCAN | PK_TEST2 | 11266 | | 11197 (1)| 00:02:15 | | |
| 10 | HASH GROUP BY | | 3 | 57 | 83623 (2)| 00:16:44 | | |
| 11 | PARTITION HASH ALL | | 3477K| 63M| 83515 (2)| 00:16:43 | 1 | 4 |
|* 12 | TABLE ACCESS FULL | TEST2 | 3477K| 63M| 83515 (2)| 00:16:43 | 1 | 4 |
| 13 | HASH GROUP BY | | 80 | 960 | 83623 (2)| 00:16:44 | | |
| 14 | PARTITION HASH ALL | | 3477K| 39M| 83515 (2)| 00:16:43 | 1 | 4 |
|* 15 | TABLE ACCESS FULL | TEST2 | 3477K| 39M| 83515 (2)| 00:16:43 | 1 | 4 |
-------------------------------------------------------------------------------------------------------------------

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

6 - access("STARTING_DATE">='20110101' AND "SHOP_CODE"='1000' AND "STARTING_DATE"<='20110331')
filter("SHOP_CODE"='1000' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101')
9 - access("STARTING_DATE">='20110101' AND "SHOP_CODE"='1000' AND "STARTING_DATE"<='20110331')
filter("SHOP_CODE"='1000' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101')
12 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')
15 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
632952 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


12:51:25 SCOTT>

この結果をみたら残念な感じが確実になりましたよね.

まあ、あせらずじっくり、料理してみましょう。

PK_TEST2は主キー索引ですが、列の順番がイケてません。SHOP_CODE,ITEM_CODE,STARTING_DATEって変えたほうが良さげ

12:50:03 SCOTT> 
12:47:30 SCOTT> select index_name,column_name from user_ind_columns where table_name like '%TEST2%' order by index_name,column_position

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
IX1_TEST2 SYS_NC00005$

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
PK_TEST2 STARTING_DATE
ITEM_CODE
SHOP_CODE

12:24:03 SCOTT> select segment_name,partition_name,blocks from user_segments where segment_name like '%TEST2%'

SEGMENT_NAME PARTITION_NAME BLOCKS
------------------------------ ------------------------------ ----------
IX1_TEST2 TEST2IX11 37888
IX1_TEST2 TEST2IX12 45056
IX1_TEST2 TEST2IX13 41984
IX1_TEST2 TEST2IX14 46080
PK_TEST2 368640
TEST2 TEST201 66560
TEST2 TEST202 80896
TEST2 TEST203 74752
TEST2 TEST204 82944

9行が選択されました。

経過: 00:00:00.01

主キーをローカル索引かつ列順を変えて作り直し。
(検索パターンも考慮しましたが影響は少なかったので、カーディナリティの高い順というB*Tree索引設計の基本みたいな順序で作り直してます。。)

第一回目の冒頭でも書きましたが、shot_code(1300店)、starting_date(365日)、item_code(140品目)ってことなので (nologgingなのは気にしないでねー)

13:36:12 SCOTT> alter table test2 add constraint pk_test2 primary key (shop_code,starting_date,item_code) using index local
13:38:07 2 (
13:38:08 3 partition pk_test2_1 tablespace tsi001,
13:38:36 4 partition pk_test2_2 tablespace tsi002,
13:38:45 5 partition pk_test2_3 tablespace tsi003,
13:38:55 6 partition pk_test2_4 tablespace tsi004
13:39:06 7 ) nologging;

表が変更されました。

できあがあったセグメントサイズを見るとグローバル索引かつ、starting_date,item_code,shop_codeって並びの時より40,000ブロックですが、索引サイズは縮小しています。

13:53:29 SCOTT> select segment_name,partition_name,blocks from user_segments where segment_name like '%TEST2%'

SEGMENT_NAME PARTITION_NAME BLOCKS
------------------------------ ------------------------------ ----------
IX1_TEST2 TEST2IX11 37888
IX1_TEST2 TEST2IX12 45056
IX1_TEST2 TEST2IX13 41984
IX1_TEST2 TEST2IX14 6080
PK_TEST2 PK_TEST2_1 71680
PK_TEST2 PK_TEST2_2 87040
PK_TEST2 PK_TEST2_3 79872
PK_TEST2 PK_TEST2_4 89088
TEST2 TEST201 66560
TEST2 TEST202 80896
TEST2 TEST203 74752
TEST2 TEST204 82944

12行が選択されました。


主キーを作り直しただけですが、同じクエリの結果が変わるか確認してみます。索引の調整で約30,000ブロック程、節約できましたが処理時間、総アクセスブロック数とも、あまり改善していません。
(30,000ブロック程節約できたわけですが〜もとが大きいのでこの程度では焼け石に水状態です><)

経過: 00:00:21.02

実行計画
----------------------------------------------------------
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')


統計
----------------------------------------------------------
1 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

15:31:33 SCOTT>

ちなみに同じ対策を月単位のレンジパーティション表で行うとどうなるんでしょ。
比較のために確認しておきます。(大人の事情でパーティションタイプの変更は不可なのですが、悔しいので比較データだけは見せつけておきたいとwww)

まずは、主キーの並びはstarting_date,item_code,shop_codeという順の複合主キーかつローカル索引で作成した場合です。(オリジナルと同じ列順の複合主キーの場合)

14:30:16 SCOTT> select index_name,column_name from user_ind_columns where table_name like '%TEST3' order by index_name,column_position;

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
PK_TEST3 STARTING_DATE
ITEM_CODE
SHOP_CODE

以下のクエリ、表が月単位のレンジパーティションかつローカル索引の主キー制約ががあるという点だけが異なりますが、アクセスしているブロック数は1/3以下になります。
まったく同じクエリなのに、1/3のアクセスブロック数ですむんですよ〜〜〜〜〜〜〜。 ;)

  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 test3
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 test3
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 test3
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 test3
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:15.76

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

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Pstart|Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 280 | 9240 | 82645 (3)| 00:16:32 | | |
| 1 | SORT ORDER BY | | 280 | 9240 | 82645 (3)| 00:16:32 | | |
| 2 | VIEW | | 280 | 9240 | 82644 (3)| 00:16:32 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | HASH GROUP BY | | 58 | 986 | 20192 (1)| 00:04:03 | | |
| 5 | PARTITION RANGE ITERATOR | | 11467 | 190K| 20191 (1)| 00:04:03 | 1 | 3 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST3 | 11467 | 190K| 20191 (1)| 00:04:03 | 1 | 3 |
|* 7 | INDEX SKIP SCAN | PK_TEST3 | 2783 | | 11867 (1)| 00:02:23 | 1 | 3 |
| 8 | HASH GROUP BY | | 58 | 986 | 20192 (1)| 00:04:03 | | |
| 9 | PARTITION RANGE ITERATOR | | 11467 | 190K| 20191 (1)| 00:04:03 | 1 | 3 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST3 | 11467 | 190K| 20191 (1)| 00:04:03 | 1 | 3 |
|* 11 | INDEX SKIP SCAN | PK_TEST3 | 2783 | | 11867 (1)| 00:02:23 | 1 | 3 |
| 12 | HASH GROUP BY | | 82 | 984 | 21130 (4)| 00:04:14 | | |
| 13 | PARTITION RANGE ITERATOR | | 14M| 170M| 20627 (2)| 00:04:08 | 1 | 3 |
|* 14 | TABLE ACCESS FULL | TEST3 | 14M| 170M| 20627 (2)| 00:04:08 | 1 | 3 |
| 15 | HASH GROUP BY | | 82 | 984 | 21130 (4)| 00:04:14 | | |
| 16 | PARTITION RANGE ITERATOR | | 14M| 170M| 20627 (2)| 00:04:08 | 1 | 3 |
|* 17 | TABLE ACCESS FULL | TEST3 | 14M| 170M| 20627 (2)| 00:04:08 | 1 | 3 |
-------------------------------------------------------------------------------------------------------------------

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

7 - access("STARTING_DATE">='20110101' AND "SHOP_CODE"='1000' AND "STARTING_DATE"<='20110331')
filter("SHOP_CODE"='1000')
11 - access("STARTING_DATE">='20110101' AND "SHOP_CODE"='1000' AND "STARTING_DATE"<='20110331')
filter("SHOP_CODE"='1000')
14 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')
17 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
175722 consistent gets
101132 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

この月単位レンジパーティションの索引も列順を入れ替えて作り直してみます。consistent getsが索引を作り変えてRANGE SCANできるようにしただけで約25,000ブロック減りました。

経過: 00:00:15.56

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

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Pstart|Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 302 | 9966 | 44244 (4)| 00:08:51 | | |
| 1 | SORT ORDER BY | | 302 | 9966 | 44244 (4)| 00:08:51 | | |
| 2 | VIEW | | 302 | 9966 | 44243 (4)| 00:08:51 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | HASH GROUP BY | | 63 | 1071 | 951 (1)| 00:00:12 | | |
| 5 | PARTITION RANGE ITERATOR | | 12188 | 202K| 949 (0)| 00:00:12 | 1 | 3 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST3 | 12188 | 202K| 949 (0)| 00:00:12 | 1 | 3 |
|* 7 | INDEX RANGE SCAN | PK_TEST3 | 2958 | | 66 (0)| 00:00:01 | 1 | 3 |
| 8 | HASH GROUP BY | | 63 | 1071 | 951 (1)| 00:00:12 | | |
| 9 | PARTITION RANGE ITERATOR | | 12188 | 202K| 949 (0)| 00:00:12 | 1 | 3 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST3 | 12188 | 202K| 949 (0)| 00:00:12 | 1 | 3 |
|* 11 | INDEX RANGE SCAN | PK_TEST3 | 2958 | | 66 (0)| 00:00:01 | 1 | 3 |
| 12 | HASH GROUP BY | | 88 | 1056 | 21171 (4)| 00:04:15 | | |
| 13 | PARTITION RANGE ITERATOR | | 15M| 182M| 20630 (2)| 00:04:08 | 1 | 3 |
|* 14 | TABLE ACCESS FULL | TEST3 | 15M| 182M| 20630 (2)| 00:04:08 | 1 | 3 |
| 15 | HASH GROUP BY | | 88 | 1056 | 21171 (4)| 00:04:15 | | |
| 16 | PARTITION RANGE ITERATOR | | 15M| 182M| 20630 (2)| 00:04:08 | 1 | 3 |
|* 17 | TABLE ACCESS FULL | TEST3 | 15M| 182M| 20630 (2)| 00:04:08 | 1 | 3 |
-------------------------------------------------------------------------------------------------------------------

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

7 - access("SHOP_CODE"='1000' AND "STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110331')
11 - access("SHOP_CODE"='1000' AND "STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110331')
14 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')
17 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
149728 consistent gets
101132 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


パーティションタイプの選択ミスってほんとに痛い結果になりますよね。初期段階のというかパーティションタイプ毎の得意技は把握しておくべきですよー。


ということで次回本格的なチューニングへと、つづく…




これまでのあらずじ…

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

| | コメント (0) | トラックバック (0)

2011年7月17日 (日)

VirtualBox 4.0.12 リリース、活発だな〜 :)

気づいたら、4.0.12がリリースされてる。:) すげ〜活発な。  アップデートdone.

20110717_83523

| | コメント (0) | トラックバック (0)

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

いろいろと面倒くさい大人の事情が登場してきたので、パーティションタイプの変更案はひとまず却下されてしまいました。(パーティションタイプの変更のほかに索引構成表なんて案もあるにはあったんですが、パーティションタイプの変更が却下されたので表ごと索引にしちゃえばーみたいな案も却下されるのは当然かもねw)

ようするに、影響範囲を最小にしたい、でも、遅いクエリーはなんとかしたいーということなんですよー。めんどくさいです。まあ、アイデアが無いではないですが限界もみえちゃいますからねー。元が元だからw


ということで、代替案を試してみることに…

非ユニークなファクンクション索引を作り、SQL文のWHERE句の構文を換えます。SQL文を書き換えるためプログラムにも多少影響がでますが、影響範囲はかなり限定されるので、大人の事情でチューニングに手枷足枷を付けてくる人たちもOK〜って言ってくれるか、くれないか、ギリギリってところです。(月単位のレンジパーティションに比べれば索引で余計にディスク容量食いますし、ファンクション索引で更新性能にも多少影響でますがね。)

ローカル索引にしたのは多少でも索引サイズ少なくしたかったからw グローバル索引にしてもほぼ同じことはできますが。

00:55:51 SCOTT> create index ix1_test2 on test2(substr(starting_date,1,6)) local (
00:56:43 2 partition test2ix11 tablespace tsi001,
00:57:13 3 partition test2ix12 tablespace tsi002,
00:57:22 4 partition test2ix13 tablespace tsi003,
00:57:33 5 partition test2ix14 tablespace tsi004
00:57:44 6 );

索引が作成されました。

ファンクション索引を作成したら、SQL文を書き換えます。

変更前

SELECT 
SUBSTR(starting_date,1,6) AS month
,shop_code
,SUM(sales_figure) AS sales_figure
FROM
test2
WHERE
starting_date BETWEEN '20110101' AND '20110331'
GROUP BY
SUBSTR(starting_date,1,6)
,shop_code
ORDER BY 1,2

変更後
WHERE句をBETWEENの範囲指定から、新たに作成したファンクション索引で検索できるよう書き換えます:) これがポイントね。

SELECT 
SUBSTR(starting_date,1,6) AS month
,shop_code
,SUM(sales_figure) AS sales_figure
FROM
test2
WHERE
SUBSTR(starting_date,1,6) = '201101'
GROUP BY
SUBSTR(starting_date,1,6)
,shop_code
ORDER BY 1,2


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

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1300 | 28600 | 4688 (1)| 00:00:57 | | |
| 1 | SORT GROUP BY | | 1300 | 28600 | 4688 (1)| 00:00:57 | | |
| 2 | PARTITION HASH ALL | | 664K| 13M| 4669 (1)| 00:00:57 | 1 | 4 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST2 | 664K| 13M| 4669 (1)| 00:00:57 | 1 | 4 |
|* 4 | INDEX RANGE SCAN | IX1_TEST2 | 265K| | 3458 (1)| 00:00:42 | 1 | 4 |
-----------------------------------------------------------------------------------------------------------------

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

4 - access(SUBSTR("STARTING_DATE",1,6)='201101')


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
38550 consistent gets
0 physical reads
0 redo size
29487 bytes sent via SQL*Net to client
1465 bytes received via SQL*Net from client
88 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1300 rows processed

随分改善できました〜:) アクセスするブロック数は多めですが、処理時間は月単位のレンジパーティションの1パーティションをフルスキャンするのと同程度。それなりにニッコリ。

前述の方法以外に、マテリアライズドビューを利用してちょうどいい感じに集計済みの状態を作っておけばもっと早くできるんですけど…なぜそれをしないかも大人の事情縛りなのです。はいw

レンジパーティションでシングルパーティションの全表走査よりブロック数は多いですが…ひとまずこの問題はクリア。でも次のもイライラもんでした。


ということで次回へつづく。




これまでのあらずじ…

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

| | コメント (0) | トラックバック (0)

2011年7月10日 (日)

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

前回のつづきです。

非パーティション表で主キーあり、ハッシュパーティション表で主キーはグローバル索引、月単位のレンジパーティション表で主キーはローカル索引の3表を作成してデータを登録したところまででした。

では早速つづきを。

(条件をそろえるため、バッファキャッシュクリア後2回実行しています。結果は2回目のものを載せています)

今回、残念だった一番のポイント。 それは、データ量が多いのでパーティション表を選んだ、パーティションキーも悪くない、でも〜。選んだパーティションタイプが残念だったんです。

まずは、残念な結果を見てください。SQL文の性能がでない〜と問題になっていたところでは、test2表のように、ハッシュパーティションかつ一意検索向けにグローバル索引として主キーあったのですが、集計目的のクエリでは全く効果なく見るも無惨な結果に…

検索キーにパーティションキー(starting_date)は利用していますが、BETWEENによる範囲検索であるため全パーティションをFULL SCANしちゃってます。残念です。おしいw
(実は主キーはグローバル索引で、starting_date,item_code,shop_codeの複合キーとなっていますが……………索引も残念な感じになっているので、索引には目もくれずに全パーティションの全表走査になっています。ほんと残念)

  1  SELECT
2 SUBSTR(starting_date,1,6) AS month
3 ,shop_code
4 ,SUM(sales_figure) AS sales_figure
5 FROM
6 test2
7 WHERE
8 starting_date BETWEEN '20110101' AND '20110131'
9 GROUP BY
10 SUBSTR(starting_date,1,6)
11 ,shop_code
12* ORDER BY 1,2

1300行が選択されました。

経過: 00:00:06.67

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

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1839 | 31263 | 83128 (2)| 00:16:38 | | |
| 1 | SORT GROUP BY | | 1839 | 31263 | 83128 (2)| 00:16:38 | | |
| 2 | PARTITION HASH ALL| | 374K| 6220K| 83118 (2)| 00:16:38 | 1 | 4 |
|* 3 | TABLE ACCESS FULL| TEST2 | 374K| 6220K| 83118 (2)| 00:16:38 | 1 | 4 |
---------------------------------------------------------------------------------------------

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

3 - filter("STARTING_DATE"<='20110131' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
302587 consistent gets
302556 physical reads
0 redo size
29487 bytes sent via SQL*Net to client
1466 bytes received via SQL*Net from client
88 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1300 rows processed


この結果を見てすぐに浮かんだのは、月単位のパーティション表にしちゃうってことだったのですが、ここで大人の事情がじゃまをしますw


現状では、パーティションタイプの変更はむーりー。と。  ではどうするか。 他の案を提示しなければなりません…。

私が、考えている間、以下の例でもみて、どんな方法が残されているか………………考えてみてください:)

せっかくなので、非パーティション表のFULL SCANとどのくらい差がでる見てみます :) test1表は非パーティション表ですが、同じ複合主キーがあるのでやはり索引は利用せず全表走査になります。
若干アクセスしているブロック数が減っている程度の差ですが、ちょっとだけこちらのほうがいいですよねw でもパーティション化したメリットはほぼナッシングw

  1  SELECT
2 SUBSTR(starting_date,1,6) AS month
3 ,shop_code
4 ,SUM(sales_figure) AS sales_figure
5 FROM
6 test1
7 WHERE
8 starting_date BETWEEN '20110101' AND '20110131'
9 GROUP BY
10 SUBSTR(starting_date,1,6)
11 ,shop_code
12* ORDER BY 1,2

1300行が選択されました。

経過: 00:00:06.66

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

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1839 | 31263 | 82796 (2)| 00:16:34 |
| 1 | SORT GROUP BY | | 1839 | 31263 | 82796 (2)| 00:16:34 |
|* 2 | TABLE ACCESS FULL| TEST1 | 374K| 6220K| 82785 (2)| 00:16:34 |
----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STARTING_DATE"<='20110131' AND
"STARTING_DATE">='20110101')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
302081 consistent gets
302068 physical reads
0 redo size
29487 bytes sent via SQL*Net to client
1466 bytes received via SQL*Net from client
88 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1300 rows processed


月単位のレンジパーティションなら全表走査しても1パーティションで済むし、アクセスするブロック数も1/10程度に減少するのにね :)
初期段階のミスって、ほんとに痛いですよね〜 :)

  1  SELECT
2 SUBSTR(starting_date,1,6) AS month
3 ,shop_code
4 ,SUM(sales_figure) AS sales_figure
5 FROM
6 test3
7 WHERE
8 starting_date BETWEEN '20110101' AND '20110131'
9 GROUP BY
10 SUBSTR(starting_date,1,6)
11 ,shop_code
12* ORDER BY 1,2

1300行が選択されました。

経過: 00:00:03.25

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

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27578 | 457K| | 17785 (2)| 00:03:34 | | |
| 1 | SORT GROUP BY | | 27578 | 457K| 146M| 17785 (2)| 00:03:34 | | |
| 2 | PARTITION RANGE SINGLE| | 5460K| 88M| | 7004 (2)| 00:01:25 | 1 | 1 |
|* 3 | TABLE ACCESS FULL | TEST3 | 5460K| 88M| | 7004 (2)| 00:01:25 | 1 | 1 |
---------------------------------------------------------------------------------------------------------


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

3 - filter("STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110131')


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
24910 consistent gets
24901 physical reads
0 redo size
29487 bytes sent via SQL*Net to client
1466 bytes received via SQL*Net from client
88 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1300 rows processed

次回へつづく… とその前に、なぜ、索引を使ってくれなかったのでしょうか?

WHERE句の検索条件であるstarting_date列は、主キーの第一列ですよね…………


ちょっとだけ情報を見せておきましょう :)
おっとっと、索引の方が表本体より大きくなってるし〜w

SEGMENT_NAME		       SEGMENT_TYPE	  BLOCKS
------------------------------ ------------------ ----------
PK_TEST2 INDEX 368640
TEST2 TABLE PARTITION 305152

で、無理矢理索引を使わせてみると…w あららら……………なんかすげ〜ことになってますね〜10倍ぐらいアクセスブロック数増えてるし〜

15:33:04 SCOTT> r
1 SELECT /*+ index(test2 pk_test2) */
2 SUBSTR(starting_date,1,6) AS month
3 ,shop_code
4 ,SUM(sales_figure) AS sales_figure
5 FROM
6 test2
7 WHERE
8 starting_date BETWEEN '20110101' AND '20110131'
9 GROUP BY
10 SUBSTR(starting_date,1,6)
11 ,shop_code
12* ORDER BY 1,2

1300行が選択されました。

経過: 00:00:09.20

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

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1839 | 31263 | 266K (1)| 00:53:19 | | |
| 1 | SORT GROUP BY | | 1839 | 31263 | 266K (1)| 00:53:19 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST2 | 374K| 6220K| 266K (1)| 00:53:19 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | PK_TEST2 | 374K| | 2043 (1)| 00:00:25 | | |
----------------------------------------------------------------------------------------------------------------

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

3 - access("STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110131')


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
3883208 consistent gets
0 physical reads
0 redo size
29487 bytes sent via SQL*Net to client
1465 bytes received via SQL*Net from client
88 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1300 rows processed
15:33:32 SCOTT>

ということで次回、大人の事情でパーティショニングタイプの変更はできないので、他の案をへつづく…。

それにしても、すげ〜ブロック数だw


これまでのあらずじ…

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

| | コメント (0) | トラックバック (0)

2011年7月 6日 (水)

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

どこかで聞いたことありそうなタイトルを少々パクリつつ、まずは準備運動から…

ということで、今回は、そのパーティショニングタイプ選択ミスってないか? 
ってところから始めていくつかイケてなかったクエリを、パーティショニングタイプの選択ミスはミスだが、どーしても今は変更できな〜い。
という大人の事情(どんな大人だw) に金縛りな状況でSQLチューニングしてみましょ。というネタ :)

以下のような3表を作成してあります。 (nologgingなのは気にしないでね)

列定義は同じですが、非パーティション表で主キーあり、ハッシュパーティション表で主キーはグローバル索引、月単位のレンジパーティション表で主キーはローカル索引の3表。
各店舗(1300店)での売上げを品目(140品目)毎、かつ日毎に集計したデータが1年分(365日分)登録されていると思ってください。
(思わなくてもいいですけど、登録してありますw)

create table test1
(
starting_date char(8) not null,
shop_code char(4) not null,
sales_figure number not null,
item_code char(10) not null,
constraint pk_test1 primary key (starting_date,item_code,shop_code) using index tablespace tsimax nologging
)
tablespace tsmax
nologging
/

create table test2
(
starting_date char(8) not null,
shop_code char(4) not null,
sales_figure number not null,
item_code char(10) not null,
constraint pk_test2 primary key (starting_date,item_code,shop_code) using index global tablespace tsimax nologging
)
partition by hash(starting_date)
(
partition test201 tablespace ts001,
partition test202 tablespace ts002,
partition test203 tablespace ts003,
partition test204 tablespace ts004
)
nologging
/

create table test3
(
starting_date char(8) not null,
shop_code char(4) not null,
sales_figure number not null,
item_code char(10) not null,
constraint pk_test3 primary key (starting_date,item_code,shop_code) using index local
(
partition test301idx tablespace tsi001,
partition test302idx tablespace tsi002,
partition test303idx tablespace tsi003,
partition test304idx tablespace tsi004,
partition test305idx tablespace tsi001,
partition test306idx tablespace tsi002,
partition test307idx tablespace tsi003,
partition test308idx tablespace tsi004,
partition test309idx tablespace tsi001,
partition test310idx tablespace tsi002,
partition test311idx tablespace tsi003,
partition test312idx tablespace tsi004,
partition testmaxidx tablespace tsimax
)
nologging
)
partition by range(starting_date) (
partition test301 values less than ('20110201') tablespace ts001,
partition test302 values less than ('20110301') tablespace ts002,
partition test303 values less than ('20110401') tablespace ts003,
partition test304 values less than ('20110501') tablespace ts004,
partition test305 values less than ('20110601') tablespace ts001,
partition test306 values less than ('20110701') tablespace ts002,
partition test307 values less than ('20110801') tablespace ts003,
partition test308 values less than ('20110901') tablespace ts004,
partition test309 values less than ('20111001') tablespace ts001,
partition test310 values less than ('20111101') tablespace ts002,
partition test311 values less than ('20111201') tablespace ts003,
partition test312 values less than ('20120101') tablespace ts004,
partition testmax values less than (maxvalue) tablespace tsmax
)
nologging
/


次回へつづく。

| | コメント (0) | トラックバック (0)

2011年7月 3日 (日)

Command-Period

Mac OS X keyboard shortcuts - http://support.apple.com/kb/ht1343

みてて思ったんだけど、脊髄反応で、⌘. タイプするMacユーザってどれだけいるんだろー、というより残っているんだろーなんて、ぼーっと思ってた :)

4


モーダルダイアログで、 「⌘.」表示しているアプリケーションとかほぼ絶滅危惧種なのか?、

SafariやChrome、Firefoxの「表示メニュー」には中止「⌘.」が用意されているけど、Operaには無いとか。MacOS Xで使うブラウザだけ見てると、絶滅危惧するほどでもないか…なんて思っているけど、

脊髄反応で「⌘.」タイプしても受付ないのあるしな〜どうなんだろ。「⌘.」が効くアプリケーションだと、あ、これに関わっている人達の仲にはMac歴長そーな人がいるよねーきっととか思ったりして :)。

| | コメント (0) | トラックバック (0)