« 悩ませ過ぎは及ばざるがごとし #5 | トップページ | 悩ませ過ぎは及ばざるがごとし #7 - おまけ »

2011年9月 7日 (水)

悩ませ過ぎは及ばざるがごとし #6

前回のつづきです。

ヒントを使ってコストベースオプティマイザを悩ませないという方法で、40秒台っだった処理時間を1秒台にすることに成功しました。

これでも解決は解決なのですが、SQL文単体としては。ただ、インスタンスレベルて考えた場合無駄なメモリ使い過ぎだろという点が気がかり。コストベースオプティマイザが悩まなくなったことでCPUリソースの無駄遣いは回避できましたが、メモリリソースの無駄遣いが凄い。

ということで、どちらの無駄も減らす効果が期待できる案1を…

案1で書き換えるとヒントなしでも随分スッキリしちゃいます。
リテラル値が6000個近くありましたが、その部分を相関副問合せにしてあります。リテラル値の箇所はバインド変数化しちゃえばいいっすね。

SELECT
T1.surro_id
,T1.name
,T1.modified
FROM
test T1 JOIN (
SELECT DISTINCT
surro_id
,surro_bcd
FROM
test
) T2
ON
T1.surro_id = T2.surro_id AND
T1.surro_bcd = T2.surro_bcd
JOIN test T3
ON
T1.surro_id = T3.surro_id AND
T1.surro_acd= T3.surro_acd
WHERE
EXISTS (
SELECT
1
FROM
test_keys
WHERE
surro_id BETWEEN 1000000001 AND 1000005999
AND surro_id = T1.surro_id
)
/

実行してみると… (以下、SQL*Plusで set autot trace exp statコマンド叩いて実行した結果です。なお事前に共有プールはクリアしてあります。)

5999行が選択されました。

経過: 00:00:00.07

実行計画
----------------------------------------------------------
Plan hash value: 3724917145

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5997 | 468K| 144 (3)| 00:00:02 |
| 1 | VIEW | VM_NWVW_1 | 5997 | 468K| 144 (3)| 00:00:02 |
| 2 | HASH UNIQUE | | 5997 | 491K| 144 (3)| 00:00:02 |
|* 3 | HASH JOIN RIGHT SEMI | | 5997 | 491K| 143 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | PK_TEST_KEYS | 5999 | 41993 | 17 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 5997 | 450K| 125 (1)| 00:00:02 |
|* 6 | INDEX RANGE SCAN | IX01_TEST | 5999 | 128K| 20 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 5998 | 322K| 105 (1)| 00:00:02 |
|* 8 | INDEX RANGE SCAN | IX02_TEST | 5999 | 53991 | 19 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| TEST | 5999 | 269K| 85 (0)| 00:00:02 |
|* 10 | INDEX RANGE SCAN | IX02_TEST | 5999 | | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


SQL_ID        SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SQL_TEXT
------------- ------------ -------------- ----------- ------------------------------
52gaz3tjd90dh 43446 15648 14504 SELECT T1.surro_id, T1.name,
T1.modified FROM test T1 jo
in ( SELECT DISTINCT s
urro_id, surro_bcd FROM
test ) T2 ON T1.surro_id
= T2.surro_id AND T1.surro
_bcd = T2.surro_bcd JO
IN test T3 ON T1.sur
ro_id = T3.surro_id AND T1.s
urro_acd= T3.surro_acd WHERE
EXISTS ( SELECT 1 FROM
test_keys WHERE surro_
id BETWEEN 1000000001 AND 1000
005999 AND T1.surro_id = su
rro_id )

きゃほーーーーーーっ。 最高〜〜〜〜っ! :) 言うことナッシングw  

処理時間が、48.83秒 から 0.07秒へ、SHARABLE_MEMが、3318142バイト から 43446バイトに。 でバインド変数化しちゃえば〜〜〜、

同時に多数のセッションから集中して発行されても耐えられそうな〜

最後に、ドヤ顔(キリっ

ほんとうにヤバそうなところはキッチリ潰しておきましょ!

OLTP系で実行計画はよいけど、コストベースオプティマイザを悩ませ過ぎて遅延してたら本末転倒ですよー。と。

ーーーー完ーーーーー


ここまでのあらずじ

悩ませ過ぎは及ばざるがごとし #1
悩ませ過ぎは及ばざるがごとし #2
悩ませ過ぎは及ばざるがごとし #3
悩ませ過ぎは及ばざるがごとし #4
悩ませ過ぎは及ばざるがごとし #5

|

トラックバック

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

この記事へのトラックバック一覧です: 悩ませ過ぎは及ばざるがごとし #6:

コメント

コメントを書く