Re: cost
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 05 Apr 2004 12:14:40 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
