いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #4 Tweet
前回のつづきです。
このクエリ、前回、大人の事情縛りでチューニング対象だったクエリがアクセスしている表と同じ表を、またまた残念な方法でアクセスしています。ボスキャラ登場って感じですw
いくら最近のオプティマイザが賢くても、勿体ないお化けを絵に描いたようなクエリじゃ最適化なんてできねっす。しかもパーティションタイプは前回の通り、starting_date列でハッシュパーティションorz.
かつ、検索範囲は、全店舗（１３００店）、全品目（１４０品目）の３６５日分の３ヶ月分、つまり、全データの１/４を検索するサブクエリが２つ、索引をうまく使えばよさげなタイプのサブクエリが２つ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 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
コメント