TKPROF: Release 9.2.0.6.0 - Production on Wed Jun 27 16:35:51 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: db2_ora_21415.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** 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,:"SYS_B_00",:"SYS_B_01") || substr(strmiddlename,:"SYS_B_02",:"SYS_B_03") strfirstname , SUM(NVL(cpd.dtotPaidAmnt+DTOTADJUSTAMNT,:"SYS_B_04")) amnt, cpm.STRPAYMENTID STRPAYMENTID ,cpd.strAcctId strAcctId ,:"SYS_B_05" descr, cpm.STRCLIENTCD STRCLIENTCD ,ccm.STRNEWICNBR STRNEWICNBR ,fgrh.DTMNTHLYPRDTO DTWKLYPRDTO, fgrh.DTMNTHLYPRDFROM DTWKLYPRDFROM ,fgrh.DTMNTHLYPRDTO DTWKLYPRDTOone, sum(nvl(cpd.DTOTDUEAMNT,:"SYS_B_06")) 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(:"SYS_B_07",:"SYS_B_08") and c.dtdueto=to_date(:"SYS_B_09",:"SYS_B_10") and cpd.nfinduetype not in(:"SYS_B_11",:"SYS_B_12")) AND LGRPRECOSEQNBR IS NULL AND NVL(NCHGSTATCD,:"SYS_B_13") not in (:"SYS_B_14",:"SYS_B_15") AND cpsm.NPARAMCD=cpd.NCHGSTATCD AND IPARAMTYPECD=:"SYS_B_16" AND fgrd.STRACCTID=cpd.STRACCTID AND NVL(cpm.npolstatcd,:"SYS_B_17") != :"SYS_B_18" AND fgrh.NRECOREFSEQNBR=:seqnbr AND fgrd.NRECOREFSEQNBR=fgrh.NRECOREFSEQNBR GROUP BY NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) , STRLASTNAME,substr(strfirstname,:"SYS_B_19",:"SYS_B_20") || substr(strmiddlename,:"SYS_B_21",:"SYS_B_22") , cpm.STRPAYMENTID ,cpd.strAcctId ,:"SYS_B_23" , cpm.STRCLIENTCD ,ccm.STRNEWICNBR ,fgrh.DTMNTHLYPRDTO , fgrh.DTMNTHLYPRDFROM,fgrh.DTMNTHLYPRDTO UNION SELECT NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) strpolnbr, --cpmthird.strpolnbr payee, STRLASTNAME,substr(strfirstname,:"SYS_B_24",:"SYS_B_25") || substr(strmiddlename,:"SYS_B_26",:"SYS_B_27") strfirstname , SUM(NVL(cpd.dtotPaidAmnt+DTOTADJUSTAMNT,:"SYS_B_28")) amnt, cpm.STRPAYMENTID STRPAYMENTID ,cpd.strAcctId strAcctId ,:"SYS_B_29" descr, cpm.STRCLIENTCD STRCLIENTCD ,ccm.STRNEWICNBR STRNEWICNBR ,fgrh.DTWKLYPRDTO DTWKLYPRDTO, fgrh.DTWKLYPRDFROM DTWKLYPRDFROM ,fgrh.DTWKLYPRDTO DTWKLYPRDTOone , sum(nvl(cpd.DTOTDUEAMNT,:"SYS_B_30")) 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(:"SYS_B_31",:"SYS_B_32") and c.dtdueto=to_date(:"SYS_B_33",:"SYS_B_34") and cpd.nfinduetype not in(:"SYS_B_35",:"SYS_B_36")) AND LGRPRECOSEQNBR IS NULL AND NVL(NCHGSTATCD,:"SYS_B_37") not in (:"SYS_B_38",:"SYS_B_39") AND cpsm.NPARAMCD=cpd.NCHGSTATCD AND IPARAMTYPECD=:"SYS_B_40" AND fgrd.STRACCTID=cpd.STRACCTID AND NVL(cpm.npolstatcd,:"SYS_B_41") != :"SYS_B_42" AND fgrh.NRECOREFSEQNBR=:seqnbr AND fgrd.NRECOREFSEQNBR=fgrh.NRECOREFSEQNBR --AND --1=2 GROUP BY NVL(cpmthird.STRthirdpartyPOLNBR, cpmthird.strpolnbr) , STRLASTNAME,substr(strfirstname,:"SYS_B_43",:"SYS_B_44") || substr(strmiddlename,:"SYS_B_45",:"SYS_B_46") , cpm.STRPAYMENTID ,cpd.strAcctId ,:"SYS_B_47" , cpm.STRCLIENTCD ,ccm.STRNEWICNBR ,fgrh.DTWKLYPRDTO , fgrh.DTWKLYPRDFROM ,fgrh.DTWKLYPRDTO ) cpm1 , com_policy_m cpm2 Where cpm1.strpolnbr =cpm2.STRthirdpartyPOLNBR(+) and cpm1.amnt !=:"SYS_B_48" order by cpm1.strlastname 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 Misses in library cache during parse: 0 Parsing user id: 37 (BSESYS) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ global cache cr request 48778 0.01 23.29 db file sequential read 99931 0.20 493.59 latch free 24 0.01 0.03 global cache s to x 56 0.00 0.02 db file scattered read 3 0.01 0.03 SQL*Net message from client 902 0.15 49.03 SQL*Net message to client 902 0.00 0.00 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 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 Misses in library cache during parse: 0 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ global cache cr request 48778 0.01 23.29 db file sequential read 99931 0.20 493.59 latch free 24 0.01 0.03 global cache s to x 56 0.00 0.02 db file scattered read 3 0.01 0.03 SQL*Net message from client 902 0.15 49.03 SQL*Net message to client 902 0.00 0.00 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS 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 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 1 user SQL statements in session. 0 internal SQL statements in session. 1 SQL statements in session. 0 statements EXPLAINed in this session. ******************************************************************************** Trace file: db2_ora_21415.trc Trace file compatibility: 9.02.00 Sort options: default 10 sessions in tracefile. 1 user SQL statements in trace file. 0 internal SQL statements in trace file. 1 SQL statements in trace file. 1 unique SQL statements in trace file. 151775 lines in trace file.