Re: cost

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 05 Apr 2004 12:14:40 -0600
Message-id: <4071A210.4A02E93D@sun.com>

A 10053 trace indicates that the NL plan was costed, but was discarded. Sure sounds like a bug to me.

I'm not using the exact structure as Cary & Karen's, but I used the demobld script to create the table (I added the pk to the create table commands).

Here's the 10053 output (parts discarded for readability) :

GENERAL PLANS

Join order[1]: DEPT [ D] EMP [ E]
Now joining: EMP [ E] *******
NL Join
  Outer table: cost: 2 cdn: 1 rcz: 11 resp: 2   Inner table: EMP
    Access path: tsc Resc: 2
    Join: Resc: 4 Resp: 4
Join cardinality: 5 = outer (1) * inner (14) * sel (3.3333e-01) [flag=0]
  Best NL cost: 4 resp: 4

>>>> Here the NL cost is calculated as 4. Sounds great, just what Karen has seen.

HA Join
  Outer table:
    resc: 2 cdn: 1 rcz: 11 deg: 1 resp: 2   Inner table: EMP
    resc: 2 cdn: 14 rcz: 7 deg: 1 resp: 2     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 1 Deg: 1

      hash_area:  2048 (max=2048)  buildfrag: 
2049                probefrag:   1 ppasses:    2

Hash join Resc: 5 Resp: 5
Join result: cost: 5 cdn: 5 rcz: 18

>>>> Here the HA join is also costed correctly.

Best so far: TABLE#: 0  CST:          2  CDN:          1 
BYTES:         11
Best so far: TABLE#: 1  CST:          5  CDN:          5 
BYTES:         90

>>>> What happened to the NL join?

Join order[2]: EMP [ E] DEPT [ D]
Now joining: DEPT [ D] *******
NL Join
  Outer table: cost: 2 cdn: 14 rcz: 7 resp: 2   Inner table: DEPT
    Access path: tsc Resc: 2
    Join: Resc: 30 Resp: 30
  Access path: index (unique)

      Index: PK_DEPT
  TABLE: DEPT
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00     Join: resc: 16 resp: 16
  Access path: index (eq-unique)

      Index: PK_DEPT
  TABLE: DEPT
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00     Join: resc: 16 resp: 16
Join cardinality: 5 = outer (14) * inner (1) * sel (3.3333e-01) [flag=0]
  Best NL cost: 16 resp: 16
HA Join
  Outer table:
    resc: 2 cdn: 14 rcz: 7 deg: 1 resp: 2   Inner table: DEPT
    resc: 2 cdn: 1 rcz: 11 deg: 1 resp: 2     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 1 Deg: 1

      hash_area:  2048 (max=2048)  buildfrag: 
2049                probefrag:   1 ppasses:    2

Hash join Resc: 5 Resp: 5
Final:
CST: 5 CDN: 5 RSC: 5 RSP: 5 BYTES: 90 IO-RSC: 5 IO-RSP: 5 CPU-RSC: 0 CPU-RSP: 0 Cary Millsap wrote:
>
> Mark,
>
> Unfortunately, not *always*. :( See Karen Morton's test case below my
> sig.
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Apr 05 2004 - 13:11:11 CDT