Is there anyone having aclue
________________________________
发件人: 刘 Qinliu
发送时间: 2017年1月10日 11:06
收件人: 'ORACLE-L'
主题: How is the Nested-Loop cost caculated in the 10053 trace?
I am reading 10053 trace Chapter of the Cost-Based Fundamentals.
I can't figure out how the nested loop cost .
db:11.2.0.4
Please look at the red annotation in the trace file .Thanks alot
the attachment is the full 10053 trace file .
The following is the 10053 trace :
1538 Access path analysis for PARENT
1539 ***************************************
1540 SINGLE TABLE ACCESS PATH
1541 Single Table Cardinality Estimation for PARENT[P]
1542 Column (#4): SMALL_NUM_P(
1543 AvgLen: 4 NDV: 2000 Nulls: 0 Density: 0.000500 Min: 0 Max: 1999
1544 Table: PARENT Alias: P
1545 Card: Original: 10000.000000 Rounded: 110 Computed: 110.05 Non Ad
1546 Access Path: TableScan
1547 Cost: 631.09 Resp: 631.09 Degree: 0
1548 Cost_io: 627.00 Cost_cpu: 20438566
1549 Resp_io: 627.00 Resp_cpu: 20438566
1550 Best:: AccessPath: TableScan
1551 Cost: 631.09 Degree: 1 Resp: 631.09 Card: 110.05 Bytes: 0
1552
1553 Access path analysis for CHILD
1554 ***************************************
1555 SINGLE TABLE ACCESS PATH
1556 Single Table Cardinality Estimation for CHILD[C]
1557 Column (#5): SMALL_NUM_C(
1558 AvgLen: 4 NDV: 10000 Nulls: 0 Density: 0.000100 Min: 0 Max: 9999
1559 Table: CHILD Alias: C
1560 Card: Original: 40000.000000 Rounded: 68 Computed: 68.01 Non Adju
1561 Access Path: TableScan
1562 Cost: 2517.49 Resp: 2517.49 Degree: 0
1563 Cost_io: 2501.00 Cost_cpu: 82458964
1564 Resp_io: 2501.00 Resp_cpu: 82458964
1565 Best:: AccessPath: TableScan
1566 Cost: 2517.49 Degree: 1 Resp: 2517.49 Card: 68.01 Bytes: 0
1571 OPTIMIZER STATISTICS AND COMPUTATIONS
1572 ***************************************
1573 GENERAL PLANS
1574 ***************************************
1575 Considering cardinality-based initial join order.
1576 Permutations for Starting Table :0
1577 Join order[1]: CHILD[C]#0 PARENT[P]#1 GRANDPARENT[GP]#2 GREATGRANDPA
1578
1579 ***************Here,When CHILD table joins the PARENT using nested loop,
--I assume the cost comes like this:
--the Card of CHILD (68)*The Cost of tablesacan of PARENT(631) + The Cost of
CHILD(2517)=45425
--the Cost I caculated 45425 does not match the 45302, and I can't figureout
how 45302 comes from.
1580 Now joining: PARENT[P]#1
1581 ***************
1582 NL Join
1583 Outer table: Card: 68.01 Cost: 2517.49 Resp: 2517.49 Degree: 1 Byt
1584 Access path analysis for PARENT
1585 Inner table: PARENT Alias: P
1586 Access Path: TableScan
1587 NL Join: Cost: 45302.44 Resp: 45302.44 Degree: 1
1588 Cost_io: 45002.00 Cost_cpu: 1502199429
1589 Resp_io: 45002.00 Resp_cpu: 1502199429
