the query ******************** SELECT /*+ LEADING(LDTB_CONTRACT_BALANCE) index(LDTB_CONTRACT_BALANCE IND01_LDTB_CON_BALANCE) index(LDTB_CONTRACT_MASTER)*/ LDTB_CONTRACT_MASTER.BRANCH , LDTB_CONTRACT_MASTER.PRODUCT PROD_ID , MITM_CUSTOMER_DEFAULT.CUST_MIS_1 OUC , LDTB_CONTRACT_MASTER.CURRENCY CCY , SUM(LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL) PRINCIPAL_DUE ,SUM (NVL(FN_GET_LD_ACCRUAL(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'IS',STTM_DATES.NEXT_WORKING_DAY),0) - NVL(FN_GET_LD_AMT(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'IS','PAID',LDTB_CONTRACT_MASTER.VALUE_DATE, LDTB_CONTRACT_MASTER.MATURITY_DATE),0)) INTEREST_DUE ,SUM (NVL(FN_GET_LD_ACCRUAL(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'N',STTM_DATES.NEXT_WORKING_DAY) ,0) - NVL(FN_GET_LD_AMT(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'N','PAID',LDTB_CONTRACT_MASTER.VALUE_DATE, LDTB_CONTRACT_MASTER.MATURITY_DATE),0)) OVERDUE_INTEREST FROM LDTB_CONTRACT_MASTER , LDTB_CONTRACT_PREFERENCE , MITM_CUSTOMER_DEFAULT , STTM_CUSTOMER , CFTB_CONTRACT_INTEREST , LDTB_CONTRACT_BALANCE , STTM_BRANCH , LDTM_PRODUCT_MASTER , CSTB_CONTRACT , STTM_DATES WHERE LDTB_CONTRACT_MASTER.CONTRACT_REF_NO = CSTB_CONTRACT.CONTRACT_REF_NO AND CSTB_CONTRACT.MODULE_CODE='LD' AND LDTB_CONTRACT_MASTER.COUNTERPARTY = MITM_CUSTOMER_DEFAULT.CUSTOMER AND LDTB_CONTRACT_MASTER.COUNTERPARTY = STTM_CUSTOMER.CUSTOMER_NO AND LDTB_CONTRACT_PREFERENCE.CONTRACT_REF_NO= LDTB_CONTRACT_MASTER.CONTRACT_REF_NO AND LDTB_CONTRACT_PREFERENCE.VERSION_NO = LDTB_CONTRACT_MASTER.VERSION_NO AND CFTB_CONTRACT_INTEREST.CONTRACT_REFERENCE_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO AND CFTB_CONTRACT_INTEREST.COMPONENT = LDTB_CONTRACT_MASTER.MAIN_COMP AND CFTB_CONTRACT_INTEREST.EVENT_SEQUENCE_NO = (SELECT MAX(Y.EVENT_SEQUENCE_NO) FROM CFTB_CONTRACT_INTEREST Y WHERE Y.CONTRACT_REFERENCE_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO) AND LDTB_CONTRACT_BALANCE.CONTRACT_REF_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO --AND NVL(LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL,0) <> 0 AND (PRINCIPAL_OUTSTANDING_BAL>0 or PRINCIPAL_OUTSTANDING_BAL<0) AND LDTM_PRODUCT_MASTER.PRODUCT = LDTB_CONTRACT_MASTER.PRODUCT AND STTM_BRANCH.BRANCH_CODE = LDTB_CONTRACT_MASTER.BRANCH AND STTM_DATES.BRANCH_CODE = STTM_BRANCH.BRANCH_CODE AND LDTB_CONTRACT_MASTER.VERSION_NO = (SELECT MAX(Z.VERSION_NO) FROM LDTBS_CONTRACT_MASTER Z WHERE Z.CONTRACT_REF_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO) --AND (LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL <> 0 AND ((PRINCIPAL_OUTSTANDING_BAL>0 or PRINCIPAL_OUTSTANDING_BAL<0) OR ( EXISTS ( SELECT 1 FROM LDTB_AMOUNT_DUE WHERE CONTRACT_REF_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO AND AMOUNT_DUE > (NVL(AMOUNT_SETTLED,0) + NVL(ADJUSTED_AMOUNT,0)) AND DUE_DATE <= STTM_DATES.TODAY ))) GROUP BY LDTB_CONTRACT_MASTER.BRANCH, LDTB_CONTRACT_MASTER.PRODUCT, MITM_CUSTOMER_DEFAULT.CUST_MIS_1, LDTB_CONTRACT_MASTER.CURRENCY *************************************************************************************************************************** explain plan ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 6535 (100)| | | 1 | SORT GROUP BY | | 1 | 240 | | 6535 (4)| 00:01:19 | | 2 | FILTER | | | | | | | | 3 | NESTED LOOPS | | 295 | 70800 | | 6089 (4)| 00:01:14 | | 4 | HASH JOIN | | 295 | 65785 | | 5793 (4)| 00:01:10 | | 5 | HASH JOIN | | 295 | 59885 | | 5585 (4)| 00:01:08 | | 6 | HASH JOIN | | 295 | 55460 | | 5497 (4)| 00:01:06 | | 7 | HASH JOIN | | 296 | 49728 | 6384K| 5383 (3)| 00:01:05 | | 8 | INDEX FAST FULL SCAN | IND_INT_CRN_COMP_ESN | 152K| 4602K| | 319 (4)| 00:00:04 | | 9 | HASH JOIN | | 126K| 16M| 4824K| 3835 (4)| 00:00:47 | | 10 | VIEW | VW_SQ_1 | 117K| 3443K| | 1414 (2)| 00:00:17 | | 11 | SORT GROUP BY | | 117K| 4361K| 12M| 1414 (2)| 00:00:17 | | 12 | INDEX FULL SCAN | IND_INT_CRN_COMP_ESN | 152K| 5641K| | 1414 (2)| 00:00:17 | | 13 | HASH JOIN | | 126K| 12M| | 1451 (6)| 00:00:18 | | 14 | INDEX FAST FULL SCAN | SYS_C0016628 | 31465 | 307K| | 32 (7)| 00:00:01 | | 15 | HASH JOIN | | 126K| 11M| | 1411 (5)| 00:00:17 | | 16 | INDEX FULL SCAN | LNPROD_PK | 93 | 465 | | 1 (0)| 00:00:01 | | 17 | HASH JOIN | | 126K| 11M| | 1404 (5)| 00:00:17 | | 18 | HASH JOIN | | 38 | 912 | | 5 (20)| 00:00:01 | | 19 | INDEX FULL SCAN | SYS_C0016574 | 38 | 152 | | 1 (0)| 00:00:01 | | 20 | TABLE ACCESS FULL | STTM_DATES | 38 | 760 | | 3 (0)| 00:00:01 | | 21 | TABLE ACCESS FULL | LDTB_CONTRACT_MASTER | 126K| 8419K| | 1394 (5)| 00:00:17 | | 22 | TABLE ACCESS FULL | LDTB_CONTRACT_BALANCE | 115K| 2249K| | 108 (12)| 00:00:02 | | 23 | TABLE ACCESS FULL | MITM_CUSTOMER_DEFAULT | 31520 | 461K| | 87 (5)| 00:00:02 | | 24 | INDEX FAST FULL SCAN | CP1_PK | 126K| 2476K| | 202 (5)| 00:00:03 | | 25 | INDEX UNIQUE SCAN | CC1_PK | 1 | 17 | | 1 (0)| 00:00:01 | | 26 | SORT AGGREGATE | | 1 | 20 | | | | | 27 | FIRST ROW | | 1 | 20 | | 3 (0)| 00:00:01 | | 28 | INDEX RANGE SCAN (MIN/MAX) | CM_PK | 1 | 20 | | 3 (0)| 00:00:01 | | 29 | TABLE ACCESS BY INDEX ROWID| CSTB_AMOUNT_DUE | 1 | 37 | | 4 (0)| 00:00:01 | | 30 | INDEX RANGE SCAN | DUE_PK1 | 1 | | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------ ********************************************************************************************************************************** the trace was enabled for 15 min the result is TKPROF: Release 10.2.0.2.0 - Production on Thu Aug 2 12:38:48 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: ././infbop_ora_4553.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 SUM(NVL(AMOUNT_SETTLED,0)+NVL(ADJUSTED_AMOUNT,0)) FROM LDTBS_AMOUNT_DUE A WHERE A.CONTRACT_REF_NO = :B4 AND A.COMPONENT_TYPE IN (SUBSTR(:B3 ,1,1),SUBSTR(:B3 ,2,1)) AND DUE_DATE <= :B2 AND DUE_DATE > :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1469 0.21 0.19 0 0 0 0 Fetch 1469 0.45 0.40 0 42859 0 1469 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2938 0.66 0.60 0 42859 0 1469 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 30 (recursive depth: 1) ******************************************************************************** SELECT SUM(A.TILL_DATE_ACCRUAL) FROM LDTBS_CONTRACT_ACCRUAL_HISTORY A WHERE A.CONTRACT_REF_NO = :B2 AND A.VALUE_DATE <= :B1 AND A.ACC_ENTRY_PASSED = 'Y' AND A.COMPONENT IN ( SELECT DISTINCT COMPONENT FROM LDTB_AMOUNT_DUE WHERE CONTRACT_REF_NO = A.CONTRACT_REF_NO AND COMPONENT_TYPE = :B3 ) AND A.EVENT_SEQ_NO = ( SELECT MAX(EVENT_SEQ_NO) FROM LDTB_CONTRACT_ACCRUAL_HISTORY WHERE CONTRACT_REF_NO = A.CONTRACT_REF_NO AND COMPONENT = A.COMPONENT AND ACC_ENTRY_PASSED = 'Y' AND VALUE_DATE <= :B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1469 0.10 0.12 0 0 0 0 Fetch 1469 4.18 66.65 11338 269900 0 1469 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2938 4.28 66.77 11338 269900 0 1469 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 30 (recursive depth: 1) ******************************************************************************** SELECT SUM(NVL(AMOUNT_SETTLED,0)+NVL(ADJUSTED_AMOUNT,0)) FROM LDTBS_AMOUNT_DUE A WHERE A.CONTRACT_REF_NO = :B4 AND A.COMPONENT_TYPE = :B3 AND DUE_DATE <= :B2 AND DUE_DATE > :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1469 0.12 0.09 0 0 0 0 Fetch 1469 0.31 0.25 0 42796 0 1469 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2938 0.43 0.34 0 42796 0 1469 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 30 (recursive depth: 1) ******************************************************************************** SELECT SUM(A.TILL_DATE_ACCRUAL) FROM LDTBS_CONTRACT_ACCRUAL_HISTORY A WHERE A.CONTRACT_REF_NO = :B2 AND A.VALUE_DATE <= :B1 AND A.ACC_ENTRY_PASSED = 'Y' AND A.COMPONENT IN ( SELECT DISTINCT COMPONENT FROM LDTB_AMOUNT_DUE WHERE CONTRACT_REF_NO = A.CONTRACT_REF_NO AND COMPONENT_TYPE IN (SUBSTR(:B3 ,1,1),SUBSTR(:B3 ,2,1))) AND A.EVENT_SEQ_NO = ( SELECT MAX(EVENT_SEQ_NO) FROM LDTB_CONTRACT_ACCRUAL_HISTORY WHERE CONTRACT_REF_NO = A.CONTRACT_REF_NO AND COMPONENT = A.COMPONENT AND ACC_ENTRY_PASSED = 'Y' AND VALUE_DATE <= :B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1469 0.09 0.10 0 0 0 0 Fetch 1469 1025.08 1114.75 19847 176061253 0 1469 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2938 1025.17 1114.85 19847 176061253 0 1469 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 30 (recursive depth: 1) ******************************************************************************** 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 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 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 5876 0.52 0.51 0 0 0 0 Fetch 5876 1030.02 1182.06 31185 176416808 0 5876 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11752 1030.54 1182.57 31185 176416808 0 5876 Misses in library cache during parse: 0 4 user SQL statements in session. 0 internal SQL statements in session. 4 SQL statements in session. ******************************************************************************** Trace file: ././infbop_ora_4553.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 4 user SQL statements in trace file. 0 internal SQL statements in trace file. 4 SQL statements in trace file. 4 unique SQL statements in trace file. 11899 lines in trace file. 1184 elapsed seconds in trace file. *************************************************************************************************************** the functions ************** CREATE OR REPLACE FUNCTION OPS$PVCSLBR1.FN_GET_LD_ACCRUAL ( p_contract_ref_no in ldtbs_contract_master.contract_ref_no%type ,p_component_type in varchar2 ,p_accrual_till in date) return number is l_amount number:=0; l_accrual_till date; BEGIN l_accrual_till := nvl(p_accrual_till,'31-DEC-2999'); IF length(p_component_type) = 1 then Select sum(a.TILL_DATE_ACCRUAL) into l_amount from ldtbs_contract_accrual_history a where a.contract_ref_no = p_contract_ref_no and a.VALUE_DATE <= l_accrual_till and a.acc_entry_passed = 'Y' and a.component in ( select distinct component from ldtb_amount_due where contract_ref_no = a.contract_ref_no and component_type = p_component_type ) and a.event_seq_no = ( select max(event_seq_no) from ldtb_contract_accrual_history where contract_ref_no = a.contract_ref_no and component = a.component and acc_entry_passed = 'Y' and VALUE_DATE <= l_accrual_till ); ELSIF length(p_component_type) = 2 then Select sum(a.TILL_DATE_ACCRUAL) into l_amount from ldtbs_contract_accrual_history a where a.contract_ref_no = p_contract_ref_no and a.VALUE_DATE <= l_accrual_till and a.acc_entry_passed = 'Y' and a.component in ( select distinct component from ldtb_amount_due where contract_ref_no = a.contract_ref_no and component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1))) and a.event_seq_no = ( select max(event_seq_no) from ldtb_contract_accrual_history where contract_ref_no = a.contract_ref_no and component = a.component and acc_entry_passed = 'Y' and VALUE_DATE <= l_accrual_till ); ELSIF length(p_component_type) = 3 then Select sum(a.TILL_DATE_ACCRUAL) into l_amount from ldtbs_contract_accrual_history a where a.contract_ref_no = p_contract_ref_no and a.VALUE_DATE <= l_accrual_till and a.acc_entry_passed = 'Y' and a.component in ( select distinct component from ldtb_amount_due where contract_ref_no = a.contract_ref_no and component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1),substr(p_component_type,3,1))) and a.event_seq_no = ( select max(event_seq_no) from ldtb_contract_accrual_history where contract_ref_no = a.contract_ref_no and component = a.component and acc_entry_passed = 'Y' and VALUE_DATE <= l_accrual_till ); ELSIF length(p_component_type) = 4 then -- no need to check .. all 4 types Select sum(a.TILL_DATE_ACCRUAL) into l_amount from ldtbs_contract_accrual_history a where a.contract_ref_no = p_contract_ref_no and a.VALUE_DATE <= l_accrual_till and a.acc_entry_passed = 'Y' and a.component in ( select distinct component from ldtb_amount_due where contract_ref_no = a.contract_ref_no ) -- and component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1),substr(p_component_type,3,1))) and a.event_seq_no = ( select max(event_seq_no) from ldtb_contract_accrual_history where contract_ref_no = a.contract_ref_no and component = a.component and acc_entry_passed = 'Y' and VALUE_DATE <= l_accrual_till ); END IF; return l_amount; EXCEPTION WHEN NO_DATA_FOUND THEN return 0 ; END; / the other functions ************************* CREATE OR REPLACE FUNCTION "FN_GET_LD_AMT" (p_contract_ref_no in ldtbs_contract_master.contract_ref_no%type ,p_component_type in varchar2 ,p_paid_flag in varchar2 ,p_from_date date ,p_till in date) return number is l_amount number:=0; begin IF length(p_component_type) = 1 then IF nvl(p_paid_flag,'OUTSTD') = 'OUTSTD' then dbms_output.put_line(' startng OUTSTD'); select sum(nvl(amount_due,0)-nvl(amount_settled,0)-nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type = p_component_type and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'PAID' THEN select sum(nvl(amount_settled,0)+nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type = p_component_type and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'TOTAL' THEN select sum(nvl(amount_due,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type = p_component_type and due_date <= p_till and due_date > p_from_date; END IF; ELSIF length(p_component_type) = 2 then IF nvl(p_paid_flag,'OUTSTD') = 'OUTSTD' then dbms_output.put_line(' startng OUTSTD'); select sum(nvl(amount_due,0)-nvl(amount_settled,0)-nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1)) and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'PAID' THEN select sum(nvl(amount_settled,0)+nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1)) and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'TOTAL' THEN select sum(nvl(amount_due,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1)) and due_date <= p_till and due_date > p_from_date; END IF; ELSIF length(p_component_type) = 3 then IF nvl(p_paid_flag,'OUTSTD') = 'OUTSTD' then dbms_output.put_line(' startng OUTSTD'); select sum(nvl(amount_due,0)-nvl(amount_settled,0)-nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1),substr(p_component_type,3,1)) and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'PAID' THEN select sum(nvl(amount_settled,0)+nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1),substr(p_component_type,3,1)) and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'TOTAL' THEN select sum(nvl(amount_due,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and a.component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1),substr(p_component_type,3,1)) and due_date <= p_till and due_date > p_from_date; END IF; ELSIF length(p_component_type) = 4 then -- No need to check .. take all components IF nvl(p_paid_flag,'OUTSTD') = 'OUTSTD' then dbms_output.put_line(' startng OUTSTD'); select sum(nvl(amount_due,0)-nvl(amount_settled,0)-nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'PAID' THEN select sum(nvl(amount_settled,0)+nvl(ADJUSTED_AMOUNT,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and due_date <= p_till and due_date > p_from_date; ELSIF p_paid_flag = 'TOTAL' THEN select sum(nvl(amount_due,0)) into l_amount from ldtbs_amount_due a where a.contract_ref_no = p_contract_ref_no and due_date <= p_till and due_date > p_from_date; END IF; END IF; return l_amount; exception when no_data_found then return 0; when others then dbms_output.put_line(' WO '||sqlerrm||sqlcode); return null; end; / ****************************************************************************************************************************