« いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #2 | トップページ | VirtualBox 4.0.12 リリース、活発だな〜 :) »

2011年7月17日 (日)

いろいろと面倒くさい大人の事情縛りの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

|

トラックバック

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

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

コメント

コメントを書く