RE: ORDER BY and first_rows_10 madness

Hi  Jonathan,
thanks you for the execellent explanation ;)
I have found out, that _sort_elimination_cost_ratio is not influencing the CBO 
(in this very case) in no way. Perhaps this parameter exists only fix fix bug 
4570598 when  FIRST_ROWS mode is used...

I have tried /*+ ALL_ROWS */ and it is working. I will create an outline 
for this statement. 
I have already tried  to use hints /outlines :
select /*+ leading(T12)  (T12 S_ASSET_CON_EXT01_X)  */
....
or even
select /*+ leading(T12) cardinality(T12 1) (T12 S_ASSET_CON_EXT01_X)  */
...

The problem with the outline approach is that depending on the values of 
ASSET_ID predicate (and despite the presence of outline ), the CBO is still
switching to using suboptimal execution plan, perhaps because 
"_optim_peek_user_binds" = TRUE.

I will try with /*+ ALL_ROWS */ and I hope that this time the execution plan 
will be stable.

Best Regards. Milen 



Looking at the single table access path doesn't really help.

For first_rows_K, the optimizer does one pass to work 
out the number of rows (N) the query should return, then it starts again (in 
Kmode) working on a strategy to get enough
data at each step to get K/N of the data expected at that step.

If the optimizer predicts a "large" number of rows, 
then K/N is small, and the cost of getting all rows
at some step, sorting them, then selecting the first
K can easily be much larger than using an index
to find the first K in a different way.

I have noticed some oddities where the optimizer's
estimate of the number (and cost) of throwing away inappropriate rows whilst 
finding the first K by index is
unrealistic.

To bypass this at the statement level, just hint with
/*+ all_rows */.  There is a case for OLTP systems
(especially across a web app server) that the user-
facing app should run first_rows_K - where K is the
typical arraysize used by the app server - but any
reports should run all_rows either by hint, or by having
a log-on trigger change the optimizer-mode.

The _sort_elimination_cost_ratio is not really something
you should fiddle with.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals 
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ 
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
--
http://www.freelists.org/webpage/oracle-l

Other related posts: