Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Mon Dec 18 2006 - 06:04:53 CST
![]() |
![]() |