Urgent Request
From: Smitty <marksmithy69_at_hotmail.com>
Date: Mon, 25 Feb 2008 17:16:12 -0800 (PST)
Message-ID: <e3cf774b-d243-49e4-9436-48cca738879e@e6g2000prf.googlegroups.com>
Date: Mon, 25 Feb 2008 17:16:12 -0800 (PST)
Message-ID: <e3cf774b-d243-49e4-9436-48cca738879e@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') ANDReceived on Mon Feb 25 2008 - 19:16:12 CST
(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