Re: Urgent Request

"Smitty" <marksmithy69_at_hotmail.com> wrote in message news:e3cf774b-d243-49e4-9436-48cca738879e_at_e6g2000prf.googlegroups.com...
> Hello everyone. I am really hoping that someone can help me with this
> issue. I have two identical databases (one is a replication of the
> other), where when I run an identical query on each, one returns the
> results in .5 seconds, while the other takes 18 seconds to return the
> same result. The indexes etc. are all identical on each database.
> When we ran a trace, we noticed that the disk gets hit only once on
> the 'quick' database, but get hit over 10000 times on the 'slow'
> database to return the same number of rows. Does anyone have any idea
> what could be the issue. I am attaching the query below. Thank you
> very much.
>
> SELECT this_.*,
> bcprsndcmn1_.*,
> bctrnsprtc3_.*,
> bctrnsprt4_.*,
> bcprsn2_.*,
> bcprsnlndn5_.*
> FROM dbowner.bc_prsn_crsng this_
> INNER JOIN dbowner.bc_prsn_dcmnt bcprsndcmn1_ ON
> this_.dcmnt_guid = bcprsndcmn1_.dcmnt_guid
> INNER JOIN dbowner.bc_trnsprt_crsng bctrnsprtc3_ ON
> this_.trnsprt_crsng_guid = bctrnsprtc3_.trnsprt_crsng_guid
> INNER JOIN dbowner.bc_trnsprt bctrnsprt4_ ON
> bctrnsprtc3_.trnsprt_guid = bctrnsprt4_.trnsprt_guid
> INNER JOIN dbowner.bc_prsn bcprsn2_ ON this_.prsn_guid =
> bcprsn2_.prsn_guid
> FULL OUTER JOIN dbowner.bc_prsn_lndng_prmsn bcprsnlndn5_ ON
> this_.prsn_crsng_guid = bcprsnlndn5_.prsn_crsng_guid
> WHERE ((bcprsndcmn1_.dcmnt_nmbr = 456900061 AND
> bcprsndcmn1_.dcmnt_type_code = 1 AND
> bcprsndcmn1_.dcmnt_expry_date = to_date('24-FEB-2016','DD-
> MON-YYYY') AND
> (bcprsndcmn1_.dcmnt_isd_cntry_code = 234 OR
> bcprsndcmn1_.dcmnt_isd_cntry_code IS NULL) AND
> (bcprsndcmn1_.dcmnt_sub_type_code = 1 OR
> bcprsndcmn1_.dcmnt_sub_type_code IS NULL) AND
> (bcprsndcmn1_.ntnlty_code IS NULL) AND
> (bcprsndcmn1_.dcmnt_isd_date IS NULL)) OR
> (bcprsn2_.gvn_name = 'YASMIN' AND bcprsn2_.srnm =
> 'WARLAND' AND
> bcprsn2_.gndr_code = 2 AND bcprsn2_.brth_date =
> '19570805'))
> ORDER BY this_.date_crtd DESC

I am guessing that the slow one has tables and indexes which are not analyzed. I am making a WAG since you haven't told us much. (eg version , explain plans in both db's etc.) Jim Received on Tue Feb 26 2008 - 01:04:34 CST