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