Re: Better cardinality estimate when dialing optimizer_features_enable back
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: Robert Freeman <robertgfreeman@xxxxxxxxx>
- Date: Tue, 24 Nov 2009 18:07:12 -0500
We're using the default value of 2. Setting it to 0 didn't make a
difference.
On Tue, Nov 24, 2009 at 5:56 PM, Robert Freeman <robertgfreeman@xxxxxxxxx>wrote:
> 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:
