Re: Better cardinality estimate when dialing optimizer_features_enable back

> 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 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.

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

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

Other related posts: