Home » RDBMS Server » Performance Tuning » Help: Tuning Query
Help: Tuning Query [message #247964] |
Wed, 27 June 2007 11:42 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orasaket
Messages: 70 Registered: November 2006
|
Member |
|
|
Hi,
I am using Oracle9i R2 on Linux
Please refer following query
Select cpm1.strpolnbr,
cpm1.STRLASTNAME,cpm1.strfirstname ,cpm1.amnt,cpm1.STRPAYMENTID ,cpm1.strAcctId ,cpm1.descr,cpm1.STRCLIENTCD ,cpm1.STRNEWICNBR,cpm1.DTWKLYPRDTO,
cpm1.DTWKLYPRDFROM , DTWKLYPRDTOone ,cpm1.amnt1,cpm2.STRPOLNBR TP,cpm2.strclientcd
From
( SELECT
NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) strpolnbr,
--cpmthird.strpolnbr payee,
STRLASTNAME,substr(strfirstname,1,1) || substr(strmiddlename,1,1) strfirstname ,
SUM(NVL(cpd.dtotPaidAmnt+DTOTADJUSTAMNT,0)) amnt,
cpm.STRPAYMENTID STRPAYMENTID ,cpd.strAcctId strAcctId ,'Unreconciled' descr,
cpm.STRCLIENTCD STRCLIENTCD ,ccm.STRNEWICNBR STRNEWICNBR
,fgrh.DTMNTHLYPRDTO DTWKLYPRDTO,
fgrh.DTMNTHLYPRDFROM DTWKLYPRDFROM ,fgrh.DTMNTHLYPRDTO DTWKLYPRDTOone,
sum(nvl(cpd.DTOTDUEAMNT,0)) amnt1
FROM
COM_POL_DUE cpd,
COM_POLICY_M cpm,
com_client_m ccm,
com_param_system_m cpsm,
(select distinct NRECOREFSEQNBR , stracctid from fin_group_reco_dtl) fgrd, fin_group_reco_hdr fgrh,
com_policy_m cpmthird
WHERE
cpd.strPolNbr = cpm.strPolNbr AND
-- NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) = cpm.strPolNbr AND
(cpmthird.STRthirdpartyPOLNBR=cpm.strPolNbr OR (cpmthird.STRthirdpartyPOLNBR IS NULL AND cpmthird.strpolnbr=cpm.strpolnbr)) AND
cpm.strClientCd = ccm.strClientCd AND
cpd.lgrprefnbr in (Select lpoldueseq from com_pol_due c where c.strpolnbr = cpd.stracctid and c.dtduefrom=to_date('01-may-07','dd-mon-yy') and c.dtdueto=to_date('31-may-07','dd-mon-yy') and cpd.nfinduetype not in(102,106)) AND
LGRPRECOSEQNBR IS NULL AND
NVL(NCHGSTATCD,4) not in (4,5) AND
cpsm.NPARAMCD=cpd.NCHGSTATCD AND
IPARAMTYPECD=3007 AND
fgrd.STRACCTID=cpd.STRACCTID AND
NVL(cpm.npolstatcd,0) != 12 AND
fgrh.NRECOREFSEQNBR=:seqnbr AND
fgrd.NRECOREFSEQNBR=fgrh.NRECOREFSEQNBR
GROUP BY NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) ,
STRLASTNAME,substr(strfirstname,1,1) || substr(strmiddlename,1,1) ,
cpm.STRPAYMENTID ,cpd.strAcctId ,'Unreconciled' ,
cpm.STRCLIENTCD ,ccm.STRNEWICNBR
,fgrh.DTMNTHLYPRDTO ,
fgrh.DTMNTHLYPRDFROM,fgrh.DTMNTHLYPRDTO
UNION
SELECT
NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) strpolnbr,
--cpmthird.strpolnbr payee,
STRLASTNAME,substr(strfirstname,1,1) || substr(strmiddlename,1,1) strfirstname ,
SUM(NVL(cpd.dtotPaidAmnt+DTOTADJUSTAMNT,0)) amnt,
cpm.STRPAYMENTID STRPAYMENTID ,cpd.strAcctId strAcctId ,'Unreconciled' descr,
cpm.STRCLIENTCD STRCLIENTCD ,ccm.STRNEWICNBR STRNEWICNBR
,fgrh.DTWKLYPRDTO DTWKLYPRDTO,
fgrh.DTWKLYPRDFROM DTWKLYPRDFROM ,fgrh.DTWKLYPRDTO DTWKLYPRDTOone ,
sum(nvl(cpd.DTOTDUEAMNT,0)) amnt1
FROM
COM_POL_DUE cpd,
COM_POLICY_M cpm,
com_client_m ccm,
com_param_system_m cpsm,
(select distinct NRECOREFSEQNBR , stracctid from fin_group_reco_dtl) fgrd, fin_group_reco_hdr fgrh,
com_policy_m cpmthird
WHERE
cpd.strPolNbr = cpm.strPolNbr AND
-- NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) = cpm.strPolNbr AND
(cpmthird.STRthirdpartyPOLNBR=cpm.strPolNbr OR (cpmthird.STRthirdpartyPOLNBR IS NULL AND cpmthird.strpolnbr=cpm.strpolnbr)) AND
cpm.strClientCd = ccm.strClientCd AND
cpd.lgrprefnbr in (Select lpoldueseq from com_pol_due c where c.strpolnbr = cpd.stracctid and c.dtduefrom=to_date('01-may-07','dd-mon-yy') and c.dtdueto=to_date('31-may-07','dd-mon-yy') and cpd.nfinduetype not in(102,106)) AND
LGRPRECOSEQNBR IS NULL AND
NVL(NCHGSTATCD,4) not in (4,5) AND
cpsm.NPARAMCD=cpd.NCHGSTATCD AND
IPARAMTYPECD=3007 AND
fgrd.STRACCTID=cpd.STRACCTID AND
NVL(cpm.npolstatcd,0) != 12 AND
fgrh.NRECOREFSEQNBR=:seqnbr AND
fgrd.NRECOREFSEQNBR=fgrh.NRECOREFSEQNBR --AND
--1=2
GROUP BY NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) ,
STRLASTNAME,substr(strfirstname,1,1) || substr(strmiddlename,1,1) ,
cpm.STRPAYMENTID ,cpd.strAcctId ,'Unreconciled' ,
cpm.STRCLIENTCD ,ccm.STRNEWICNBR
,fgrh.DTWKLYPRDTO ,
fgrh.DTWKLYPRDFROM ,fgrh.DTWKLYPRDTO
) cpm1
,
com_policy_m cpm2
Where
cpm1.strpolnbr =cpm2.STRthirdpartyPOLNBR(+)
and cpm1.amnt !=0
order by cpm1.strlastname
When the value for bind variable :seqnbr is passed as 54 (account with less data i.e. 125 records) the query performs well
however while value is passed as 83 (account with around 4000 records), the query takes a long time.
Trace file for session with :seqnbr=54 is db2_ora_21415.txt is attached herewith
how can i reduced time with seqnbr with large number of records?
Please advice
Thanks and Regards,
OraSaket
|
|
|
|
Re: Help: Tuning Query [message #248013 is a reply to message #247964] |
Wed, 27 June 2007 15:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
A long query, without either table/index definitions.
Without explain and without complete TKPROF.
What do you expect?
Post complete TKPROF (with EXPLAIN and numbers of selected rows).
Anyway. You have
db file sequential read 99931 0.20 493.59
Which means that you are eiter using non-selective or incorrect indexes and the query waits on index-related reads.
HTH.
Michael
|
|
|
Goto Forum:
Current Time: Sat Aug 17 04:06:13 CDT 2024
|