Re: Intrepretation of 10053 trace

• Post for FREE via your newsreader at post.usenet.com ****

Hi

Without the complete trace file it's difficult to understand what's happening... therefore, I suggest you to post the *whole* tracefile and the definition of the tables/indexes.

Chris

"Pk" <pbhandari2050_at_yahoo.com> wrote in message news:fa93fdc3.0409201324.70dba4e3_at_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?

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=