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_prms
n bcprsnlndn5_ ON
this_.prsn_crsng_guid = bcprsnlndn5_.prsn_crsng_gu
id
WHERE ((bcprsndcmn1_.dcmnt_nmbr = 456900061 AND
bcprsndcmn1_.dcmnt_type_co
de = 1 AND
bcprsndcmn1_.dcmnt_expry_d
ate = to_date('24-FEB-2016','DD-
MON-YYYY') AND
(bcprsndcmn1_.dcmnt_isd_cn
try_code = 234 OR
bcprsndcmn1_.dcmnt_isd_cnt
ry_code IS NULL) AND
(bcprsndcmn1_.dcmnt_sub_ty
pe_code = 1 OR
bcprsndcmn1_.dcmnt_sub_typ
e_code IS NULL) AND
(bcprsndcmn1_.ntnlty_code IS NULL) AND
(bcprsndcmn1_.dcmnt_isd_da
te 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
Start Free Trial
