´ð¸´: How is the Nested-Loop cost caculated in the 10053 trace?

From: Áõ Qinliu <Ivyliu_99_at_hotmail.com>
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: 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 11 2017 - 02:51:43 CET