About CBO cost in oracle10g

From: eygle <eygle_at_itpub.net>
Date: 21 May 2004 02:25:53 -0700
Message-ID: <f9bc34d.0405210125.3ee8451e@posting.google.com>

We know ,Oracle compute cost for CBO.
And to NL,we have the below formula:

NL - NESTED LOOP JOIN
join cost = cost of accessing outer table + (cardinality of outer table * cost of accessing inner table )

But with Oracle10g,I find all of that is changed: For example:

---

BASE STATISTICAL INFORMATION

---

Table stats Table: DEPT Alias: DEPT

  TOTAL ::  CDN: 4  NBLKS:  5  AVG_ROW_LEN:  20
  COLUMN:     DEPTNO(NUMBER)  Col#: 1      Table: DEPT   Alias: DEPT
    Size: 3  NDV: 4  Nulls: 0  Density: 2.5000e-01 Min: 10  Max: 40
    No Histogram: #BKT: 1
        (1 uncompressed buckets and 2 endpoint values)

Index stats
  Index: PK_DEPT COL#: 1
    TOTAL :: LVLS: 0 #LB: 1 #DK: 4 LB/K: 1 DB/K: 1 CLUF: 1

---

Table stats Table: EMP Alias: EMP

  TOTAL ::  CDN: 14  NBLKS:  5  AVG_ROW_LEN:  37
  COLUMN:     DEPTNO(NUMBER)  Col#: 8      Table: EMP   Alias: EMP
    Size: 3  NDV: 3  Nulls: 0  Density: 3.3333e-01 Min: 10  Max: 30
    No Histogram: #BKT: 1
        (1 uncompressed buckets and 2 endpoint values)

Index stats
  Index: PK_EMP COL#: 1
    TOTAL :: LVLS: 0 #LB: 1 #DK: 14 LB/K: 1 DB/K: 1 CLUF: 1 _OPTIMIZER_PERCENT_PARALLEL = 0

---

SINGLE TABLE ACCESS PATH
  TABLE: EMP Alias: EMP
    Original Card: 14 Rounded Card: 14 Computed Card: 14.00   Access Path: table-scan Resc: 3 Resp: 3   BEST_CST: 3.02 PATH: 2 Degree: 1

---

SINGLE TABLE ACCESS PATH
  TABLE: DEPT Alias: DEPT
    Original Card: 4 Rounded Card: 4 Computed Card: 4.00   Access Path: table-scan Resc: 3 Resp: 3   BEST_CST: 3.02 PATH: 2 Degree: 1

---

OPTIMIZER STATISTICS AND COMPUTATIONS

---

GENERAL PLANS

---

Join order[1]: DEPT[DEPT]#0 EMP[EMP]#1 Now joining: EMP[EMP]#1 *******
NL Join
  Outer table: cost: 3 cdn: 4 rcz: 13 resp: 3   Inner table: EMP Alias: EMP
    Access Path: table-scan Resc: 2
    Join: Resc: 9 Resp: 9
  Best NL cost: 9 resp: 9
Join cardinality: 14 = outer (4) * inner (14) * sel (2.5000e-01) [flag=0]

I want to know where the Best NL cost come from? And how can we get the '9' here?

Thanks in advance. Received on Fri May 21 2004 - 04:25:53 CDT