54 is less than 4 for CBO ? solved

Thanks for the explanation Jonathan,
the digits are fitting pretty well to your your explanation of points 1) and 2) :

I have
First K Rows: K/N ratio = 0,000001091131285

Join cardinality (N) is 916480 =>
1/N = 1/ 916480 = 0,000001091131285 = K/N ratio!

CBO is (supposedly) adjusting the cardinality of the join to the optmizer goal (FIRST_ROWS(1),FIRST_ROWS(10) etc ) using the adjusted join cardinality factor (First K Rows: K/N ratio line in the 10053 trace file).

The samples (format is HINT => join card of 10053 event trace file):  

base, no hints = > Join cardinality: 916480 = outer (303367558) * inner (31768) * sel (9.5096e-08)
FIRST_ROWS(1) => Join cardinality: 1 = outer (332) * inner (31768) * sel (9.5096e-08)
FIRST_ROWS(10) => Join cardinality: 10 = outer (3311) * inner (31768) * sel (9.5096e-08)
FIRST_ROWS(100) => Join cardinality: 100 = outer (33102) * inner (31768) *

sel (9.5096e-08) 
FIRST_ROWS(1000) => Join cardinality: 1000  = outer (331014)    * inner (31768) * 
sel (9.5096e-08) 

Best Regards. Milen

• Original Message -----
  > Date: Mon, 22 May 2006 14:27:56 +0200 (MEST)
  > Wrom: SKVFVWRKJVZCMHVIBGDADRZFSQHYUCD
  > Subject: RE: 54 is less than 4 for CBO ?
  >
  > In the 10053 event file I see the following lines (with
  > optimizer_mode=FIRST_ROWS_1)
  > First K Rows: K/N ratio = 0,000001091131285, qbc=11020ea80 First K
  > Rows: Setup end
  >
  >
  >
  > And now the next bacth of questions (conserning "alter session set
  > optimizer_mode=FIRST_ROWS_1" ):
  >
  > 1)Where the digit
  > First K Rows: K/N ratio = 0,000001091131285, qbc=11020ea80
  > is coming from ?
  >
  > 2) Why the FTS cost on T_FZG_SA is only 2 ? How this value is
  > calculated
  >
  

Roughly speaking:

1. Oracle calculates the expected cardinality N of the final result and produces a fraction that represents your intention of returning 1/N of the rows - this would be 10/N, 100/N etc. if you used first_rows_10, first_rows_100.
2. Given that you want 1/N th of the data, then you need only do 1/Nth of the driving table scan to get enough data in that step to continue to the next step. Allow for some rounding errors, and oddly placed 'plus 1'.

Regards

Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

-- 


Bis zu 70% Ihrer Onlinekosten sparen: GMX SmartSurfer!
      Kostenlos downloaden: http://www.gmx.net/de/go/smartsurfer
    
--
http://www.freelists.org/webpage/oracle-l