Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
  • Date: Wed, 25 Nov 2009 08:33:46 +0100

> On Tue, Nov 24, 2009 at 2:41 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> But all things considered, I can produce the PIO trap time and time again
> using 10.2.0.4 OFE.  With extensive testing, I've never been able to
> reproduce it with 10.1.0.

This corresponds to what has been seen from some of your 10046 traces provided:

- You'll be able to reproduce longer execution times with 10.2.0.4 OFE because 
some of the execution plans seem to be suboptimal - obviously caused by the bad 
cardinality estimates as discussed so far.
This will happen when the blocks have to read physically rather than from the 
cache - so sometimes the execution time will be good, sometimes bad depending 
on this.

- This is probably not reproducible with the 10.1 OFE, since the execution 
plans tend to be better, due to estimates closer to reality.
Even with physical I/O necessary these will perform good, due to significantly 
less I/O required.

Still outstanding:

- Setting DENSITY explicitly to get similar cardinality estimates with 10.2.0.4 
OFE as with 10.1 OFE
- Testing DYNAMIC_SAMPLING on table level

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

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


Other related posts: