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>

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_.*,

      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 =
      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-

(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
Received on Mon Feb 25 2008 - 19:16:12 CST

Original text of this message