いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #2 Tweet
前回のつづきです。
非パーティション表で主キーあり、ハッシュパーティション表で主キーはグローバル索引、月単位のレンジパーティション表で主キーはローカル索引の３表を作成してデータを登録したところまででした。
では早速つづきを。
（条件をそろえるため、バッファキャッシュクリア後２回実行しています。結果は２回目のものを載せています）
今回、残念だった一番のポイント。 それは、データ量が多いのでパーティション表を選んだ、パーティションキーも悪くない、でも〜。選んだパーティションタイプが残念だったんです。
まずは、残念な結果を見てください。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 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 あららら……………なんかすげ〜ことになってますね〜１０倍ぐらいアクセスブロック数増えてるし〜
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
これまでのあらずじ…
| 固定リンク | 0
コメント