=~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2007.08.02 12:39:51 =~=~=~=~=~=~=~=~=~=~=~= cat infbop_020807.txt 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 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 34 | 4 | | 1 | SORT AGGREGATE | | 1 | 34 | | | 2 | FILTER | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| CSTB_AMOUNT_DUE | 1 | 34 | 4 | | 4 | INDEX RANGE SCAN | DUE_PK1 | 1 | | 3 | --------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version 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 ) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 77 | 88 | | 1 | SORT AGGREGATE | | 1 | 77 | | | 2 | FILTER | | | | | | 3 | TABLE ACCESS BY INDEX ROWID | LDTB_CONTRACT_ACCRUAL_HISTORY | 1 | 47 | 3 | | 4 | NESTED LOOPS | | 1 | 77 | 8 | | 5 | SORT UNIQUE | | 1 | 30 | 4 | | 6 | TABLE ACCESS BY INDEX ROWID| CSTB_AMOUNT_DUE | 1 | 30 | 4 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 7 | INDEX RANGE SCAN | DUE_PK1 | 3 | | 3 | | 8 | INDEX RANGE SCAN | IND_REF_NO | 1 | | 2 | | 9 | SORT AGGREGATE | | 1 | 41 | | | 10 | TABLE ACCESS BY INDEX ROWID | LDTB_CONTRACT_ACCRUAL_HISTORY | 1 | 41 | 80 | | 11 | INDEX RANGE SCAN | IND_REF_NO | 86 | | 3 | -------------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version 21 rows selected. 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 error in the explainplan 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 ) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 88 | | 1 | SORT AGGREGATE | | 1 | 13 | | | 2 | VIEW | | 1 | 13 | 88 | | 3 | FILTER | | | | | | 4 | SORT GROUP BY | | 1 | 157 | 88 | | 5 | TABLE ACCESS BY INDEX ROWID | LDTB_CONTRACT_ACCRUAL_HISTORY | 1 | 68 | 79 | | 6 | NESTED LOOPS | | 1 | 157 | 87 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 7 | NESTED LOOPS | | 1 | 89 | 8 | | 8 | SORT UNIQUE | | 1 | 30 | 4 | | 9 | TABLE ACCESS BY INDEX ROWID| CSTB_AMOUNT_DUE | 1 | 30 | 4 | | 10 | INDEX RANGE SCAN | DUE_PK1 | 3 | | 3 | | 11 | TABLE ACCESS BY INDEX ROWID | LDTB_CONTRACT_ACCRUAL_HISTORY | 1 | 59 | 3 | | 12 | INDEX RANGE SCAN | IND_REF_NO | 1 | | 2 | | 13 | INDEX RANGE SCAN | IND_REF_NO | 86 | | 2 | ----------------------------------------------------------------------------------------------------- Note ----- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - 'PLAN_TABLE' is old version 23 rows selected. 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. shpp04:rsh:/tmp>