��: How is the Nested-Loop cost caculated in the 10053 trace?
Date: Wed, 11 Jan 2017 01:51:43 +0000
Message-ID: <SG2PR01MB0687794EAE407408AFFBE88A86660_at_SG2PR01MB0687.apcprd01.prod.exchangelabs.com>
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: 01552
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 11 2017 - 02:51:43 CET