A couple of points to consider:
The book went up to 10.1.0.4, and Oracle keeps tweaking the code to improve the
algorithms and allow for new ideas.
The difference (in this example) between actual and expected is about 0.25%,
which is pretty irrelevant until you come across an example with a much larger
where it's much larger
The difference seems to come from doing the arithmetic with the I/O portion of
the cost:
Possibly there's a small allowance for the fact that part of the tablescan is
the space management blocks the first time around with an assumption that they
will be cached (or possibly the relevant details kept in local memory) on
subsequent scans. A possible test would be to see if there's a difference
between running the test with freelist management compared to automatic segment
space management.
You could also search Randolf Geist's blog
http://oracle-randolf.blogspot.co.uk/ ; over the last few years he's published
several examples of how the cost calculation varies from the basic pattern.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of 刘 Qinliu <Ivyliu_99@xxxxxxxxxxx>
Sent: 11 January 2017 01:51:43
To: 'ORACLE-L'
Subject: 答复: How is the Nested-Loop cost caculated in the 10053 trace?
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
��i��0龙�zX�妒+�� n��{�+i�^
