Home » RDBMS Server » Performance Tuning » Help: Tuning Query
Help: Tuning Query [message #247964] Wed, 27 June 2007 11:42 Go to next message
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 #247965 is a reply to message #247964] Wed, 27 June 2007 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch      902      0.01       0.06          0          0          0       13515
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      902      0.01       0.06          0          0          0       13515



IMO, 0.06 seconds is NOT a long time.

Without useful evidence, You're On Your Own (YOYO)!
Re: Help: Tuning Query [message #248013 is a reply to message #247964] Wed, 27 June 2007 15:30 Go to previous message
michael_bialik
Messages: 611
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
Previous Topic: cache hit ratio
Next Topic: Help- Tuning a Query
Goto Forum:
  


Current Time: Sat Dec 10 16:33:17 CST 2016

Total time taken to generate the page: 0.08436 seconds