Re: query performance

From: Paul Quenneville <paulq_at_home.com>
Date: Thu, 11 Oct 2001 20:37:36 GMT
Message-ID: <kqnx7.45012$5h5.20218279@news3.rdc2.on.home.com>

Are you sure you have the same pk, fk and indexes created and enabled in both instances ?

"Jim Poe" <jpoe_at_fulcrumit.com> wrote in message news:9q4cj0$htq_at_dispatch.concentric.net...
> This is the whole explain plan for TEST and DEVL.
>
> TEST
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=280908 Card=359135
Bytes=181363175)
> SORT (UNIQUE) (Cost=280908 Card=359135 Bytes=181363175)
> NESTED LOOPS (Cost=135732 Card=359135 Bytes=181363175)
> NESTED LOOPS (OUTER) (Cost=48138 Card=14599 Bytes=7328698)
> NESTED LOOPS (OUTER) (Cost=33539 Card=14599 Bytes=7241104)
> NESTED LOOPS (OUTER) (Cost=18940 Card=14599 Bytes=6992921)
> NESTED LOOPS (OUTER) (Cost=18940 Card=14599 Bytes=6759337)
> NESTED LOOPS (OUTER) (Cost=11323 Card=2539 Bytes=868338)
> NESTED LOOPS (OUTER) (Cost=8784 Card=2539 Bytes=825175)
> NESTED LOOPS (OUTER) (Cost=6514 Card=2270 Bytes=719590)
> NESTED LOOPS (OUTER) (Cost=6514 Card=2270
Bytes=660570)
> NESTED LOOPS (OUTER) (Cost=6514 Card=2270
> Bytes=601550)
> NESTED LOOPS (OUTER) (Cost=6514 Card=2270
> Bytes=542530)
> NESTED LOOPS (OUTER) (Cost=4244 Card=2270
> Bytes=483510)
> NESTED LOOPS (OUTER) (Cost=1974 Card=2270
> Bytes=424490)
> NESTED LOOPS (OUTER) (Cost=663 Card=437
> Bytes=77349)
> NESTED LOOPS (OUTER) (Cost=321 Card=114
> Bytes=19038)
> NESTED LOOPS (OUTER) (Cost=249 Card=24
> Bytes=3768)
> NESTED LOOPS (OUTER) (Cost=225 Card=24
> Bytes=3576)
> NESTED LOOPS (OUTER) (Cost=201
Card=24
> Bytes=3408)
> NESTED LOOPS (OUTER) (Cost=177
> Card=24 Bytes=3216)
> NESTED LOOPS (OUTER) (Cost=177
> Card=24 Bytes=3048)
> NESTED LOOPS (OUTER) (Cost=177
> Card=24 Bytes=2832)
> NESTED LOOPS (OUTER)
(Cost=177
> Card=24 Bytes=2664)
> NESTED LOOPS (OUTER)
(Cost=1
> 77 Card=24 Bytes=2520)
> MERGE JOIN (Cost=9
Card=24
> Bytes=2136)
> SORT (JOIN) (Cost=7
> Card=19 Bytes=684)
> HASH JOIN (OUTER)
> (Cost=5 Card=19 Bytes=684)
> TABLE ACCESS
(FULL)
> OF SUBM (Cost=1 Card=19 Bytes=494)
> VIEW (Cost=3
Card=11
> Bytes=110)
> HASH JOIN
(OUTER)
> (Cost=3 Card=11 Bytes=737)
> NESTED LOOPS
> (Cost=1 Card=11 Bytes=572)
> TABLE ACCESS
> (FULL) OF OFFICE (Cost=1 Card=83 Bytes=3984)
> INDEX
(UNIQUE
> SCAN) OF PDCR_PK (UNIQUE)
> TABLE ACCESS
> (FULL) OF OFFICE_AKA (Cost=1 Card=25 Bytes=375)
> SORT (JOIN) (Cost=3
> Card=24 Bytes=1272)
> TABLE ACCESS (FULL)
OF
> QUOTE (Cost=1 Card=24 Bytes=1272)
> VIEW (Cost=7 Card=5
> Bytes=80)
> HASH JOIN (Cost=7
Card=5
> Bytes=235)
> HASH JOIN (Cost=5
> Card=17 Bytes=663)
> HASH JOIN (Cost=3
> Card=17 Bytes=527)
> TABLE ACCESS
> (FULL) OF CTAC_AKA (Cost=1 Card=17 Bytes=153)
> TABLE ACCESS
> (FULL) OF CTAC (Cost=1 Card=133 Bytes=2926)
> TABLE ACCESS
(FULL)
> OF CTAC_ROLE (Cost=1 Card=135 Bytes=1080)
> TABLE ACCESS (FULL)
OF
> QUOTE_ROLE (Cost=1 Card=40 Bytes=320)
> INDEX (UNIQUE SCAN) OF
> PROC_STATUS_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF
> POL_STATUS_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF PROG_PK
> (UNIQUE)
> INDEX (UNIQUE SCAN) OF LINE_PK
> (UNIQUE)
> TABLE ACCESS (FULL) OF QUOTE_CONTR
> (Cost=1 Card=19 Bytes=152)
> TABLE ACCESS (FULL) OF QUOTE_CARR
> (Cost=1 Card=17 Bytes=119)
> TABLE ACCESS (BY INDEX ROWID) OF
> MKT_CARR (Cost=1 Card=13 Bytes=104)
> INDEX (UNIQUE SCAN) OF MKT_CARR_PK
> (UNIQUE)
> VIEW (Cost=3 Card=19 Bytes=190)
> HASH JOIN (OUTER) (Cost=3 Card=19
> Bytes=1273)
> NESTED LOOPS (Cost=1 Card=10
> Bytes=520)
> TABLE ACCESS (FULL) OF OFFICE
> (Cost=1 Card=83 Bytes=3984)
> INDEX (UNIQUE SCAN) OF BRANCH_PK
> (UNIQUE)
> TABLE ACCESS (FULL) OF OFFICE_AKA
> (Cost=1 Card=25 Bytes=375)
> VIEW (Cost=3 Card=23 Bytes=230)
> HASH JOIN (OUTER) (Cost=3 Card=23
> Bytes=1541)
> NESTED LOOPS (Cost=1 Card=14
Bytes=728)
> TABLE ACCESS (FULL) OF OFFICE
(Cost=1
> Card=83 Bytes=3984)
> INDEX (UNIQUE SCAN) OF CARR_PK
> (UNIQUE)
> TABLE ACCESS (FULL) OF OFFICE_AKA
> (Cost=1 Card=25 Bytes=375)
> VIEW (Cost=3 Card=44 Bytes=440)
> HASH JOIN (OUTER) (Cost=3 Card=44
> Bytes=2948)
> NESTED LOOPS (Cost=1 Card=23 Bytes=1196)
> TABLE ACCESS (FULL) OF OFFICE (Cost=1
> Card=83 Bytes=3984)
> INDEX (UNIQUE SCAN) OF MKT_PK (UNIQUE)
> TABLE ACCESS (FULL) OF OFFICE_AKA
(Cost=1
> Card=25 Bytes=375)
> TABLE ACCESS (BY INDEX ROWID) OF OFFICE
(Cost=1
> Card=83 Bytes=2158)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF OFFICE (Cost=1
> Card=83 Bytes=2158)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF OFFICE_ADDR (Cost=1
> Card=85 Bytes=680)
> INDEX (RANGE SCAN) OF OFFICE_ADDR_OFFICE_FK
(NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF ADDR (Cost=1 Card=106
> Bytes=1802)
> INDEX (UNIQUE SCAN) OF ADDR_PK (UNIQUE)
> VIEW (Cost=3 Card=69 Bytes=8349)
> HASH JOIN (OUTER) (Cost=3 Card=69 Bytes=4623)
> NESTED LOOPS (Cost=1 Card=36 Bytes=1872)
> TABLE ACCESS (FULL) OF OFFICE (Cost=1 Card=83
> Bytes=3984)
> INDEX (UNIQUE SCAN) OF INSD_PK (UNIQUE)
> TABLE ACCESS (FULL) OF OFFICE_AKA (Cost=1 Card=25
> Bytes=375)
> INDEX (UNIQUE SCAN) OF ST_PK (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF ADDR (Cost=1 Card=106
Bytes=1802)
> INDEX (UNIQUE SCAN) OF ADDR_PK (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF ST (Cost=1 Card=104 Bytes=624)
> INDEX (UNIQUE SCAN) OF ST_PK (UNIQUE)
> VIEW
> UNION-ALL
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
>
>
>
>
> DEVL
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=7001 Card=4969 Bytes=5371489)
> SORT (UNIQUE) (Cost=4198 Card=4969 Bytes=5371489)
> NESTED LOOPS (Cost=1395 Card=4969 Bytes=5371489)
> HASH JOIN (OUTER) (Cost=183 Card=202 Bytes=217756)
> HASH JOIN (OUTER) (Cost=164 Card=202 Bytes=212302)
> NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=204828)
> NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=194324)
> NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=183820)
> NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=173316)
> HASH JOIN (OUTER) (Cost=145 Card=202 Bytes=169276)
> HASH JOIN (OUTER) (Cost=127 Card=202 Bytes=165438)
> HASH JOIN (OUTER) (Cost=112 Card=202 Bytes=160186)
> HASH JOIN (OUTER) (Cost=97 Card=202 Bytes=154934)
> NESTED LOOPS (OUTER) (Cost=82 Card=202
> Bytes=149682)
> NESTED LOOPS (OUTER) (Cost=82 Card=202
> Bytes=145642)
> NESTED LOOPS (OUTER) (Cost=82 Card=202
> Bytes=140188)
> HASH JOIN (OUTER) (Cost=82 Card=202
> Bytes=134734)
> HASH JOIN (OUTER) (Cost=67 Card=202
> Bytes=130896)
> HASH JOIN (OUTER) (Cost=52 Card=202
> Bytes=127058)
> HASH JOIN (Cost=33 Card=202
> Bytes=122008)
> NESTED LOOPS (OUTER) (Cost=25
> Card=82 Bytes=35014)
> HASH JOIN (OUTER) (Cost=25
Card=82
> Bytes=30586)
> HASH JOIN (OUTER) (Cost=21
> Card=82 Bytes=27552)
> HASH JOIN (OUTER) (Cost=17
> Card=82 Bytes=25420)
> HASH JOIN (OUTER) (Cost=13
> Card=82 Bytes=21156)
> HASH JOIN (OUTER)
(Cost=9
> Card=82 Bytes=16892)
> HASH JOIN (OUTER)
> (Cost=5 Card=82 Bytes=6232)
> TABLE ACCESS (FULL)
OF
> SUBM (Cost=1 Card=82 Bytes=4674)
> VIEW (Cost=3 Card=67
> Bytes=1273)
> HASH JOIN (OUTER)
> (Cost=3 Card=67 Bytes=10452)
> NESTED LOOPS
> (Cost=1 Card=67 Bytes=6968)
> TABLE ACCESS
> (FULL) OF OFFICE (Cost=1 Card=82 Bytes=7462)
> INDEX (UNIQUE
> SCAN) OF PDCR_PK (UNIQUE)
> TABLE ACCESS
> (FULL) OF OFFICE_AKA (Cost=1 Card=82 Bytes=4264)
> VIEW (Cost=3 Card=67
> Bytes=8710)
> HASH JOIN (OUTER)
> (Cost=3 Card=67 Bytes=10452)
> NESTED LOOPS
(Cost=1
> Card=67 Bytes=6968)
> TABLE ACCESS
> (FULL) OF OFFICE (Cost=1 Card=82 Bytes=7462)
> INDEX (UNIQUE
> SCAN) OF INSD_PK (UNIQUE)
> TABLE ACCESS
(FULL)
> OF OFFICE_AKA (Cost=1 Card=82 Bytes=4264)
> TABLE ACCESS (FULL) OF
> OFFICE (Cost=1 Card=82 Bytes=4264)
> TABLE ACCESS (FULL) OF
> OFFICE (Cost=1 Card=82 Bytes=4264)
> TABLE ACCESS (FULL) OF
> OFFICE_ADDR (Cost=1 Card=82 Bytes=2132)
> TABLE ACCESS (FULL) OF ADDR
> (Cost=1 Card=82 Bytes=3034)
> INDEX (UNIQUE SCAN) OF ST_PK
> (UNIQUE)
> TABLE ACCESS (FULL) OF QUOTE
(Cost=1
> Card=246 Bytes=43542)
> VIEW (Cost=7 Card=45 Bytes=1125)
> HASH JOIN (Cost=7 Card=45
> Bytes=7515)
> HASH JOIN (Cost=5 Card=55
> Bytes=6325)
> HASH JOIN (Cost=3 Card=67
> Bytes=3484)
> TABLE ACCESS (FULL) OF
> QUOTE_ROLE (Cost=1 Card=82 Bytes=2132)
> TABLE ACCESS (FULL) OF
> CTAC_ROLE (Cost=1 Card=82 Bytes=2132)
> TABLE ACCESS (FULL) OF CTAC
> (Cost=1 Card=82 Bytes=5166)
> TABLE ACCESS (FULL) OF CTAC_AKA
> (Cost=1 Card=82 Bytes=4264)
> VIEW (Cost=3 Card=67 Bytes=1273)
> HASH JOIN (OUTER) (Cost=3 Card=67
> Bytes=10452)
> NESTED LOOPS (Cost=1 Card=67
> Bytes=6968)
> TABLE ACCESS (FULL) OF OFFICE
> (Cost=1 Card=82 Bytes=7462)
> INDEX (UNIQUE SCAN) OF BRANCH_PK
> (UNIQUE)
> TABLE ACCESS (FULL) OF OFFICE_AKA
> (Cost=1 Card=82 Bytes=4264)
> VIEW (Cost=3 Card=67 Bytes=1273)
> HASH JOIN (OUTER) (Cost=3 Card=67
> Bytes=10452)
> NESTED LOOPS (Cost=1 Card=67
> Bytes=6968)
> TABLE ACCESS (FULL) OF OFFICE
> (Cost=1 Card=82 Bytes=7462)
> INDEX (UNIQUE SCAN) OF MKT_PK
> (UNIQUE)
> TABLE ACCESS (FULL) OF OFFICE_AKA
> (Cost=1 Card=82 Bytes=4264)
> INDEX (UNIQUE SCAN) OF POL_STATUS_PK
> (UNIQUE)
> INDEX (UNIQUE SCAN) OF PROC_STATUS_PK
(UNIQUE)
> INDEX (UNIQUE SCAN) OF PROG_PK (UNIQUE)
> TABLE ACCESS (FULL) OF QUOTE_CONTR (Cost=1
Card=82
> Bytes=2132)
> TABLE ACCESS (FULL) OF QUOTE_CARR (Cost=1 Card=82
> Bytes=2132)
> TABLE ACCESS (FULL) OF MKT_CARR (Cost=1 Card=82
> Bytes=2132)
> VIEW (Cost=3 Card=67 Bytes=1273)
> HASH JOIN (OUTER) (Cost=3 Card=67 Bytes=10452)
> NESTED LOOPS (Cost=1 Card=67 Bytes=6968)
> TABLE ACCESS (FULL) OF OFFICE (Cost=1 Card=82
> Bytes=7462)
> INDEX (UNIQUE SCAN) OF CARR_PK (UNIQUE)
> TABLE ACCESS (FULL) OF OFFICE_AKA (Cost=1 Card=82
> Bytes=4264)
> INDEX (UNIQUE SCAN) OF LINE_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
> TABLE ACCESS (FULL) OF ADDR (Cost=1 Card=82 Bytes=3034)
> TABLE ACCESS (FULL) OF ST (Cost=1 Card=82 Bytes=2214)
> VIEW
> UNION-ALL
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
> TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
>
>
> Thanks
>
>
> --
> Jim Poe (jpoe_at_fulcrumit.com)
>
>
Received on Thu Oct 11 2001 - 15:37:36 CDT