Subject: RE: ORDER BY and first_rows_10 madness

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

• Original Message -----
  > Date: Fri, 15 Dec 2006 15:30:51 +0100
  > From: "Milen Kulev" <makulev_at_gmx.net>
  > 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 ?
  >
  

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