TKPROF: Release 8.0.6.0.0 - Production on Sun May 23 10:46:29 2010 (c) Copyright 1999 Oracle Corporation. All rights reserved. Trace file: c:\ora00192.trc Sort options: prsdsk exedsk fchdsk ******************************************************************************** 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(ITEM_AMT) FROM A_PHARMACY_CASH_SALES WHERE BUS_UNIT = :b1 AND (TRUNC(CRE_DATE) BETWEEN :b2 AND :b3 ) AND LOCATION_CD = :b4 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 20 12.81 22.06 24474 6184650 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 32 12.81 22.06 24474 6184650 0 10 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 675 (ULTGNP) Rows Row Source Operation ------- --------------------------------------------------- 139 SORT AGGREGATE 4104534 TABLE ACCESS BY INDEX ROWID A_PHARMACY_CASH_SALES 4104541 INDEX RANGE SCAN (object id 748691) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 139 SORT (AGGREGATE) 4104534 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'A_PHARMACY_CASH_SALES' ******************************************************************************** SELECT SUM(ADVNC_AMOUNT) FROM A_ULTIMA_ADVANCES WHERE BUS_UNIT = :b1 AND SUBSTR(INVOICE_NO,1,1) != 'J' AND (TRUNC(ADVNC_DATE) BETWEEN :b2 AND :b3 ) AND LOCATION_CD = :b4 AND TRANX_MODE IN ( 'P' ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 20 14.35 81.57 23622 6533250 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 32 14.35 81.57 23622 6533250 0 10 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 675 (ULTGNP) Rows Row Source Operation ------- --------------------------------------------------- 312 SORT AGGREGATE 4524541 TABLE ACCESS BY INDEX ROWID A_ULTIMA_ADVANCES 4923072 INDEX RANGE SCAN (object id 830967) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 312 SORT (AGGREGATE) 4524541 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'A_ULTIMA_ADVANCES' ******************************************************************************** SELECT SUM(NVL(ORD_BILL_NET_AMT,0)) FROM A_PT_ORDREG A,A_PT_BILL_DETAIL B WHERE A.BUS_UNIT = :b1 AND A.BUS_UNIT = B.BUS_UNIT AND A.PT_CODE = B.PT_CODE AND A.PT_CODE = :b2 AND A.REG_NO = B.REG_NO AND A.REG_NO = :b3 AND SUBSTR(A.REG_NO,1,2) = 'O-' AND A.SEQ_NO = B.SEQ_NO AND A.ORDER_NO = B.ORDER_NO AND A.SERVICE_TYPE_CODE = B.SERVICE_TYPE_CODE AND A.SERVICE_CODE = B.SERVICE_CODE AND NVL(A.CASH_FLAG,'O') = 'C' AND NVL(B.PT_BILL_VOID_FLAG,'O') != 'V' AND B.LOCATION_CD = :b4 AND B.CRE_BY = :b5 AND (B.CRE_DATE) = :b6 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 447 0.01 0.02 0 0 0 0 Fetch 894 0.08 3.73 956 10878 0 447 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1342 0.09 3.75 956 10878 0 447 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 675 (ULTGNP) Rows Row Source Operation ------- --------------------------------------------------- 786 SORT AGGREGATE 786 NESTED LOOPS 3106 TABLE ACCESS BY INDEX ROWID A_PT_ORDREG 3553 INDEX RANGE SCAN (object id 748708) 1399 TABLE ACCESS BY INDEX ROWID A_PT_BILL_DETAIL 1852 INDEX UNIQUE SCAN (object id 5240) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 786 SORT (AGGREGATE) 786 NESTED LOOPS 3106 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'A_PT_ORDREG' 3553 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'A_PT_ORDREG_NDX4' (NON-UNIQUE) 1399 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'A_PT_BILL_DETAIL' 1852 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_A_PT_BILL_DETAIL' (UNIQUE) ******************************************************************************** SELECT PT_FIRST_NAME_E || ' ' || PT_SECOND_NAME_E || ' ' || PT_LAST_NAME_E FROM A_PT_MASTER WHERE PT_CODE = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 447 0.00 0.00 0 0 0 0 Fetch 888 0.00 3.00 548 2250 0 441 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1336 0.00 3.00 548 2250 0 441 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 675 (ULTGNP) Rows Row Source Operation ------- --------------------------------------------------- 441 TABLE ACCESS BY INDEX ROWID A_PT_MASTER 888 INDEX RANGE SCAN (object id 5282) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 441 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'A_PT_MASTER' 888 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX6_A_PT_MASTER' (NON-UNIQUE) ******************************************************************************** SELECT B.CRE_BY CRE_BY, B.REG_NO REG_NO, B.PT_CODE PT_CODE, A.PT_REG_DATE PT_REG_DATE, (B.CRE_DATE) CRE_DATE, B.LOCATION_CD LOCATION_CD, CUST_CUSTOMER cust FROM A_PT_BILL_MASTER A , A_PT_BILL_DETAIL B WHERE ( A.BUS_UNIT = B.BUS_UNIT AND A.REG_NO = B.REG_NO AND A.PT_CODE = B.PT_CODE AND ((B.CRE_DATE) >= :P_DATE_FROM AND (B.CRE_DATE) < :P_DATE_TO+1) AND B.BUS_UNIT=:ESI_PRM_1 AND SUBSTR(A.REG_NO,1,2) = 'O-' AND NVL(B.PT_BILL_VOID_FLAG,'O') <> 'V' ) AND ( :BANK = B.LOCATION_CD) UNION SELECT CPD_CRE_BY CRE_BY, CPD_VOUCHER_NO REG_NO, CPD_GNP_RECEIPT PT_CODE, CPD_RECEIPT_DATE PT_REG_DATE, (CPD_VOUCHER_DATE) CRE_DATE, CPD_LOCATION_CD LOCATION_CD, CPD_CUSTOMER_NO CUST FROM COMPANY_PAYMENT_DETAILS WHERE ( CPD_BUS_UNIT=:ESI_PRM_1 AND CPD_VOUCHER_DATE >= :P_DATE_FROM AND CPD_VOUCHER_DATE < (:P_DATE_TO+1) AND CPD_STATUS = 'A' AND CPD_POST_STATUS = 'Y' ) AND ( :BANK = COMPANY_PAYMENT_DETAILS.CPD_LOCATION_CD) UNION SELECT CRE_BY, INVOICE_NO REG_NO, PT_CODE, ADVNC_DATE PT_REG_DATE, (CRE_DATE) CRE_DATE, LOCATION_CD, PT_CODE CUST FROM AR_RESTRUCTURE_PAYMENT WHERE ( BUS_UNIT=:ESI_PRM_1 AND TRANX_MODE = 'P' AND CRE_DATE >= :P_DATE_FROM AND CRE_DATE < (:P_DATE_TO+1) ) AND ( :BANK = AR_RESTRUCTURE_PAYMENT.LOCATION_CD) ORDER BY 1 ASC , REG_NO call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 18 0.02 0.33 118 8041 0 447 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 26 0.02 0.33 118 8041 0 447 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 675 (ULTGNP) Rows Row Source Operation ------- --------------------------------------------------- 792 SORT UNIQUE 792 UNION-ALL 786 NESTED LOOPS 5502 TABLE ACCESS BY INDEX ROWID A_PT_BILL_DETAIL 5509 INDEX RANGE SCAN (object id 553304) 786 TABLE ACCESS BY INDEX ROWID A_PT_BILL_MASTER 786 INDEX UNIQUE SCAN (object id 5249) 42 TABLE ACCESS BY INDEX ROWID COMPANY_PAYMENT_DETAILS 49 INDEX RANGE SCAN (object id 698112) 0 TABLE ACCESS BY INDEX ROWID AR_RESTRUCTURE_PAYMENT 7 INDEX RANGE SCAN (object id 706464) error during parse of EXPLAIN PLAN statement ORA-00932: inconsistent datatypes parse error offset: 412 ******************************************************************************** SELECT CPD_VOUCHER_AMT FROM COMPANY_PAYMENT_DETAILS WHERE CPD_BUS_UNIT = :b1 AND CPD_VOUCHER_NO = :b2 AND CPD_LOCATION_CD = :b3 AND CPD_CRE_BY = :b4 AND TRUNC(CPD_VOUCHER_DATE) = :b5 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 447 0.00 0.00 0 0 0 0 Fetch 453 1.36 1.53 80 36665 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 901 1.36 1.53 80 36665 0 6 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 675 (ULTGNP) Rows Row Source Operation ------- --------------------------------------------------- 6 TABLE ACCESS BY INDEX ROWID COMPANY_PAYMENT_DETAILS 3803076 INDEX RANGE SCAN (object id 698112) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 6 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'COMPANY_PAYMENT_DETAILS' ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 30 0.06 0.12 8 0 8 0 Execute 2727 0.02 0.04 0 0 0 0 Fetch 4563 28.71 112.41 49815 12792231 27 2279 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7320 28.79 112.57 49823 12792231 35 2279 Misses in library cache during parse: 25 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 97 0.00 0.00 0 0 0 0 Execute 142 0.00 0.00 0 0 0 0 Fetch 1093 0.03 1.97 488 1738 0 1045 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1332 0.03 1.97 488 1738 0 1045 Misses in library cache during parse: 0 30 user SQL statements in session. 97 internal SQL statements in session. 127 SQL statements in session. 5 statements EXPLAINed in this session. ******************************************************************************** Trace file: c:\ora00192.trc Trace file compatibility: 7.03.02 Sort options: prsdsk exedsk fchdsk 1 session in tracefile. 30 user SQL statements in trace file. 97 internal SQL statements in trace file. 127 SQL statements in trace file. 38 unique SQL statements in trace file. 5 SQL statements EXPLAINed using schema: ULTGNP.prof$plan_table Default table was used. Table was created. Table was dropped. 9562 lines in trace file.