Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Robert Freeman <robertgfreeman@xxxxxxxxx>
  • To: nkodner@xxxxxxxxx, Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
  • Date: Tue, 24 Nov 2009 14:56:40 -0800 (PST)

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@xxxxxxxxx>
To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
Cc: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxxxxx> 
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
>
>

Other related posts: