Re: Better cardinality estimate when dialing optimizer_features_enable back
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
- Date: Tue, 24 Nov 2009 21:46:33 +0100
> To answer your initial question, I think index access does in fact
> make sense. I'm retrieving anywhere from one to a thousand rows from
> letter_bin per call. This query is executed thousands of times as
> part of a batch job.
This raises in first place the question why do you need to execute the query
thousands of times?
Apart from that you say that when accessing even only a thousand of rows via
the index the performance is worse than performing a full table scan? Looking
at the number of blocks of the table that seems to be rather unlikely to me.
According to the plans posted most of the time is wasted on accessing the table
by rowid - is your issue really the usage of the index? I can't believe that
the examples posted would be faster when using a FTS for 1,700 rows.
Can you show us an example with ALLSTATS LAST where the modified plan (e.g.
using the cardinality hint) is actually faster?
Have you tried to set the DENSITY manually to address the single-table access
estimate issue?
> As a frame of reference, the table represents letters being mailed to
> an employer(prty_id), for every employee. There are many small
> businesses each having a few rows. There are medium and large
> businesses that have many rows. And finally there are employment
> agencies which have a LOT of rows. Hopefully this explains the
> distribution of data.
You have more than 200,000 distinct values - at a quick glance almost every
value of the 254 recorded in the histogram seems to be unpopular. The histogram
is almost useless given your distribution I would say.
> As far as the transitive closure issue, there are also other queries
> which query letter_bin by party_id without the join to parties -
> those are affected as well. The cardinality is always incorrect for
> any query by letter_bin.party_id.
It was just in idea where I would be interested in the result - as I said it
doesn't address the root cause.
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
______________________________________________________
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:
