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