Intrepretation of 10053 trace

From: Pk <pbhandari2050_at_yahoo.com>
Date: 20 Sep 2004 14:24:03 -0700
Message-ID: <fa93fdc3.0409201324.70dba4e3@posting.google.com>

I am trying to analyze the 10053 trace and it doesn't make sense why the Optimizer is costing the Nested Loop this high. I would appreicate if anyone could shed any light in this:

Definition of the problem:
In the 10053 trace of a Query here are the two single table access paths:

SINGLE TABLE ACCESS PATH
TABLE: OUT_ORD_HDR ORIG CDN: 284368 ROUNDED CDN: 2393 CMPTD CDN: 2393 Access path: tsc Resc: 3083 Resp: 3083
Access path: index (scan)

Index: OUT_ORD_HDR_AK_8
TABLE: OUT_ORD_HDR
RSC_CPU: 0 RSC_IO: 211

IX_SEL: 8.4152e-03 TB_SEL: 8.4152e-03
BEST_CST: 211.00 PATH: 4 Degree: 1

---

SINGLE TABLE ACCESS PATH
TABLE: OUT_ORD_STAT ORIG CDN: 19 ROUNDED CDN: 11 CMPTD CDN: 11 Access path: tsc Resc: 2 Resp: 2
Access path: index (scan)

Index: OUT_ORD_STAT_PK
TABLE: OUT_ORD_STAT
RSC_CPU: 0 RSC_IO: 2

IX_SEL: 6.0365e-01 TB_SEL: 6.0365e-01
BEST_CST: 2.00 PATH: 4 Degree: 1
In the other part of the 10053 trace, the join orders are shown as follows: Join order[1]: OUT_ORD_STAT [OUT_ORD_STAT] OUT_ORD_HDR [OUT_ORD_HDR] CUSTOMER

[CUSTOMER]

Now joining: OUT_ORD_HDR [OUT_ORD_HDR] ******* NL Join

Outer table: cost: 2 cdn: 11 rcz: 11 resp: 2
Inner table: OUT_ORD_HDR
Access path: tsc Resc: 3083

Join: Resc: 33915 Resp: 33915
Access path: index (join index)

Index: OUT_ORD_HDR_AK_8
TABLE: OUT_ORD_HDR
RSC_CPU: 0 RSC_IO: 4131

IX_SEL: 0.0000e+00 TB_SEL: 1.1111e-01
Join (ordered NL): resc: 45443 resp: 45443 Join cardinality: 2287 = outer (11) * inner (2393) * sel (8.3333e-02)
[flag=0]

Best NL cost: 33915 resp: 33915
Everything here makes snese except the RSC_IO for the index access path. The index access path earlier was calculated as 211. See above. So then, why has the optimizer recosted it as 4131? Is there anything I am missing? Received on Mon Sep 20 2004 - 16:24:03 CDT