Subject: RE: ORDER BY and first_rows_10 madness

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Sat, 16 Dec 2006 08:40:44 -0000


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

For first_rows_K, the optimizer does one pass to workout 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

----- Original Message -----

Date: Fri, 15 Dec 2006 15:30:51 +0100
From: "Milen Kulev" <makulev@xxxxxxx>
Subject: ORDER BY and  first_rows_10 madness

Below is an excerpt of 10053 event trace file.-------------------------------------------------

SINGLE TABLE ACCESS PATH (First K Rows)
 TABLE: S_ASSET_CON     ORIG CDN: 2749192  ROUNDED CDN: 1  CMPTD CDN: 1
 Access path: tsc  Resc:  9654  Resp:  9654
 Access path: index (equal)
     Index: S_ASSET_CON_EXT01_X
 TABLE: S_ASSET_CON
     RSC_CPU: 0   RSC_IO: 4
 IX_SEL:  0.0000e+00  TB_SEL:  4.4555e-07
 Skip scan: ss-sel 0  andv 1481513
   ss cost 1481513
   index io scan cost 0
 Access path: index (scan)
     Index: S_ASSET_CON_U1
 TABLE: S_ASSET_CON
     RSC_CPU: 0   RSC_IO: 4
 IX_SEL:  4.4555e-07  TB_SEL:  4.4555e-07
 BEST_CST: 1.00  PATH: 4  Degree:  1

....
SINGLE TABLE ACCESS PATH (First K Rows)
 TABLE: S_CONTACT     ORIG CDN: 16  ROUNDED CDN: 15  CMPTD CDN: 15
 Access path: tsc  Resc:  2  Resp:  2
.....
 Access path: index (no sta/stp keys)
     Index: S_CONTACT_M12
 TABLE: S_CONTACT
     RSC_CPU: 0   RSC_IO: 18
 IX_SEL:  1.0000e+00  TB_SEL:  1.0000e+00
 ....
 ORDER BY sort
First K Rows: switch to Amode plans
****** Recost for ORDER BY (using index) ************
First K Rows: switch to Kmode plans
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
 TABLE: S_CONTACT     ORIG CDN: 16  ROUNDED CDN: 15  CMPTD CDN: 15
 Access path: tsc  Resc:  2  Resp:  2
 Skip scan: ss-sel 1  andv 15
   ss cost 15
   index io scan cost 1
 Access path: index (no sta/stp keys)
     Index: S_CONTACT_M12
 TABLE: S_CONTACT
     RSC_CPU: 0   RSC_IO: 18
 IX_SEL:  1.0000e+00  TB_SEL:  1.0000e+00
 BEST_CST: 5.00  PATH: 4  Degree:  1
 .....
-------------------------------------------------

Any pointers,  exeperiences , lessons learned ?


--
//www.freelists.org/webpage/oracle-l

Subject: RE: ORDER BY and first_rows_10 madness

Other related posts: