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