Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Tue, 24 Nov 2009 17:24:25 -0500

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.

I'm not sure what you mean by IO profile.  Randolf noted that our mreads
came in faster than our sreads.  I re-gathered the system stats and the
results were consistent with the earlier stats.




On Tue, Nov 24, 2009 at 5:02 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> I (like Randolf) am a bit suspect that this is entirely just an
> execution plan problem.  There seems to be some relationship to the IO
> profile.  I think you need to get both a fast and slow execution
> profile for the 10.2.0.4 version and try and drill down on the
> difference in the execution stats.  It may be useful to get a cold
> cache, cold filesystem profile for each and an warm version also.
>
> If it were truly just an execution plan problem then it should be
> consistently reproducible for each and every execution.
>
> On Tue, Nov 24, 2009 at 1:54 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> >> What is the difference in execution time of the two queries in
> >> allstats_both_queries.txt ?
> >>
> >  Anywhere from a 30 seconds to none at all.  That's the puzzling thing.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>

Other related posts: