Home » RDBMS Server » Performance Tuning » analying 10053 trace
analying 10053 trace [message #65432] Mon, 20 September 2004 13:00
Prakash Bhandari
Messages: 1
Registered: September 2004
Junior Member
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 you
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?

I would appreciate anyone's feedback.

Oracle Server Enterprise Edition : 9.2.0.4

Thanks.
Previous Topic: Delete is taking time
Next Topic: Using Loader to load 4GB into a temporary table in RAM
Goto Forum:
  


Current Time: Fri Mar 29 02:20:19 CDT 2024