Re: Better cardinality estimate when dialing optimizer_features_enable back

What is it set to now? Setting it at the database level would scare me a bit because it becomes global and you don't know what else it might impact.
I was just wondering if somehow it was kicking in at times when it was not expected. Have you tried to run the query with it set to 0 as a hint several times to see if the performance smooths out?

 Robert G. Freeman
Oracle ACE
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Other various titles
Blog: http://robertgfreeman.blogspot.com




________________________________
From: Neil Kodner <nkodner_at_gmail.com>
To: Randolf Geist <info_at_sqltools-plusplus.org>
Cc: oracle-l-freelists <oracle-l_at_freelists.org>
Sent: Tue, November 24, 2009 3:52:29 PM
Subject: Re: Better cardinality estimate when dialing optimizer_features_enable  back

Would it help that I have optimizer_dynamic_setting at 2 at the database level?  I tried using a dynamic_sampling hint with a value of 4; that didn't make a difference on the cardinality estimate.


On Tue, Nov 24, 2009 at 5:47 PM, Randolf Geist <info_at_sqltools-plusplus.org> wrote:

> So.... looking through this thread.... any way that dynamic sampling
>>> could be a bad boy here?
>>>
>>> Just a wild, off the cuff thought....
>>>
>>> RF
>>>
>>> Robert G. Freeman
>>> Oracle ACE
>
>That is actually very good idea  (all the time I had the impression I missed something obvious) - in principle dynamic sampling should be able to get the selectivity right.
>
>>In this particular case the question would be if the overhead induced by dynamic sampling would outweigh the benefit of it. It depends largely on the sample level required to get helpful estimates - it might be worth a try to add a "DYNAMIC_SAMPLING(LETTER_BIN 2)" hint - starting with a low sample level (may be even 1). Higher sampling levels will get the selectivity right but the overhead might be significant then.
>
>
>>Regards,
>>Randolf
>
>>Oracle related stuff blog:
>http://oracle-randolf.blogspot.com/
>
>>Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
>http://www.apress.com/book/view/1430226684
>http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
>>______________________________________________________
>>GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
>>Jetzt freischalten unter http://movieflat.web.de
>
>

--
http://www.freelists.org/webpage/oracle-l