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 17:27:21 -0500
On Tue, Nov 24, 2009 at 5:21 PM, Randolf Geist
<info@xxxxxxxxxxxxxxxxxxxxx>wrote:
> > 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
>
> But in these trace files you have significantly different plans - the 10.1
> using efficient ones e.g. using the "BITMAP AND" operation to eliminate most
> of the rows in the index operation before visiting the table or different
> indexes depending on the query. The 10.2.0.4 one does always a simple index
> range scan on LETTER_BIN_PRTY_ID_I and therefore sometimes has to visit
> thousands of table rows / blocks.
>
> I've mentioned it already several times - have you tried setting the
> DENSITY manually using DBMS_STATS.SET_COLUMN_STATS on LETTER_BIN.PRTY_ID to
> get the pre-10.2.0.4 non-popular value density usage? That might be the
> "remedy" (if it is working as described) you're asking for in your other
> reply to Greg.
>
> I'll work on the density setting. I've never manually tweaked optimizer
statistics before. Fortunately our QA and test systems exhibit the same
behavior when it comes to cardinality estimates.
> > 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?
>
> "table fetch continued row" is not a wait event but a statistic. It will
> show up as additional buffer get and potentially as "db file sequential
> read". I noticed that requesting e.g. 1,695 rows from the table resulted in
> more than 2,000 consistent gets - so more than a single get per row - this
> usually indicates chained / migrated rows.
>
> That makes things much clearer-thanks. I'm now checking for chained rows.
For some reason I thought table fetch continued row was a wait event.
> 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
>
>
Other related posts:
