Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> 54 is less than 4 for CBO ? solved

54 is less than 4 for CBO ? solved

From: Milen Kulev <makulev_at_gmx.net>
Date: Fri, 26 May 2006 15:03:02 +0200
Message-ID: <20060526130302.297620@gmx.net>


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

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
Received on Fri May 26 2006 - 08:03:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US