Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
  • Date: Tue, 24 Nov 2009 16:51:47 -0500

>
>
> But those plans don't show evidence that they perform differently - the
> only difference is a hash join instead of a nested loop and a hash unique
> instead of sort unique but the number of blocks processed is the same -
> these two examples ought to perform almost equally.
>
> The access to LETTER_BIN is the same operation and since PARTIES returns
> exactly a single row I can't identify why these two plans should show a
> significantly different run time behaviour - and according to the ALLSTATS
> LAST output both completed in the same time (less than a second since no
> physical reads were involved).
>
> When comparing actual execution time you need to be very careful if
> physical reads are involved or not - reading 2,000 blocks at 8ms per block
> will take significantly more time than reading those 2,000 from memory.
>
> Can you provide an example with measured different runtime behaviour?
>
> This is exactly what's bothering me.  I have some 10046 tracefiles taken
that show all of the db file sequential reads when running with 10.2.0.4.
that are just not present when setting OFE back to 10.1.  I've repeated this
test far too many times to believe that it's a matter of PIO.

What's odd is that the execution plans look nearly identical but have a
substantially different runtime.  We both agree on that.

A 'bad'  10046 tracefile can be found at
http://www.neilkodner.com/10046_trace.txt

A good tracefile, using ofe 10.1.0 is
http://www.neilkodner.com/10046_10_1_0.txt


> By the way, the LETTER_BIN table seems to have a significant number of
> migrated/chained rows - probably due to the large rows as you say with
> inline CLOBs.


I suspected chained rows but I didn't see the corresponding wait (table
fetch continued row) so I haven't even checked.  Is that a mistake?

>
> > As a trial, I also modified the query to use parties.party_id rather
> > than letter_bin.prty_id - it had no effect on the plan's cardinality.
>
> Sorry, my intention here was to have both in the query - a filter on
> PARTIES.PRTY_ID _and_ LETTER_BIN.PRTY_ID to avoid the transitive closure
> from taking place.
>
> 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.de/Expert-Oracle-Practices-Database-Administration/dp/1430226684
> ___________________________________________________________
> Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.!
> http://produkte.web.de/go/02/
>
>

Other related posts: