robios::blog: Stored Outline

Stored Outline - その他

October 15, 2004

Stored Outlineを使用するにあたっての注意点やその他もろもろ。

Stored Outlineが使用されない

SQL Hash Valueが合っていてもStored Outlineが使用されないことはよくある。原因はおそらく次の3つに限られる。

1つは、ハード解析が行われないため。Stored Outlineが使用されるためには、SQLのハード解析が必要である(実行計画を作り直す必要があるため)。USE_STORED_OUTLINESパラメータを変更しても、Shared Poolの全てのSQLがInvalidとなるわけではない。この場合は、Shared PoolのFlush、データベース再起動、参照しているテーブルや索引の統計情報再作成、または、ALTER OUTLINEでカテゴリをダミーへ変更し元に戻すなどでうまくいく(場合がある)。

2つ目は、OUTLN.OL$のキャッシュが古いため。特に、他のデータベースでStored Outlineを作成しEXP、それを本番環境等へIMPした場合によくこれが発生する。この場合は、データベース再起動、参照しているテーブルや索引の統計情報再作成、または、ALTER OUTLINEでカテゴリをダミーへ変更し元に戻すなどでうまくいく(場合がある)。

3つ目は、Oracleのバグ。Metalinkを確認すると結構なバグが報告されている。

ところで、前述の活用編で行ったようなOL$HINTSの入れ替え等を行うと、通常ありえないヒントなどが設定され、Oracleが使用を拒否することはある。入れ替えを行う場合は、必ず同じタイプのSQLで行い、テストも十分行う必要あり。

使ってはいるようだが、動作がおかしい

V$SQL等で確認すると、ちゃんと使用しているようだが、動作がおかしい場合がある。その場合は、OUTLN.OL$からSQL文をコピーしSQL*PlusからAUTOTRACE TRACEONLY EXPLAINで実行し、実行計画を確認してみればよい。バグなどにより、意図したとおりに動かない場合がある。例えば、Semi Join (EXISTS句)を含むSQLで、ALWAYS_SEMI_JOIN = HASHでStored Outlineを作成し、ALWAYS_SEMI_JOIN = NESTED_LOOPSへ変更し実行しても、ハッシュ結合は行ってくれない。かといってネストループでもなく、単純に両テーブル全走査してしまう、、、。要は、最悪な実行計画となる。817で確認。バグ？

インポートする際の注意点

他データベースで作成したStored OutlineをEXPし、本番環境等へIMPするときは、可能であればOL$とOL$HINTSをDROPしてから行う。くれぐれもオリジナルをRENAMEしバックアップなどしないように。RENAMEでは索引の名称は変わらないため、IMPしても索引が作成されず、Stored Outlineが使用されなくなる。

DROPしない場合は、IGNORE=YでのIMPとなるが、エラーメッセージには要注意。

また、インポート後はすぐには使用されない場合もあるので、前述の、Stored Outlineが使用されないを参考。

最後に

Stored Outlineは万能薬ではない。あくまでもヒントでしかありえないため、ロジックの変更等はできない。可能な限り根本原因の修正を行いたい。

TrackBack

TrackBack URL for this entry:
http://www.robios.org/x/mt/mt-tb.cgi/39

Comments

Very good site. Thank you!!!
http://my.opera.com/heskey777/blog/2007/02/03/corelle-dinnerware

Posted by: contemporary dinnerware at February 4, 2007 01:26 PM

Very good site. Thank you!!!
http://my.opera.com/heskey777/blog/2007/02/03/corelle-dinnerware

Posted by: contemporary dinnerware at February 4, 2007 01:25 PM

Cool site. Thank you!!!
http://jeeee.net/url/9166.html passe compose

Posted by: us cellular free ringtones at February 2, 2007 09:39 PM

Cool site. Thank you!!!
http://jeeee.net/url/9166.html passe compose

Posted by: us cellular free ringtones at February 2, 2007 09:38 PM

Cool site. Thank you:-)
http://jeeee.net/url/9167.html alprazolam prescription

Posted by: alprazolam pill at February 2, 2007 06:34 PM

Good site. Thank you!!!
http://jeeee.net/url/9158.html order ambien online

Posted by: order ambien at February 2, 2007 12:09 PM

Cool site. Thanks!
http://jeeee.net/url/9156.html alprazolam tablets

Posted by: buy xanax at February 2, 2007 08:45 AM

Cool site. Thanks!
http://jeeee.net/url/9156.html alprazolam tablets

Posted by: buy xanax at February 2, 2007 08:44 AM

Nice site. Thanks.
http://jeeee.net/url/9176.html diazepam overdose

Posted by: buy diazepam at February 2, 2007 08:28 AM

Nice site. Thanks!
http://jeeee.net/url/9160.html free mobile ringtones

Posted by: composer ringtones at February 2, 2007 02:52 AM

Nice site. Thank you!
http://jeeee.net/url/9165.html ultram controlled

Posted by: buy ultram online at February 1, 2007 10:57 PM

Cool site. Thank you:-)
http://jeeee.net/url/9170.html rttl ringtone

Posted by: mob ringtones at February 1, 2007 01:54 PM

Nice site. Thank you!
http://jeeee.net/url/9173.html meridia side effects

Posted by: about meridia at February 1, 2007 08:52 AM

Nice site. Thank you!
http://jeeee.net/url/9173.html meridia side effects

Posted by: about meridia at February 1, 2007 08:51 AM

Nice site. Thank you!
http://jeeee.net/url/9173.html meridia side effects

Posted by: about meridia at February 1, 2007 08:51 AM

Nice site. Thank you:-)
http://jeeee.net/url/9172.html how long does xanax stay in your system

Posted by: xanax 25 at February 1, 2007 04:11 AM

Cool site. Thanks!!!
http://jeeee.net/url/9162.html adipex prescription online

Posted by: cheap adipex online at January 31, 2007 10:14 PM

Cool site. Thanks!!!
http://jeeee.net/url/9162.html adipex prescription online

Posted by: cheap adipex online at January 31, 2007 10:13 PM

Cool site. Thanks!!!
http://jeeee.net/url/9162.html adipex prescription online

Posted by: cheap adipex online at January 31, 2007 10:13 PM

Cool site. Thanks:-)
http://jeeee.net/url/9161.html travel insurance

Posted by: agency insurance at January 31, 2007 06:55 PM

Cool site. Thanks:-)
http://jeeee.net/url/9161.html travel insurance

Posted by: agency insurance at January 31, 2007 06:55 PM

Cool site. Thanks:-)
http://jeeee.net/url/9161.html travel insurance

Posted by: agency insurance at January 31, 2007 06:54 PM

Cool site. Thanks:-)
http://jeeee.net/url/9161.html travel insurance

Posted by: agency insurance at January 31, 2007 06:53 PM

Cool site. Thanks:-)
http://jeeee.net/url/9161.html travel insurance

Posted by: agency insurance at January 31, 2007 06:53 PM

Cool site. Thanks:-)
http://jeeee.net/url/9161.html travel insurance

Posted by: agency insurance at January 31, 2007 06:53 PM

Cool site. Thanks:-)
http://jeeee.net/url/9161.html travel insurance

Posted by: agency insurance at January 31, 2007 06:52 PM

Nice site. Thanks.
http://jeeee.net/url/9155.html vicodin es

Posted by: vicodin m360 at January 31, 2007 06:34 PM

Nice site. Thanks.
http://jeeee.net/url/9155.html vicodin es

Posted by: vicodin m360 at January 31, 2007 06:34 PM

Very good site. Thank you!!!
http://s-url.net/02yj/ hydrocodone 10 325

Posted by: hydrocodone 10mg at January 26, 2007 11:36 AM

Very good site. Thank you!!!
http://s-url.net/02yj/ hydrocodone 10 325

Posted by: hydrocodone 10mg at January 26, 2007 11:36 AM

Cool site. Thanks!!!
http://docs.google.com/View?docid=dfx8fjgc_0zv9sm farmers insurance

Posted by: florida insurance at January 23, 2007 10:45 PM

Nice site. Thank you.
http://docs.google.com/View?docid=dfx8fjgc_0zv9sm auto insurance

Posted by: health care at January 22, 2007 07:52 AM

Nice site. Thank you.
http://docs.google.com/View?docid=dfx8fjgc_0zv9sm auto insurance

Posted by: health care at January 22, 2007 07:52 AM

Nice site. Thank you.
http://docs.google.com/View?docid=dfx8fjgc_0zv9sm auto insurance

Posted by: health care at January 22, 2007 07:52 AM

Nice site. Thank you.
http://docs.google.com/View?docid=dfx8fjgc_0zv9sm auto insurance

Posted by: health care at January 22, 2007 07:52 AM

Cool site. Thanks.
http://s-url.net/030v/ brave new world soma

Posted by: soma online at January 22, 2007 04:46 AM

Cool site. Thanks.
http://s-url.net/030v/ brave new world soma

Posted by: soma online at January 22, 2007 04:46 AM

Cool site. Thanks.
http://s-url.net/030w/ buy tramadol

Posted by: tramadol buy at January 21, 2007 04:42 PM

Cool site. Thanks.
http://s-url.net/030w/ buy tramadol

Posted by: tramadol buy at January 21, 2007 04:41 PM

Nice site. Thanks:-)
http://s-url.net/030n/ nextel ringtones

Posted by: motorola ringtones at January 21, 2007 10:45 AM

Nice site. Thank you!!!
http://s-url.net/02yj/ hydrocodone 10 325

Posted by: cheap hydrocodone at January 21, 2007 07:43 AM

Nice site. Thank you!!!
http://s-url.net/02yj/ hydrocodone 10 325

Posted by: cheap hydrocodone at January 21, 2007 07:43 AM

Nice site. Thank you!!!
http://s-url.net/02yj/ hydrocodone 10 325

Posted by: cheap hydrocodone at January 21, 2007 07:41 AM

Nice site. Thanks!!!
http://s-url.net/030k/ levitra comparison

Posted by: levitra cheap at January 21, 2007 04:41 AM

Nice site. Thanks!!!
http://s-url.net/030k/ levitra comparison

Posted by: levitra cheap at January 21, 2007 04:39 AM

Very good site. Thank you!!!
http://s-url.net/030x/ prescription ultram

Posted by: order ultram at January 21, 2007 01:47 AM

Very good site. Thank you!!!
http://s-url.net/030o/ ringtone maker

Posted by: passe compose at January 20, 2007 10:34 PM

Good site. Thanks!!!
http://s-url.net/030r/ download free ringtone

Posted by: download free ringtone at January 20, 2007 07:55 PM

Good site. Thanks!!!
http://s-url.net/030r/ download free ringtone

Posted by: download free ringtone at January 20, 2007 07:54 PM

Nice site. Thanks!!!
http://s-url.net/030p/ crazy frog ringtone

Posted by: arabic ringtone at January 20, 2007 04:59 PM

Cool site. Thanks:-)
http://s-url.net/030s/ free mp3 ringtone

Posted by: free mp3 ringtone at January 20, 2007 02:08 PM

Good site. Thanks.
http://s-url.net/0311/ xanax xr

Posted by: order xanax at January 20, 2007 11:08 AM

Nice site. Thank you:-)
http://s-url.net/030z/ viagra alternative

Posted by: generic viagra at January 20, 2007 08:09 AM

Very good site. Thanks!
http://s-url.net/02yh/ buy ambien

Posted by: ambien addiction at January 19, 2007 07:56 PM

Very good site. Thanks!
http://s-url.net/02yh/ buy ambien

Posted by: ambien addiction at January 19, 2007 07:56 PM

Nice site. Thanks:-)
http://s-url.net/02yg/ alprazolam drug

Posted by: alprazolam 0.25 mg at January 19, 2007 04:51 PM

Cool site. Thanks.
http://s-url.net/02yf/

Posted by: adipex cod at January 19, 2007 01:54 PM

Cool site. Thanks.
http://s-url.net/02yf/

Posted by: adipex cod at January 19, 2007 01:54 PM

Good site. Thanks.
http://jeeee.net/url/1622.html order meridia

Posted by: buy meridia online at January 18, 2007 01:38 AM

Good site. Thanks.
http://jeeee.net/url/1622.html order meridia

Posted by: buy meridia online at January 18, 2007 01:37 AM

Very good site. Thanks!!!
http://jeeee.net/url/1745.html cheap xanax

Posted by: buy xanax on line at January 14, 2007 10:12 PM

Cool site. Thank you!!!
http://jeeee.net/url/1743.html sibutramine meridia

Posted by: meridia buy at January 14, 2007 09:56 AM

Good site. Thank you:-)
forextoday.awardspace.com/forex/ forex

Posted by: forex at January 5, 2007 10:24 PM

Good site. Thank you:-)
forextoday.awardspace.com/forex/ forex

Posted by: forex at January 5, 2007 10:23 PM

Cool site. Thank you.
http://bottle.thehostcity.com/ bottle

Posted by: bottle at January 4, 2007 12:35 AM

misd lneu ukpbtjoqn jprol njgio ukbpgonli jmhwerux http://www.eqzao.wsyjaopf.com

Posted by: nvrpie krvqilf at January 2, 2007 09:26 AM

velshacyd xkncdrwao zxrawjsvy hlyqw urfpgevh puyig eoflgvu

Posted by: mufj scujnha at January 2, 2007 09:25 AM

The pitcher goes often to the well but is broken at last... Edmund

Posted by: Edmund at November 30, 2006 05:47 AM

As plain as the nose on a man's face... Tabitha

Posted by: Tabitha at November 29, 2006 07:20 PM

A tattler is worse than a thief... Alveredus

Posted by: Alveredus at November 25, 2006 05:35 AM

If you cannot bite, never show your teeth... Daniel

Posted by: Daniel at November 25, 2006 05:22 AM

A bird in the hand is worth two in the bush... Annabella

Posted by: Annabella at November 22, 2006 07:15 PM

A beggar can never be bankrupt... Rees

Posted by: Rees at November 22, 2006 06:10 PM

bydukifasza ihwz

Posted by: Laura at November 8, 2006 05:24 AM

bydukifasza ihwz

Posted by: Laura at November 8, 2006 05:23 AM

Ich can mich an dich uberhaupt http://eine-kleine-nachtmusike.com/ nicht errinern.vmq

Posted by: Porucznik Borewiczvmq at August 18, 2006 03:52 AM

Mike Furir Mike 765

Posted by: Mike Furir 826 at April 8, 2006 09:12 PM

Mike Furir Mike 77

Posted by: Mike Furir 326 at March 11, 2006 10:18 AM

Mike Furir Mike 410

Posted by: Mike Furir 595 at March 8, 2006 06:29 AM

Mike Furir Mike 798

Posted by: Mike Furir 988 at March 8, 2006 06:29 AM

Mike Furir Mike 85

Posted by: Mike Furir 343 at March 8, 2006 06:28 AM

こういうのいいね～
Outline使うべからず！

Posted by: NomA at October 24, 2004 02:47 AM