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

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

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

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

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

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

---

これまでのあらずじ…

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