TKPROF: Release 8.1.7.4.0 - Production on Sun Dec 17 13:03:09 2006 (c) Copyright 2000 Oracle Corporation. All rights reserved. Trace file: sty_prod_ora_1062.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 servcat_code ,serv_code ,serv_type ,customercat_code ,customergeo_code ,taxcode ,to_char(valid_from,'YYYYMMDD') ,to_char(invalid_from,'YYYYMMDD') , nvl(taxrate,0.0) ,nvl(glacode,' ') ,nvl(taxtype_id,0) ,nvl(legalcode,' ') , tax_seqno ,NVL(taxcode_version_des,'') ,taxcat_id ,NVL(taxcat_name,'') , zero_tax_default from SERVICE_TAX_VIEW order by 1,2,3,4,5,6,7 desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.04 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 40 0.03 0.29 12 138 96 39 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 42 0.04 0.33 12 138 96 39 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 39 SORT ORDER BY 39 NESTED LOOPS 40 NESTED LOOPS 40 HASH JOIN 2 VIEW TAX_CODE_VIEW 2 SORT UNIQUE 2 UNION-ALL 2 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 TABLE ACCESS FULL TAX_CODE_VERSION 4 TABLE ACCESS BY INDEX ROWID TAX_CATEGORY 4 INDEX UNIQUE SCAN (object id 10552) 4 TABLE ACCESS BY INDEX ROWID TAX_CODE 4 INDEX UNIQUE SCAN (object id 10554) 0 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 HASH JOIN 2 TABLE ACCESS FULL TAX_CODE_VERSION 2 TABLE ACCESS FULL TAX_CODE_VERSION 4 TABLE ACCESS BY INDEX ROWID TAX_CATEGORY 4 INDEX UNIQUE SCAN (object id 10552) 4 TABLE ACCESS BY INDEX ROWID TAX_CODE 4 INDEX UNIQUE SCAN (object id 10554) 39 TABLE ACCESS FULL SERVICE_TAX_CODE 78 TABLE ACCESS BY INDEX ROWID SERVICE_TAXATION 78 INDEX UNIQUE SCAN (object id 10474) 39 TABLE ACCESS BY INDEX ROWID SERVICECATCODE 78 INDEX UNIQUE SCAN (object id 10453) 4 SORT AGGREGATE 0 TABLE ACCESS FULL TAX_CODE_VERSION 4 SORT AGGREGATE 2 TABLE ACCESS FULL TAX_CODE_VERSION Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 39 SORT (ORDER BY) 39 NESTED LOOPS 40 NESTED LOOPS 40 HASH JOIN 2 VIEW OF 'TAX_CODE_VIEW' 2 SORT (UNIQUE) 2 UNION-ALL 2 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CATEGORY' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CATEGORY' (UNIQUE) 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CODE' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CODE' (UNIQUE) 4 SORT (AGGREGATE) 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 0 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 HASH JOIN 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CATEGORY' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CATEGORY' (UNIQUE) 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CODE' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CODE' (UNIQUE) 4 SORT (AGGREGATE) 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 39 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SERVICE_TAX_CODE' 78 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'SERVICE_TAXATION' 78 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKSERVICE_TAXATION' (UNIQUE) 39 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'SERVICECATCODE' 78 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SERVCAT_SERVCAT_ID' (UNIQUE) ******************************************************************************** select taxcode ,to_char(valid_from,'YYYYMMDD') ,to_char(invalid_from, 'YYYYMMDD') ,nvl(taxrate,0.0) ,nvl(glacode,' ') ,nvl(taxtype_id,0) , nvl(legalcode,' ') ,tax_seqno ,NVL(taxcode_version_des,'') ,taxcat_id , NVL(taxcat_name,'') ,zero_tax_default from TAX_CODE_VIEW where zero_tax_default='X' order by 1,2 desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.00 1 38 72 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 1 38 72 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY 1 VIEW TAX_CODE_VIEW 1 SORT UNIQUE 1 UNION-ALL 1 FILTER 2 NESTED LOOPS 2 NESTED LOOPS 3 TABLE ACCESS FULL TAX_CODE_VERSION 3 TABLE ACCESS BY INDEX ROWID TAX_CATEGORY 4 INDEX UNIQUE SCAN (object id 10552) 2 TABLE ACCESS BY INDEX ROWID TAX_CODE 2 INDEX UNIQUE SCAN (object id 10554) 0 FILTER 2 NESTED LOOPS 2 HASH JOIN 2 MERGE JOIN CARTESIAN 2 TABLE ACCESS FULL TAX_CATEGORY 2 SORT JOIN 2 TABLE ACCESS FULL TAX_CODE_VERSION 2 TABLE ACCESS FULL TAX_CODE_VERSION 2 TABLE ACCESS BY INDEX ROWID TAX_CODE 2 INDEX UNIQUE SCAN (object id 10554) 2 SORT AGGREGATE 0 TABLE ACCESS FULL TAX_CODE_VERSION 2 SORT AGGREGATE 1 TABLE ACCESS FULL TAX_CODE_VERSION Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (ORDER BY) 1 VIEW OF 'TAX_CODE_VIEW' 1 SORT (UNIQUE) 1 UNION-ALL 1 FILTER 2 NESTED LOOPS 2 NESTED LOOPS 3 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 3 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CATEGORY' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CATEGORY' (UNIQUE) 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CODE' 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CODE' (UNIQUE) 2 SORT (AGGREGATE) 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 0 FILTER 2 NESTED LOOPS 2 HASH JOIN 2 MERGE JOIN (CARTESIAN) 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CATEGORY' 2 SORT (JOIN) 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CODE' 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CODE' (UNIQUE) 2 SORT (AGGREGATE) 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' ******************************************************************************** select taxcode ,tax_seqno ,to_char(valid_from,'YYYYMMDD') , to_char(invalid_from,'YYYYMMDD') ,taxrate ,glacode ,taxtype_id , nvl(legalcode,' ') ,NVL(taxcode_version_des,'') ,taxcat_id ,NVL(taxcat_name, '') from TAX_CODE_VIEW order by 1,2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 51 84 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 51 84 2 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 2 SORT ORDER BY 2 VIEW TAX_CODE_VIEW 2 SORT UNIQUE 2 UNION-ALL 2 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 TABLE ACCESS FULL TAX_CODE_VERSION 4 TABLE ACCESS BY INDEX ROWID TAX_CATEGORY 4 INDEX UNIQUE SCAN (object id 10552) 4 TABLE ACCESS BY INDEX ROWID TAX_CODE 4 INDEX UNIQUE SCAN (object id 10554) 0 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 HASH JOIN 2 TABLE ACCESS FULL TAX_CODE_VERSION 2 TABLE ACCESS FULL TAX_CODE_VERSION 4 TABLE ACCESS BY INDEX ROWID TAX_CATEGORY 4 INDEX UNIQUE SCAN (object id 10552) 4 TABLE ACCESS BY INDEX ROWID TAX_CODE 4 INDEX UNIQUE SCAN (object id 10554) 4 SORT AGGREGATE 0 TABLE ACCESS FULL TAX_CODE_VERSION 4 SORT AGGREGATE 2 TABLE ACCESS FULL TAX_CODE_VERSION Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 2 SORT (ORDER BY) 2 VIEW OF 'TAX_CODE_VIEW' 2 SORT (UNIQUE) 2 UNION-ALL 2 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CATEGORY' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CATEGORY' (UNIQUE) 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CODE' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CODE' (UNIQUE) 4 SORT (AGGREGATE) 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 0 FILTER 3 NESTED LOOPS 3 NESTED LOOPS 3 HASH JOIN 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CATEGORY' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CATEGORY' (UNIQUE) 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAX_CODE' 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKTAX_CODE' (UNIQUE) 4 SORT (AGGREGATE) 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_CODE_VERSION' ******************************************************************************** select TE.customer_id ,TE.TAXTYPE_ID ,TE.SEQ_NO ,to_char(TE.EXPIRATION_DATE, 'YYYYMMDD') ,to_char(TE.VALID_FROM,'YYYYMMDD') ,TT.DESCRIPTION from TAX_EXEMPT TE ,TAXTYPE TT where TE.TAXTYPE_ID=TT.TAXTYPE_ID order by TE.customer_id,TE.TAXTYPE_ID,TE.VALID_FROM call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 6 0.00 0.00 0 7 16 267 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 8 0.00 0.01 0 7 16 267 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 267 SORT ORDER BY 267 HASH JOIN 2 TABLE ACCESS FULL TAXTYPE 267 TABLE ACCESS FULL TAX_EXEMPT Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 267 SORT (ORDER BY) 267 HASH JOIN 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAXTYPE' 267 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAX_EXEMPT' ******************************************************************************** select ULCFF1 ,ULCFF2 into :b0:b1,:b2:b3 from USERLBL where ULTAG='ARRDDFT' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID USERLBL 2 INDEX UNIQUE SCAN (object id 10676) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'USERLBL' 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKUSERLBL' (UNIQUE) ******************************************************************************** ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD-HH24.MI.SS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) ******************************************************************************** ALTER SESSION SET NLS_LANGUAGE = AMERICAN call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) ******************************************************************************** ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. ' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) ******************************************************************************** ALTER SESSION SET PARTITION_VIEW_ENABLED = TRUE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) ******************************************************************************** SELECT RT.RULE_TEMPLATE_ID, TO_CHAR(RTV.VERSION_DATE,'YYYY MM DD HH24 MI SS'), RTV.COMPILED_IND, RTI.RULE_TEMPLATE_TEXT, RT.APPL_MECHANISM_NUM, RT.EVAL_MECHANISM_NUM FROM RULE_TEMPLATE RT, RULE_TEMPLATE_VERSION RTV, RULE_TEMPLATE_INTERPRETED RTI WHERE RT.RULE_PURPOSE_CODE = 2 AND RTV.WORK_STATE = 'P' AND RTV.COMPILED_IND = 'N' AND RT.RULE_TEMPLATE_ID = RTV.RULE_TEMPLATE_ID AND RTV.INTERPRETED_ID = RTI.INTERPRETED_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.02 2 5 12 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.02 2 5 12 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS FULL RULE_TEMPLATE_VERSION 0 TABLE ACCESS BY INDEX ROWID RULE_TEMPLATE_INTERPRETED 0 INDEX UNIQUE SCAN (object id 10440) 0 TABLE ACCESS BY INDEX ROWID RULE_TEMPLATE 0 INDEX UNIQUE SCAN (object id 10435) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_TEMPLATE_VERSION' 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_TEMPLATE_INTERPRETED' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_TEMPLATE_INTERPRETED' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_TEMPLATE' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_TEMPLATE' (UNIQUE) ******************************************************************************** SELECT RT.RULE_TEMPLATE_ID, TO_CHAR(RTV.VERSION_DATE,'YYYY MM DD HH24 MI SS'), RTV.COMPILED_IND, RTC.RULE_NAME, RTC.APPL_MECHANISM_NUM, RTC.EVAL_MECHANISM_NUM FROM RULE_TEMPLATE RT, RULE_TEMPLATE_VERSION RTV, RULE_TEMPLATE_COMPILED RTC WHERE RT.RULE_PURPOSE_CODE = 2 AND RTV.WORK_STATE = 'P' AND RTV.COMPILED_IND = 'Y' AND RT.RULE_TEMPLATE_ID = RTV.RULE_TEMPLATE_ID AND RTV.RULE_NAME_ID = RTC.RULE_NAME_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 8 0.03 0.18 5 23 36 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10 0.03 0.18 5 23 36 7 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 7 HASH JOIN 27 TABLE ACCESS FULL RULE_TEMPLATE_VERSION 147 MERGE JOIN CARTESIAN 8 TABLE ACCESS FULL RULE_TEMPLATE 147 SORT JOIN 21 TABLE ACCESS FULL RULE_TEMPLATE_COMPILED Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 7 HASH JOIN 27 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_TEMPLATE_VERSION' 147 MERGE JOIN (CARTESIAN) 8 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_TEMPLATE' 147 SORT (JOIN) 21 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_TEMPLATE_COMPILED' ******************************************************************************** SELECT A.RULE_PACK_ID, TO_CHAR(B.VALID_FROM,'YYYY MM DD HH24 MI SS') FROM RULE_PACK A, GL_PACK B WHERE A.WORK_STATE = 'P' AND STATE_DATE < TO_DATE( '2006-12-16-00-00-00', 'YYYY-MM-DD-HH24-MI-SS' ) AND A.RULE_PURPOSE_CODE = 2 AND A.RULE_PACK_ID = B.RULE_PACK_ID ORDER BY B.VALID_FROM DESC call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.01 0.01 1 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.01 1 1 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID GL_PACK 1 INDEX FULL SCAN DESCENDING (object id 9232) 0 TABLE ACCESS BY INDEX ROWID RULE_PACK 0 INDEX UNIQUE SCAN (object id 10431) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'GL_PACK' 1 INDEX GOAL: ANALYZED (FULL SCAN DESCENDING) OF 'PK_GL_PACK' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_PACK' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_PACK' (UNIQUE) ******************************************************************************** SELECT RULE_MODEL_ID FROM RULE_MODEL WHERE WORK_STATE = 'P' AND STATE_DATE < TO_DATE( '2006-12-16-00-00-00', 'YYYY-MM-DD-HH24-MI-SS' ) AND RULE_PURPOSE_CODE = 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 1 0 4 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 0 4 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS FULL RULE_MODEL Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_MODEL' ******************************************************************************** SELECT DISTINCT RE.RULE_ELEMENT_ID, RE.RULE_TEMPLATE_ID FROM RULE_ELEMENT RE, RULE_ELEMENT_VERSION REV WHERE RE.RULE_PURPOSE_CODE = 2 AND REV.WORK_STATE = 'P' AND REV.VERSION_DATE < TO_DATE( '2006-12-16-00-00-00', 'YYYY-MM-DD-HH24-MI-SS' ) AND RE.RULE_ELEMENT_ID = REV.RULE_ELEMENT_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 1 0 4 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 0 4 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT UNIQUE 0 NESTED LOOPS 1 TABLE ACCESS FULL RULE_ELEMENT 0 TABLE ACCESS BY INDEX ROWID RULE_ELEMENT_VERSION 0 INDEX RANGE SCAN (object id 10412) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (UNIQUE) 0 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_ELEMENT' 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_ELEMENT_VERSION' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_RULE_ELEMENT_VERSION' (UNIQUE) ******************************************************************************** SELECT A.RULE_MECH_ID, C.EVAL_OR_APPL, C.LOGIC_TYPE_ID, A.LOGIC_SUBTYPE_ID, B.LOGIC_SUBTYPE_CODE FROM RULE_MECH A, RULE_LOGIC_SUBTYPE B, RULE_LOGIC_TYPE C WHERE WORK_STATE = 'P' AND STATE_DATE < TO_DATE( '2006-12-16-00-00-00', 'YYYY-MM-DD-HH24-MI-SS' ) AND A.LOGIC_SUBTYPE_ID = B.LOGIC_SUBTYPE_ID AND B.LOGIC_TYPE_ID = C.LOGIC_TYPE_ID AND A.RULE_PURPOSE_CODE = 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 1 0 4 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 0 4 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS FULL RULE_MECH 0 TABLE ACCESS BY INDEX ROWID RULE_LOGIC_SUBTYPE 0 INDEX UNIQUE SCAN (object id 10416) 0 TABLE ACCESS BY INDEX ROWID RULE_LOGIC_TYPE 0 INDEX UNIQUE SCAN (object id 10419) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_MECH' 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_LOGIC_SUBTYPE' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_LOGIC_SUBTYPE' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_LOGIC_TYPE' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_LOGIC_TYPE' (UNIQUE) ******************************************************************************** SELECT A.RULE_PACK_ID, A.RULE_MODEL_ID FROM RULE_MODEL_PACK A, RULE_PACK B WHERE A.RULE_PACK_ID = B.RULE_PACK_ID AND B.RULE_PURPOSE_CODE = 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 1 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 1 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 1 INDEX FULL SCAN (object id 10429) 0 TABLE ACCESS BY INDEX ROWID RULE_PACK 0 INDEX UNIQUE SCAN (object id 10431) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 1 INDEX GOAL: ANALYZED (FULL SCAN) OF 'PK_RULE_PACK_MODEL' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_PACK' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_PACK' (UNIQUE) ******************************************************************************** SELECT A.RULE_MODEL_ID, A.PRIORITY_NUM, A.RULE_ELEMENT_ID FROM RULE_ENTRY A, RULE_MODEL B WHERE A.RULE_MODEL_ID = B.RULE_MODEL_ID AND B.RULE_PURPOSE_CODE = 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 1 0 4 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.01 1 0 4 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 1 TABLE ACCESS FULL RULE_ENTRY 0 TABLE ACCESS BY INDEX ROWID RULE_MODEL 0 INDEX UNIQUE SCAN (object id 10427) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_ENTRY' 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_MODEL' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_MODEL' (UNIQUE) ******************************************************************************** SELECT A.RULE_ELEMENT_ID, A.RULE_MECH_ID, A.TEMPLATE_POSITION, B.LOGIC_SUBTYPE_ID, A.EVAL_OR_APPL FROM RULE_MECH_ELEMENT A, RULE_MECH B WHERE A.RULE_MECH_ID = B.RULE_MECH_ID AND B.RULE_PURPOSE_CODE = 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 1 0 4 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 0 4 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 1 TABLE ACCESS FULL RULE_MECH_ELEMENT 0 TABLE ACCESS BY INDEX ROWID RULE_MECH 0 INDEX UNIQUE SCAN (object id 10423) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'RULE_MECH_ELEMENT' 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_MECH' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKRULE_MECH' (UNIQUE) ******************************************************************************** SELECT TO_CHAR(REV.VERSION_DATE,'YYYY MM DD HH24 MI SS'), REVA.LINE_NUM, REVA.EVAL_OR_APPL, REVA.TEMPLATE_POSITION, REVA.RULE_VALUE, NVL( RME.CURRENCY, -1 ), DT.DATA_TYPE_CODE FROM RULE_ELEMENT_VERSION REV, RULE_ELEMENT_VALUE REVA, RULE_MECH_ELEMENT RME, RULE_MECH RM, RULE_LOGIC_SUBTYPE RLS, DATA_CLASS DC, DATA_TYPE DT WHERE REV.RULE_ELEMENT_ID = :rule_element_id AND REV.WORK_STATE = 'P' AND REV.RULE_ELEMENT_ID = REVA.RULE_ELEMENT_ID AND REV.VERSION = REVA.VERSION AND REVA.RULE_ELEMENT_ID = RME.RULE_ELEMENT_ID AND REVA.EVAL_OR_APPL = RME.EVAL_OR_APPL AND REVA.TEMPLATE_POSITION = RME.TEMPLATE_POSITION AND RME.RULE_MECH_ID = RM.RULE_MECH_ID AND RM.LOGIC_SUBTYPE_ID = RLS.LOGIC_SUBTYPE_ID AND RLS.DATA_CLASS_ID = DC.DATA_CLASS_ID AND DC.DATA_TYPE_ID = DT.DATA_TYPE_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 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 1 0.00 0.01 0 0 0 0 Misses in library cache during parse: 0 Parsing user id: 50 (BCH) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_ELEMENT_VERSION' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_RULE_ELEMENT_VERSION' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_ELEMENT_VALUE' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_RULE_ELEMENT_VALUE' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_MECH_ELEMENT' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RUME_EL' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_MECH' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKRULE_MECH' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'RULE_LOGIC_SUBTYPE' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RULE_LOGIC_SUBTYPE' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'DATA_CLASS' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_DATA_CLASS' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'DATA_TYPE' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_DATA_TYPE' (UNIQUE) ******************************************************************************** select ulcff1 ,ulcff2 ,ulcff3 ,ulcff4 into :b0:b1,:b2:b3,:b4:b5,:b6:b7 from USERLBL where ULTAG='AR_FC_BALANCE_GL' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID USERLBL 1 INDEX UNIQUE SCAN (object id 10676) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'USERLBL' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKUSERLBL' (UNIQUE) ******************************************************************************** select uldtff1 into :b0:b1 from USERLBL where ULTAG='AR_FC_BALANCE' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID USERLBL 1 INDEX UNIQUE SCAN (object id 10676) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'USERLBL' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKUSERLBL' (UNIQUE) ******************************************************************************** INSERT INTO DOCUMENT_REFERENCE ( DOCUMENT_ID, CUSTOMER_ID, BILLSEQNO, CONTR_GROUP, DATE_CREATED, OHXACT, CSLEVEL, TIMM_VERSION, SPEC_CTRL_GRP, BILL_INS_CODE, BILL_INFORMATION, BILLCYCLE ) VALUES ( :di, :cd, 1081, :cg, TO_DATE( '2006-12-16-00.00.00', 'YYYY-MM-DD-HH24.MI.SS' ), DECODE( :oh,0,NULL,:oh ), :CsLvl, '600', :scg, :ins , :bi, '02' ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 30 0.02 0.03 0 1 302 30 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 31 0.02 0.03 0 1 302 30 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE ******************************************************************************** insert into DOCUMENT_ALL (DOCUMENT_ID,SEQNO,TYPE_ID,CONTRACT_ID,SORT_CRITERIA, DOCUMENT_SIZE,DOCUMENT,DOC_FILE_CNT,MEDIUM) values (:b0,:b1,:b2,DECODE(:b3,0,null ,:b3),:b5,:b6,:b7,:b8,:b9) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 156 0.61 2.18 168 1816 9998 156 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 157 0.61 2.18 168 1816 9998 156 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE ******************************************************************************** select file# from file$ where ts#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.01 0.00 0 0 0 0 Execute 10 0.00 0.01 0 0 0 0 Fetch 20 0.00 0.00 0 30 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 40 0.01 0.01 0 30 0 10 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID FILE$ 2 INDEX RANGE SCAN (object id 39) ******************************************************************************** update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8 where ts#=:1 and user#=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.01 0.00 0 0 0 0 Execute 10 0.02 0.01 0 60 10 10 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 20 0.03 0.01 0 60 10 10 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE TSQ$ 2 TABLE ACCESS CLUSTER TSQ$ 2 INDEX UNIQUE SCAN (object id 11) ******************************************************************************** delete from BCH_PROCESS_CONTR where ((CUSTOMER_SET_ID=:b0 and BILLSEQNO=:b1) and BCH_PACKAGE_NUM=:b2) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.02 0.03 2 5 130 122 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.03 2 5 130 122 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 1 DELETE BCH_PROCESS_CONTR 123 TABLE ACCESS BY INDEX ROWID BCH_PROCESS_CONTR 123 INDEX RANGE SCAN (object id 8752) Rows Execution Plan ------- --------------------------------------------------- 0 DELETE STATEMENT GOAL: CHOOSE 1 DELETE OF 'BCH_PROCESS_CONTR' 123 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_BCH_PROCESS_CONTR' (UNIQUE) ******************************************************************************** select condition from cdef$ where rowid=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.00 0 4 0 2 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY USER ROWID CDEF$ ******************************************************************************** UPDATE BCH_PROCESS_CUST SET CUSTOMER_PROCESS_STATUS ='S' WHERE BILLSEQNO = 1081 AND CUSTOMER_SET_ID = :cust_set AND BCH_PACKAGE_NUM = :pack_num AND CUSTOMER_ID = :cust_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.41 33 81 31 31 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.41 33 81 31 31 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 31 UPDATE BCH_PROCESS_CUST 62 TABLE ACCESS BY INDEX ROWID BCH_PROCESS_CUST 62 INDEX RANGE SCAN (object id 46030) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 31 UPDATE OF 'BCH_PROCESS_CUST' 62 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'BCH_PROCESS_CUST' 62 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CUSTIDF' (NON-UNIQUE) ******************************************************************************** UPDATE MPUUBTAB SET LAST_BILLING_DURATION = :MilliSecs, REC_VERSION = REC_VERSION + 1 WHERE CUSTOMER_ID = :cd call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 71 42 31 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 71 42 31 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 31 UPDATE MPUUBTAB 31 INDEX UNIQUE SCAN (object id 9812) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 31 UPDATE OF 'MPUUBTAB' 31 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKMPUUBTAB' (UNIQUE) ******************************************************************************** update BCH_PROCESS_CUST set CUSTOMER_PROCESS_STATUS='I' where CUSTOMER_PROCESS_STATUS='D' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.02 0.02 0 541 8 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.02 0.02 0 541 8 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE BCH_PROCESS_CUST 1 TABLE ACCESS FULL BCH_PROCESS_CUST Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 1 UPDATE OF 'BCH_PROCESS_CUST' 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BCH_PROCESS_CUST' ******************************************************************************** BEGIN UPDATE BCH_CONTROL SET AMOUNT = NVL (AMOUNT, 0.0) + :amount, SUBSCR_CONT = NVL (SUBSCR_CONT ,0) + :subscr_cont, NO_OF_UNDEF_CALLS = NVL (NO_OF_UNDEF_CALLS,0) + :no_of_undef_calls, NO_OF_UNDEF_CLICKS = NVL (NO_OF_UNDEF_CLICKS,0) + :no_of_undef_clicks, UNDEF_RATED_VOLUME = NVL (UNDEF_RATED_VOLUME,0) + :undef_rated_volume, UNDEF_ROUND_VOLUME = NVL (UNDEF_ROUND_VOLUME,0) + :undef_round_volume, UNDEF_AMOUNT = NVL (UNDEF_AMOUNT, 0.0) + :undef_amount, CUSTOMER_NUM = NVL (CUSTOMER_NUM, 0) + :customer_num, CONTRACT_NUM = NVL (CONTRACT_NUM, 0) + :contract_num, RTX_NUM = NVL (RTX_NUM, 0) + :rtx_num, BILLING_DURATION = NVL (BILLING_DURATION, 0) + :billing_duration WHERE CUSTOMER_SET_ID = :customer_set_id AND BILLSEQNO = :billseqno AND BCH_CONTROL_ID = :bch_control_id; IF SQL%NOTFOUND THEN INSERT INTO BCH_CONTROL ( AMOUNT, SUBSCR_CONT, NO_OF_UNDEF_CALLS, NO_OF_UNDEF_CLICKS, UNDEF_RATED_VOLUME, UNDEF_ROUND_VOLUME, UNDEF_AMOUNT, CUSTOMER_NUM, CONTRACT_NUM, RTX_NUM, BILLING_DURATION, BCH_PROCESS_ID, END_DATE, CURRENCY, CUSTOMER_SET_ID, BILLSEQNO, BCH_CONTROL_ID ) VALUES ( :amount, :subscr_cont, :no_of_undef_calls, :no_of_undef_clicks, :undef_rated_volume, :undef_round_volume, :undef_amount, :customer_num, :contract_num, :rtx_num, :billing_duration, :bch_process_id, NULL, :currency, :customer_set_id, :billseqno, :bch_control_id ); END IF; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.01 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) ******************************************************************************** UPDATE BCH_CONTROL SET AMOUNT=NVL(AMOUNT,0.0) + :b1 ,SUBSCR_CONT= NVL(SUBSCR_CONT,0) + :b2 ,NO_OF_UNDEF_CALLS=NVL(NO_OF_UNDEF_CALLS,0) + :b3 , NO_OF_UNDEF_CLICKS=NVL(NO_OF_UNDEF_CLICKS,0) + :b4 ,UNDEF_RATED_VOLUME= NVL(UNDEF_RATED_VOLUME,0) + :b5 ,UNDEF_ROUND_VOLUME=NVL(UNDEF_ROUND_VOLUME, 0) + :b6 ,UNDEF_AMOUNT=NVL(UNDEF_AMOUNT,0.0) + :b7 ,CUSTOMER_NUM= NVL(CUSTOMER_NUM,0) + :b8 ,CONTRACT_NUM=NVL(CONTRACT_NUM,0) + :b9 ,RTX_NUM= NVL(RTX_NUM,0) + :b10 ,BILLING_DURATION=NVL(BILLING_DURATION,0) + :b11 WHERE CUSTOMER_SET_ID = :b12 AND BILLSEQNO = :b13 AND BCH_CONTROL_ID = :b14 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 1 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.01 1 1 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE BCH_CONTROL 1 INDEX UNIQUE SCAN (object id 8743) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 1 UPDATE OF 'BCH_CONTROL' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_BCH_CONTROL' (UNIQUE) ******************************************************************************** INSERT INTO BCH_CONTROL ( AMOUNT,SUBSCR_CONT,NO_OF_UNDEF_CALLS, NO_OF_UNDEF_CLICKS,UNDEF_RATED_VOLUME,UNDEF_ROUND_VOLUME,UNDEF_AMOUNT, CUSTOMER_NUM,CONTRACT_NUM,RTX_NUM,BILLING_DURATION,BCH_PROCESS_ID,END_DATE, CURRENCY,CUSTOMER_SET_ID,BILLSEQNO,BCH_CONTROL_ID ) VALUES ( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12, NULL ,:b13,:b14,:b15, :b16 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.04 2 1 5 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.04 2 1 5 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE ******************************************************************************** begin ReferenceNumber . AllocRefnums ( :basetab , :basecol , :status , :typ , :count1 , :vdate , :retvalue , :refarr ) ; END ; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) ******************************************************************************** SELECT RA.REFNUM_ID,REFNUM_MAINTENANCE_TYPE,RANGE_AUTOEXTENSION_TYPE, REFNUM_VERSION,VALID_FROM,PERIOD_TYPE,FIRST_REF_VALUE,REFNUM_PREFIX, REFNUM_SUFFIX,VALUE_LENGTH,RANGE_LENGTH FROM REFNUM_ACCESS RA,REFNUM_BASE RB,REFNUM_VERSION RV WHERE REFERENCE_TABLE = :b1 AND REFERENCE_COLUMN = :b2 AND REFERENCE_CHAR_CONDITION = :b3 AND REFERENCE_INT_CONDITION = :b4 AND RA.REFNUM_ID = RB.REFNUM_ID AND RB.REFNUM_ID = RV.REFNUM_ID AND VALID_FROM = (SELECT MAX(VALID_FROM) FROM REFNUM_VERSION WHERE REFNUM_ID = RB.REFNUM_ID AND VALID_FROM <= :b5 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.22 5 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.22 5 7 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 256 (SYSADM) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID REFNUM_ACCESS 1 INDEX UNIQUE SCAN (object id 10357) 1 TABLE ACCESS BY INDEX ROWID REFNUM_BASE 1 INDEX UNIQUE SCAN (object id 10359) 1 TABLE ACCESS BY INDEX ROWID REFNUM_VERSION 1 INDEX UNIQUE SCAN (object id 10368) 2 SORT AGGREGATE 1 FIRST ROW 1 INDEX RANGE SCAN (MIN/MAX) (object id 10368) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'REFNUM_ACCESS' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_REFNUM_ACCESS' (UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'REFNUM_BASE' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_REFNUM_BASE' (UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'REFNUM_VERSION' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'UK_REFNUM_VERSION' (UNIQUE) 2 SORT (AGGREGATE) 1 FIRST ROW 1 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF 'UK_REFNUM_VERSION' (UNIQUE) ******************************************************************************** SELECT PERIOD_CNT,PERIOD_ACTIVE_IND FROM REFNUM_PERIOD WHERE REFNUM_ID = :b1 AND REFNUM_VERSION = :b2 AND PERIOD_START_DATE = :b3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.05 2 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.05 2 2 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 256 (SYSADM) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID REFNUM_PERIOD 1 INDEX UNIQUE SCAN (object id 10362) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'REFNUM_PERIOD' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'UK_REFNUM_PERIOD' (UNIQUE) ******************************************************************************** SELECT MIN(RANGE_CNT) FROM REFNUM_VALUE WHERE REFNUM_ID = :b1 AND REFNUM_VERSION = :b2 AND PERIOD_CNT = :b3 AND RANGE_ACTIVE_IND = 'Y' AND (LAST_REFERENCE_DATE <= :b4 OR :b5 = 0 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.02 2 1 4 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.02 2 1 4 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 256 (SYSADM) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 1 TABLE ACCESS FULL REFNUM_VALUE Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'REFNUM_VALUE' ******************************************************************************** SELECT NEXT_REF_VALUE FROM REFNUM_VALUE WHERE REFNUM_ID = :b1 AND REFNUM_VERSION = :b2 AND PERIOD_CNT = :b3 AND RANGE_CNT = :b4 FOR UPDATE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 1 2 1 0 Fetch 1 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 2 1 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 256 (SYSADM) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FOR UPDATE 1 FILTER 1 TABLE ACCESS BY INDEX ROWID REFNUM_VALUE 1 INDEX UNIQUE SCAN (object id 10366) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 FOR UPDATE 1 FILTER 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'REFNUM_VALUE' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_REFNUM_VALUE' (UNIQUE) ******************************************************************************** UPDATE REFNUM_VALUE SET NEXT_REF_VALUE=NEXT_REF_VALUE + :b1 , LAST_REFERENCE_DATE=:b2 WHERE REFNUM_ID = :b3 AND REFNUM_VERSION = :b4 AND PERIOD_CNT = :b5 AND RANGE_CNT = :b6 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 1 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 256 (SYSADM) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE REFNUM_VALUE 1 FILTER 1 INDEX UNIQUE SCAN (object id 10366) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 1 UPDATE OF 'REFNUM_VALUE' 1 FILTER 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_REFNUM_VALUE' (UNIQUE) ******************************************************************************** update DOCUMENT_REFERENCE set INVOICE_REFNUM=:b1,OHXACT=DECODE(:b2,0,OHXACT, :b2) where DOCUMENT_ID=:b4 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 2 30 30 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 0 2 30 30 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 30 UPDATE DOCUMENT_REFERENCE 30 INDEX UNIQUE SCAN (object id 9103) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 30 UPDATE OF 'DOCUMENT_REFERENCE' 30 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_DOCUMENT_REFERENCE' (UNIQUE) ******************************************************************************** SELECT STOPIND FROM BCH_MONITOR_TABLE WHERE BILLCYCLE = :BillCycle AND CONTROL_GROUP_IND = :CntrlGroupInd AND BILL_INFORMATION_IND = :BillInfoInd call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'BCH_MONITOR_TABLE' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_BCH_MONITOR_TABLE' (UNIQUE) ******************************************************************************** UPDATE BCH_CONTROL SET END_DATE = TO_DATE( '2006-12-17-13.00.08', 'YYYY-MM-DD-HH24.MI.SS' ) WHERE BILLSEQNO = 1081 AND BCH_CONTROL_ID = 6281 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 7 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 7 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 50 (BCH) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE BCH_CONTROL 2 TABLE ACCESS FULL BCH_CONTROL Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 1 UPDATE OF 'BCH_CONTROL' 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BCH_CONTROL' ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 32 0.02 0.05 0 0 0 0 Execute 216 0.70 2.71 203 2518 10548 403 Fetch 72 0.08 0.56 27 272 336 320 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 320 0.80 3.32 230 2790 10884 723 Misses in library cache during parse: 5 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 29 0.02 0.00 0 0 0 0 Execute 29 0.04 0.08 4 65 17 12 Fetch 26 0.00 0.29 9 44 4 16 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 84 0.06 0.37 13 109 21 28 Misses in library cache during parse: 0 40 user SQL statements in session. 22 internal SQL statements in session. 62 SQL statements in session. 33 statements EXPLAINed in this session. ******************************************************************************** Trace file: sty_prod_ora_1062.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 40 user SQL statements in trace file. 22 internal SQL statements in trace file. 62 SQL statements in trace file. 42 unique SQL statements in trace file. 33 SQL statements EXPLAINed using schema: SYSADM.prof$plan_table Default table was used. Table was created. Table was dropped. 1120 lines in trace file.