« 悩ませ過ぎは及ばざるがごとし #6 | トップページ | オラクルは会議室で動いてんじゃな〜〜イ。統計情報で動いてんだ! »

2011年9月 8日 (木)

悩ませ過ぎは及ばざるがごとし #7 - おまけ

最近、おまけ付けるの好きなので:)

コストベースオプティマイザがどんだけ考えているか、チューニング前とチューニング後(案1と案2)でトレースファイルサイズで比較してみることに(本来それだけで比較できるものではないとは思いますが、あまりにも差があるのでw)


コストベースオプティマイザのトレースは次のようにして取得できますよね(詳細はググってね)

SCOTT> alter session set events='10053 trace name context forever, level 1';

セッションが変更されました。

経過: 00:00:00.00
SCOTT> SELECT …… 

・・・仲略・・・

5999行が選択されました。

経過: 00:00:53.48

SCOTT> alter session set events='10053 trace name context off';

セッションが変更されました。

経過: 00:00:00.00

SCOTT>

オプティマイザトレースファイルのサイズを比較すると以下のようになります:) 
オプティマイザを悩ますクエリ、ヒントで考えるな!感じろ!にしたクエリ(考えてますけど〜〜〜w)、最後が、相関副問合せに書き換えたクエリの順となっています。

[oracle@lampeye trace]$ ll
合計 59088
-rw-r----- 1 oracle oinstall 54431 9月 8 22:14 alert_lampeye2.log
-rw-r----- 1 oracle oinstall 54294792 9月 8 22:16 lampeye2_ora_5913.trc
-rw-r----- 1 oracle oinstall 5154103 9月 8 22:17 lampeye2_ora_5969.trc
-rw-r----- 1 oracle oinstall 898699 9月 8 22:19 lampeye2_ora_5981.trc
[oracle@lampeye trace]$

最初のトレースファイル(.trc),54294792バイトもありますよーw このオプティマイザトレースファイルがハードパースで48秒も考え込んでた時のものです。
(対象クエリも合わせて載せてみました)

-rw-r----- 1 oracle oinstall 54294792  9月  8 22:16 lampeye2_ora_5913.trc

こんなにオプティマイザを考えさせちゃうクエリ↓

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
T1.surro_id IN (リテラル,....限界まで)
OR T1.surro_id IN (リテラル,....限界まで

.....以下....すきなだけ繰り返しw

/


2つめのトレースファイル、5154103バイトで、1/10程度になりましたーでも大きいですよね。これはヒントを付加して悩まないようにしてあげたものですが、それでもこんなサイズになってます!

-rw-r----- 1 oracle oinstall  5154103  9月  8 22:17 lampeye2_ora_5969.trc

案2、ヒントを付加したクエリでもこんなにでるのね。

SELECT   
/*+
LEADING(T1 T2 T3)
USE_HASH(T1 T2)
USE_HASH(T1 T3)
INDEX(T1 IX01_TEST)
INDEX(T3 IX01_TEST)
*/
T1.surro_id,
T1.name,
T1.modified
FROM
test T1 JOIN (
SELECT
/*+
MERGE
INDEX(test IX02_TEST)
*/
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
T1.surro_id IN (リテラル,....限界まで)
OR T1.surro_id IN (リテラル,....限界まで)

.....以下....すきなだけ繰り返しw

/


最後のトレースファイルが(案1)、相関副問合せを利用して改善したクエリをハードパースした際に出力されたトレースファイルです。随分違うもんですねー

-rw-r----- 1 oracle oinstall   898699  9月  8 22:19 lampeye2_ora_5981.trc

案1、処理時間も短いけど、ほんと決断はやって感じですね。

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
)
/




ここまでのあらずじ

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

|

トラックバック

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

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

コメント

コメントを書く