Re: About CBO cost in oracle10g

Is this the standard emp/dept tables
from the 10g demo, and have you
analyzed them ?

Can you email the whole trace file,
I can't emulate your result at present

It is possible that with the nested loop full tablescan Oracle 10g has an algorithm that assumes that subsequent scans of
the inner table have a cache benefit, and therefore lower cost.

What do the content of the CPU_COST
and IO_COST columns of the plan_table
look like when you hint the NL access path ?

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

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

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"eygle" <eygle_at_itpub.net> wrote in message
news:f9bc34d.0405210125.3ee8451e_at_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.