RE: ORDER BY and first_rows_10 madness
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 18 Dec 2006 13:04:53 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
