OraFAQ Forum: Performance Tuning » Help: Tuning Query

Home » RDBMS Server » Performance Tuning » Help: Tuning Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help: Tuning Query [message #247964]

Wed, 27 June 2007 11:42

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

Attachment: db2_ora_21415.txt
(Size: 11.16KB, Downloaded 1117 times)

Report message to a moderator

Re: Help: Tuning Query [message #247965 is a reply to message #247964]

Wed, 27 June 2007 11:48

BlackSwan
Messages: 26766
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)!

Report message to a moderator

Re: Help: Tuning Query [message #248013 is a reply to message #247964]

Wed, 27 June 2007 15:30

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

Report message to a moderator

Previous Topic:	cache hit ratio
Next Topic:	Help- Tuning a Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 00:50:06 CDT 2024