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: Tue, 24 Nov 2009 23:39:02 +0100

> Take a look at the 10046 files I just generated
> http://www.neilkodner.com/good_10046.txt
> and
> http://www.neilkodner.com/bad_10046.txt
> 
> Pay attention to the second query. It ran for 15 or so seconds, did 
> lots of db file sequential read, and then subsequent executions did 
> not. I disconnected, reconnected again, and the query ran quickly. I 
> disconnected again, reconnected, and got a good plan once again. I 
> can provide that 10046 as well.

That looks like the PIO trap - the subsequent execution of the inefficient 
query used exactly the same execution plan but didn't perform any physical 
reads, but only consistent gets from the cache.

This can be seen from the two 10046 traces for the query with PRTY_ID=64437.

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: