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

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

|

トラックバック

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

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

コメント

コメントを書く