Re: How is the Nested-Loop cost caculated in the 10053 trace?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 11 Jan 2017 10:56:24 +0000
Message-ID: <MMXP123MB09117D2D0436AFE6DF497887A5660_at_MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of �� Qinliu <Ivyliu_99_at_hotmail.com> 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�^ Received on Wed Jan 11 2017 - 11:56:24 CET