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: Wed, 25 Nov 2009 08:25:01 +0100
> Would it help that I have optimizer_dynamic_setting at 2 at the
> database level? I tried using a dynamic_sampling hint with a value of
> 4; that didn't make a difference on the cardinality estimate.
As pointed out by Robert - I wouldn't recommend changing the global level, for
two reasons:
- Potentially affects other queries that might change their execution plan not
for the better
- Dynamic Sampling gets only get activated under certain circumstances if
statistics have been gathered
There is a difference between the meaning of the level of Dynamic Sampling
between Session/Cursor and Table level.
For testing purposes I would recommend using it on Table level as already
outlined - this means explicitly mentioning the table alias in the
DYNAMIC_SAMPLING hint. If you don't specify the table alias this will be
Dynamic Sampling on cursor level - level 4 very likely would not activate
Dynamic Sampling at all with your statistics in place and the predicates used.
So if you want to test this:
- Use the DYNAMIC_SAMPLING hint with the table alias:
DYNAMIC_SAMPLING(LETTER_BIN, <level>). This will activate Dynamic Sampling on
the alias LETTER_BIN even with statistics in place to check for selectivity
estimates
- Check the EXPLAIN PLAN output -> In the "Notes" section you need to see
"Dynamic Sampling used..."
If it doesn't change the estimate, increase the level step by step, at last
with level 10 the estimates need to get close to reality, obviously this is not
reasonable, but it confirms that it works in general.
For further details you can run a 10053 optimizer trace which will tell you the
details about the Dynamic Sampling performed and the decisions of the optimizer
to use or discard the results of the sampling.
Robert: According to the provided information Dynamic Sampling is here not the
culprit - the majority of time is spent on the actual execution, not the parse
step.
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
