TKPROF: Release 8.0.6.3.0 - Production on Thu Jan 4 05:37:32 2007 (c) Copyright 1999 Oracle Corporation. All rights reserved. Trace file: tcrm_ora_11191.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 ******************************************************************************** ALTER SESSION SET SQL_TRACE TRUE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 12.53 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 12.53 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: ALL_ROWS Parsing user id: 173 ******************************************************************************** BEGIN FND_CONCURRENT.INIT_SQL_REQUEST; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 3.30 0 0 0 0 Execute 1 400.00 527.40 0 0 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 400.00 530.70 0 0 1 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 ******************************************************************************** SELECT PROCESS, MACHINE FROM V$SESSION WHERE AUDSID = USERENV('sessionid') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 3.95 0 0 0 0 Execute 1 0.00 3.43 0 0 0 0 Fetch 1 100.00 64.91 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 100.00 72.29 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** SELECT 0, FCR.REQUESTED_BY, FCR.RESPONSIBILITY_ID, FCR.RESPONSIBILITY_APPLICATION_ID, FCR.SECURITY_GROUP_ID, 0, FCR.REQUESTED_BY, FCR.CONC_LOGIN_ID, FCR.PROGRAM_APPLICATION_ID, FCR.CONCURRENT_PROGRAM_ID, FCR.REQUEST_ID, FCR.PRIORITY_REQUEST_ID FROM FND_CONCURRENT_REQUESTS FCR, FND_CP_SQL_REQUESTS SR WHERE FCR.PHASE_CODE = 'R' AND FCR.STATUS_CODE = 'R' AND FCR.REQUEST_ID = SR.REQUEST_ID AND SR.MACHINE = :B2 AND SR.CLIENT_PROCESS_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.89 0 0 0 0 Execute 1 0.00 6.45 0 0 0 0 Fetch 1 0.00 11.30 0 10 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 20.64 0 10 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** SELECT U.USER_NAME, NVL(U.EMPLOYEE_ID, -1), NVL(U.CUSTOMER_ID, -1), NVL(U.SUPPLIER_ID, -1), NVL(U.PERSON_PARTY_ID, -1) FROM FND_USER U WHERE U.USER_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.66 0 0 0 0 Execute 1 0.00 2.65 0 0 0 0 Fetch 1 0.00 1.37 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 6.68 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID FND_USER (cr=2 pr=0 pw=0 time=91 us) 1 INDEX UNIQUE SCAN FND_USER_U1 (cr=1 pr=0 pw=0 time=47 us)(object id 33427) ******************************************************************************** SELECT A.APPLICATION_SHORT_NAME FROM FND_APPLICATION A WHERE A.APPLICATION_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.38 0 0 0 0 Execute 1 0.00 1.77 0 0 0 0 Fetch 1 0.00 0.60 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 3.75 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID FND_APPLICATION (cr=2 pr=0 pw=0 time=61 us) 1 INDEX UNIQUE SCAN FND_APPLICATION_U1 (cr=1 pr=0 pw=0 time=33 us)(object id 32802) ******************************************************************************** SELECT USERENV('SESSIONID') FROM SYS.DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.33 0 0 0 0 Execute 1 0.00 0.65 0 0 0 0 Fetch 1 0.00 0.24 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 2.22 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FAST DUAL (cr=0 pr=0 pw=0 time=6 us) ******************************************************************************** SELECT FPOV.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES FPOV, FND_PROFILE_OPTIONS FPO WHERE FPO.PROFILE_OPTION_NAME = 'AFCORE_LOGGING_ENABLED' AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID AND FPOV.LEVEL_ID = 10001 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.94 0 0 0 0 Execute 1 0.00 2.37 0 0 0 0 Fetch 1 0.00 31.83 0 56 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 37.14 0 56 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS (cr=56 pr=0 pw=0 time=3212 us) 1 TABLE ACCESS BY INDEX ROWID FND_PROFILE_OPTIONS (cr=3 pr=0 pw=0 time=161 us) 1 INDEX UNIQUE SCAN FND_PROFILE_OPTIONS_U2 (cr=2 pr=0 pw=0 time=105 us)(object id 33241) 0 TABLE ACCESS FULL FND_PROFILE_OPTION_VALUES (cr=53 pr=0 pw=0 time=3041 us) ******************************************************************************** SELECT FPOV.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES FPOV, FND_PROFILE_OPTIONS FPO WHERE FPO.PROFILE_OPTION_NAME = 'AFCORE_LOGGING_PROFILE_OPTION' AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID AND FPOV.LEVEL_ID = 10001 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.12 0 0 0 0 Execute 1 0.00 1.78 0 0 0 0 Fetch 1 0.00 29.49 0 56 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 33.39 0 56 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS (cr=56 pr=0 pw=0 time=2960 us) 1 TABLE ACCESS BY INDEX ROWID FND_PROFILE_OPTIONS (cr=3 pr=0 pw=0 time=80 us) 1 INDEX UNIQUE SCAN FND_PROFILE_OPTIONS_U2 (cr=2 pr=0 pw=0 time=47 us)(object id 33241) 0 TABLE ACCESS FULL FND_PROFILE_OPTION_VALUES (cr=53 pr=0 pw=0 time=2869 us) ******************************************************************************** SELECT PROFILE_OPTION_ID, APPLICATION_ID, SITE_ENABLED_FLAG , APP_ENABLED_FLAG , RESP_ENABLED_FLAG , USER_ENABLED_FLAG, ORG_ENABLED_FLAG , SERVER_ENABLED_FLAG, SERVERRESP_ENABLED_FLAG, HIERARCHY_TYPE, USER_CHANGEABLE_FLAG FROM FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME = UPPER(:B1 ) AND START_DATE_ACTIVE <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 2.96 0 0 0 0 Execute 11 0.00 26.06 0 0 0 0 Fetch 11 0.00 11.35 0 33 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 24 0.00 40.37 0 33 0 11 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID FND_PROFILE_OPTIONS (cr=6 pr=0 pw=0 time=288 us) 2 INDEX UNIQUE SCAN FND_PROFILE_OPTIONS_U2 (cr=4 pr=0 pw=0 time=157 us)(object id 33241) ******************************************************************************** SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID = :B4 AND APPLICATION_ID = :B3 AND LEVEL_ID = 10003 AND LEVEL_VALUE = :B2 AND LEVEL_VALUE_APPLICATION_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 2.44 0 0 0 0 Execute 8 0.00 10.09 0 0 0 0 Fetch 8 100.00 4.28 0 17 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 18 100.00 16.81 0 17 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID FND_PROFILE_OPTION_VALUES (cr=4 pr=0 pw=0 time=177 us) 0 INDEX RANGE SCAN FND_PROFILE_OPTION_VALUES_U1 (cr=4 pr=0 pw=0 time=160 us)(object id 584282) ******************************************************************************** SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID = :B4 AND APPLICATION_ID = :B3 AND LEVEL_ID = :B2 AND LEVEL_VALUE = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 2.47 0 0 0 0 Execute 21 100.00 21.37 0 0 0 0 Fetch 21 0.00 12.80 0 51 0 9 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 44 100.00 36.64 0 51 0 9 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID FND_PROFILE_OPTION_VALUES (cr=6 pr=0 pw=0 time=164 us) 2 INDEX RANGE SCAN FND_PROFILE_OPTION_VALUES_U1 (cr=4 pr=0 pw=0 time=94 us)(object id 584282) ******************************************************************************** SELECT NAME, VERSION FROM FND_CACHE_VERSIONS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.55 0 0 0 0 Execute 1 0.00 0.96 0 0 0 0 Fetch 1 0.00 1.78 0 1 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 4.29 0 1 0 3 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 3 INDEX FULL SCAN FND_CACHE_VERSIONS_PK1 (cr=1 pr=0 pw=0 time=115 us)(object id 590087) ******************************************************************************** SELECT R.RESPONSIBILITY_NAME FROM FND_RESPONSIBILITY_VL R WHERE R.RESPONSIBILITY_ID = :B2 AND R.APPLICATION_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.41 0 0 0 0 Execute 2 0.00 2.72 0 0 0 0 Fetch 2 0.00 2.56 0 10 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 6.69 0 10 0 2 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 NESTED LOOPS (cr=10 pr=0 pw=0 time=258 us) 2 INDEX UNIQUE SCAN FND_RESPONSIBILITY_U1 (cr=4 pr=0 pw=0 time=89 us)(object id 33329) 2 TABLE ACCESS BY INDEX ROWID FND_RESPONSIBILITY_TL (cr=6 pr=0 pw=0 time=152 us) 2 INDEX UNIQUE SCAN FND_RESPONSIBILITY_TL_U1 (cr=4 pr=0 pw=0 time=96 us)(object id 33479) ******************************************************************************** SELECT NODE_NAME FROM FND_NODES WHERE NODE_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.15 0 0 0 0 Execute 1 0.00 0.92 0 0 0 0 Fetch 1 0.00 6.91 0 6 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 8.98 0 6 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS FULL FND_NODES (cr=6 pr=0 pw=0 time=690 us) ******************************************************************************** select NAME from HR_OPERATING_UNITS where ORGANIZATION_ID = :zorg_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.01 0 0 0 0 Execute 1 100.00 2.12 0 0 0 0 Fetch 1 0.00 5.39 0 6 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 100.00 9.52 0 6 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 MERGE JOIN CARTESIAN (cr=6 pr=0 pw=0 time=563 us) 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=154 us) 1 NESTED LOOPS (cr=3 pr=0 pw=0 time=98 us) 1 TABLE ACCESS BY INDEX ROWID HR_ALL_ORGANIZATION_UNITS_TL (cr=2 pr=0 pw=0 time=74 us) 1 INDEX UNIQUE SCAN HR_ALL_ORGANIZATION_UNTS_TL_PK (cr=1 pr=0 pw=0 time=47 us)(object id 44637) 1 INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK (cr=1 pr=0 pw=0 time=19 us)(object id 43498) 1 INDEX RANGE SCAN HR_ORGANIZATION_INFORMATIO_FK2 (cr=1 pr=0 pw=0 time=51 us)(object id 596105) 1 BUFFER SORT (cr=2 pr=0 pw=0 time=397 us) 1 TABLE ACCESS BY INDEX ROWID HR_ORGANIZATION_INFORMATION (cr=2 pr=0 pw=0 time=188 us) 2 INDEX RANGE SCAN HR_ORGANIZATION_INFORMATIO_FK2 (cr=1 pr=0 pw=0 time=41 us)(object id 596105) ******************************************************************************** SELECT SUBSTRB(MACHINE,1,60), PROCESS, PROGRAM FROM V$SESSION WHERE AUDSID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.44 0 0 0 0 Execute 1 0.00 1.22 0 0 0 0 Fetch 1 100.00 58.54 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 100.00 62.20 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=5791 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=5765 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=18 us) ******************************************************************************** SELECT NVL(MULTI_ORG_FLAG, 'N'), NVL(MULTI_CURRENCY_FLAG, 'N') FROM FND_PRODUCT_GROUPS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.93 0 0 0 0 Execute 1 0.00 1.10 0 0 0 0 Fetch 1 0.00 4.25 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 7.28 0 3 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS FULL FND_PRODUCT_GROUPS (cr=3 pr=0 pw=0 time=408 us) ******************************************************************************** SELECT DISTINCT PRODUCT_DEPENDENCY, LEVEL FROM FND_PRODUCT_INIT_DEPENDENCY P1 WHERE LEVEL = (SELECT MAX(LEVEL) FROM FND_PRODUCT_INIT_DEPENDENCY P2 WHERE P2.PRODUCT_DEPENDENCY = P1.PRODUCT_DEPENDENCY CONNECT BY PRIOR P2.PRODUCT_DEPENDENCY = P2.APPLICATION_SHORT_NAME START WITH P2.APPLICATION_SHORT_NAME = UPPER(:B1 ) ) CONNECT BY PRIOR PRODUCT_DEPENDENCY = APPLICATION_SHORT_NAME START WITH APPLICATION_SHORT_NAME = UPPER(:B1 ) ORDER BY LEVEL DESC call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.92 0 0 0 0 Execute 1 0.00 7.13 0 0 0 0 Fetch 1 0.00 1.37 0 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 10.42 0 1 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT UNIQUE (cr=1 pr=0 pw=0 time=448 us) 0 FILTER (cr=1 pr=0 pw=0 time=355 us) 0 CONNECT BY WITH FILTERING (cr=1 pr=0 pw=0 time=349 us) 0 TABLE ACCESS BY INDEX ROWID FND_PRODUCT_INIT_DEPENDENCY (cr=1 pr=0 pw=0 time=79 us) 0 INDEX RANGE SCAN FND_PRODUCT_INIT_DEPENDENCY_U1 (cr=1 pr=0 pw=0 time=69 us)(object id 108134) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us) 0 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN FND_PRODUCT_INIT_DEPENDENCY_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 108134) 0 TABLE ACCESS FULL FND_PRODUCT_INIT_DEPENDENCY (cr=0 pr=0 pw=0 time=0 us) 0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us) 0 FILTER (cr=0 pr=0 pw=0 time=0 us) 0 CONNECT BY WITH FILTERING (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID FND_PRODUCT_INIT_DEPENDENCY (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN FND_PRODUCT_INIT_DEPENDENCY_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 108134) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us) 0 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN FND_PRODUCT_INIT_DEPENDENCY_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 108134) 0 TABLE ACCESS FULL FND_PRODUCT_INIT_DEPENDENCY (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** SELECT INIT_FUNCTION_NAME FROM FND_PRODUCT_INITIALIZATION WHERE APPLICATION_SHORT_NAME = :B1 AND EXISTS (SELECT 1 FROM FND_PRODUCT_INIT_CONDITION C WHERE C.APPLICATION_SHORT_NAME = :B1 AND C.RE_INIT_CONDITION IN (:B6 , :B5 , :B4 , :B3 , :B2 )) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.57 0 0 0 0 Execute 1 0.00 3.43 0 5 0 0 Fetch 1 0.00 0.33 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 5.33 0 5 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 FILTER (cr=5 pr=0 pw=0 time=250 us) 0 INDEX RANGE SCAN FND_PRODUCT_INITIALIZATION_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 108129) 0 INLIST ITERATOR (cr=5 pr=0 pw=0 time=181 us) 0 INDEX RANGE SCAN FND_PRODUCT_INIT_CONDITION_U1 (cr=5 pr=0 pw=0 time=118 us)(object id 108132) ******************************************************************************** DELETE FROM FND_CP_SQL_REQUESTS WHERE REQUEST_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.95 0 0 0 0 Execute 1 0.00 18.03 0 6 3 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 19.98 0 6 3 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** SELECT P.PID, P.SPID, AUDSID, PROCESS, SUBSTR(USERENV('LANGUAGE'), INSTR( USERENV('LANGUAGE'), '.') + 1) FROM V$SESSION S, V$PROCESS P WHERE P.ADDR = S.PADDR AND S.AUDSID = USERENV('SESSIONID') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.75 0 0 0 0 Execute 1 0.00 1.66 0 0 0 0 Fetch 1 100.00 119.22 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 100.00 122.63 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=11826 us) 1 HASH JOIN (cr=0 pr=0 pw=0 time=11782 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=5978 us) 217 FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=742 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=28 us) ******************************************************************************** UPDATE FND_CONCURRENT_REQUESTS SET ORACLE_SESSION_ID = :B4 , ORACLE_PROCESS_ID = :B3 , OS_PROCESS_ID = :B2 , NLS_CODESET = :B1 WHERE REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.29 0 0 0 0 Execute 1 100.00 19.59 0 3 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 100.00 21.88 0 3 1 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE (cr=3 pr=0 pw=0 time=1533 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_REQUESTS_U1 (cr=3 pr=0 pw=0 time=895 us)(object id 34547) ******************************************************************************** SELECT P.OPTIMIZER_MODE, P.CONCURRENT_PROGRAM_NAME, UPPER(P.ENABLE_TRACE), UPPER(R.ENABLE_TRACE), DECODE(UPPER(P.ENABLE_TIME_STATISTICS),'Y','TRUE', NULL), EXECUTION_METHOD_CODE FROM FND_CONCURRENT_PROGRAMS P, FND_CONCURRENT_REQUESTS R WHERE P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID AND P.APPLICATION_ID = R.PROGRAM_APPLICATION_ID AND R.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 2.96 0 0 0 0 Execute 1 0.00 4.96 0 0 0 0 Fetch 1 0.00 2.35 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 10.27 0 7 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=7 pr=0 pw=0 time=534 us) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_REQUESTS (cr=4 pr=0 pw=0 time=460 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_REQUESTS_U1 (cr=3 pr=0 pw=0 time=399 us)(object id 34547) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROGRAMS (cr=3 pr=0 pw=0 time=64 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_U1 (cr=2 pr=0 pw=0 time=36 us)(object id 33183) ******************************************************************************** SELECT PLSQL_LOG, PLSQL_OUT, PLSQL_DIR FROM FND_CONCURRENT_PROCESSES P, FND_CONCURRENT_REQUESTS R WHERE P.CONCURRENT_PROCESS_ID = R.CONTROLLING_MANAGER AND R.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.46 0 0 0 0 Execute 1 0.00 2.72 0 0 0 0 Fetch 1 0.00 1.37 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 5.55 0 7 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=7 pr=0 pw=0 time=276 us) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_REQUESTS (cr=4 pr=0 pw=0 time=212 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_REQUESTS_U1 (cr=3 pr=0 pw=0 time=189 us)(object id 34547) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROCESSES (cr=3 pr=0 pw=0 time=56 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_PROCESSES_U1 (cr=2 pr=0 pw=0 time=28 us)(object id 33070) ******************************************************************************** ALTER SESSION SET SQL_TRACE = TRUE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.61 0 0 0 0 Execute 1 0.00 12.69 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 14.30 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** SELECT SUBSTR(REPLACE(USER_CONCURRENT_PROGRAM_NAME,'''',' '),1,240), SUBSTR(CONCURRENT_PROGRAM_NAME,1,30) FROM FND_CONCURRENT_PROGRAMS_VL WHERE CONCURRENT_PROGRAM_ID = :B1 AND APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.45 0 0 0 0 Execute 1 0.00 3.12 0 0 0 0 Fetch 1 0.00 1.58 0 6 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 6.15 0 6 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=6 pr=0 pw=0 time=301 us) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROGRAMS (cr=3 pr=0 pw=0 time=219 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_U1 (cr=2 pr=0 pw=0 time=199 us)(object id 33183) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROGRAMS_TL (cr=3 pr=0 pw=0 time=74 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_TL_U1 (cr=2 pr=0 pw=0 time=43 us)(object id 33634) ******************************************************************************** SELECT TO_CHAR(SYSDATE,'DD-MON-YY:HH24:MI:SS') FROM SYS.DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.27 0 0 0 0 Execute 1 100.00 1.58 0 0 0 0 Fetch 1 0.00 2.66 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 100.00 5.51 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** SELECT 'TRACE_USER_DETAILS: TIMESTAMP=03-JAN-07:16:25:02: USER_NAME=CPET: CONC_REQUEST_ID=6489063: OLTP_OPT_MODE= : CONC_OPT_MODE= : CONC_PROG_EXE= XXINA_CPET_CUST_ORD_UPDATE: CONC_PROG_NAME=Synchronize Customer Update: RESPONSIBILITY=Customer Profile Enquiry Tool: APPL_NAME=XXCPET' FROM DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 54.65 0 0 0 0 Execute 1 0.00 1.25 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 55.90 0 0 0 0 Misses in library cache during parse: 1 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** SELECT Q.RESOURCE_CONSUMER_GROUP FROM FND_CONCURRENT_REQUESTS R, FND_CONCURRENT_PROCESSES P, FND_CONCURRENT_QUEUES Q WHERE R.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID AND R.CONTROLLING_MANAGER = P.CONCURRENT_PROCESS_ID AND Q.CONCURRENT_QUEUE_ID= P.CONCURRENT_QUEUE_ID AND Q.APPLICATION_ID = P.QUEUE_APPLICATION_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.83 0 0 0 0 Execute 1 0.00 5.10 0 0 0 0 Fetch 1 0.00 3.30 0 9 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 10.23 0 9 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=9 pr=0 pw=0 time=677 us) 1 NESTED LOOPS (cr=7 pr=0 pw=0 time=528 us) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_REQUESTS (cr=4 pr=0 pw=0 time=466 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_REQUESTS_U1 (cr=3 pr=0 pw=0 time=427 us)(object id 34547) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROCESSES (cr=3 pr=0 pw=0 time=55 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_PROCESSES_U1 (cr=2 pr=0 pw=0 time=31 us)(object id 33070) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_QUEUES (cr=2 pr=0 pw=0 time=137 us) 1 INDEX RANGE SCAN FND_CONCURRENT_QUEUES_N2 (cr=1 pr=0 pw=0 time=84 us)(object id 590046) ******************************************************************************** SELECT P.RESOURCE_CONSUMER_GROUP FROM FND_CONCURRENT_PROGRAMS P, FND_CONCURRENT_REQUESTS R WHERE R.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID AND R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.40 0 0 0 0 Execute 1 0.00 2.56 0 0 0 0 Fetch 1 0.00 1.27 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 5.23 0 7 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=7 pr=0 pw=0 time=275 us) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_REQUESTS (cr=4 pr=0 pw=0 time=213 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_REQUESTS_U1 (cr=3 pr=0 pw=0 time=189 us)(object id 34547) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROGRAMS (cr=3 pr=0 pw=0 time=56 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_U1 (cr=2 pr=0 pw=0 time=36 us)(object id 33183) ******************************************************************************** SELECT DECODE(:B1 , 'P', 'REPORTS', 'I', 'PLSQL_CP', 'J', 'JAVA_STORED_CP', 'K', 'JAVA_CP', 'Q', 'SQLPLUS_CP', 'E', 'PERL_CP', 'S', 'SUBROUTINE_CP', 'A', 'HOST_CP', 'H', 'HOST_CP', 'L', 'LOADER_CP', 'NOT_SUPPORTED') FROM DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.62 0 0 0 0 Execute 1 0.00 1.13 0 0 0 0 Fetch 1 0.00 0.24 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 2.99 0 0 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FAST DUAL (cr=0 pr=0 pw=0 time=6 us) ******************************************************************************** SELECT ENABLE_ROUTINE, DISABLE_ROUTINE, DRO.DEBUG_OPTION_NAME, DRO.DEBUG_OPTION_VALUE, DR.DEBUG_RULE_ID, REPEATION_COUNTER, START_TIME, END_TIME, USER_ID, RESPONSIBILITY_ID, RESP_APPL_ID, COMPONENT_TYPE, COMPONENT_NAME, COMPONENT_ID, COMPONENT_APPL_ID, TRACE_FILE_ROUTINE, TRACE_FILE_NODE, COMMENTS, NVL(REQUEST_ID,0) REQID FROM FND_DEBUG_OPTIONS DO, FND_DEBUG_RULES DR, FND_DEBUG_OPTION_VALUES DOV, FND_DEBUG_RULE_OPTIONS DRO WHERE ( DR.USER_ID = :B8 OR (DR.RESPONSIBILITY_ID = :B7 AND DR.RESP_APPL_ID = :B6 ) OR (DR.USER_ID IS NULL AND DR.RESPONSIBILITY_ID IS NULL) ) AND (( SYSDATE >= DR.START_TIME AND SYSDATE <= DR.END_TIME) OR DR.REPEATION_COUNTER > 0 ) AND ( DR.COMPONENT_NAME = :B5 OR (DR.COMPONENT_ID = :B4 AND DR.COMPONENT_APPL_ID = :B3 ) ) AND DR.COMPONENT_TYPE = :B2 AND DRO.DEBUG_OPTION_NAME = DOV.DEBUG_OPTION_NAME AND DRO.DEBUG_OPTION_VALUE = DOV.DEBUG_OPTION_VALUE AND DRO.DEBUG_OPTION_NAME = DO.DEBUG_OPTION_NAME AND DO.TYPE = 'D' AND DO.ENABLED_FLAG = 'Y' AND ((DR.REQUEST_ID IS NOT NULL AND :B1 = DR.REQUEST_ID) OR (DR.REQUEST_ID IS NULL) ) AND DR.DEBUG_RULE_ID = DRO.DEBUG_RULE_ID AND DR.DEBUG_RULE_ID=(SELECT MIN(DEBUG_RULE_ID) FROM FND_DEBUG_RULES IDR WHERE (IDR.USER_ID = :B8 OR (IDR.RESPONSIBILITY_ID = :B7 AND IDR.RESP_APPL_ID = :B6 ) OR (IDR.USER_ID IS NULL AND IDR.RESPONSIBILITY_ID IS NULL) ) AND (( SYSDATE >= IDR.START_TIME AND SYSDATE <= IDR.END_TIME) OR IDR.REPEATION_COUNTER > 0 ) AND ( IDR.COMPONENT_NAME = :B5 OR (IDR.COMPONENT_ID = :B4 AND IDR.COMPONENT_APPL_ID = :B3 ) ) AND IDR.COMPONENT_TYPE = :B2 AND ((IDR.REQUEST_ID IS NOT NULL AND :B1 = IDR.REQUEST_ID) OR (IDR.REQUEST_ID IS NULL) ) ) ORDER BY REQID DESC, DR.CREATION_DATE, DR.DEBUG_RULE_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 3.05 0 0 0 0 Execute 1 0.00 4.38 0 0 0 0 Fetch 1 0.00 1.31 0 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 8.74 0 1 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=1 pr=0 pw=0 time=194 us) 0 NESTED LOOPS (cr=1 pr=0 pw=0 time=75 us) 0 NESTED LOOPS (cr=1 pr=0 pw=0 time=73 us) 0 NESTED LOOPS (cr=1 pr=0 pw=0 time=69 us) 0 TABLE ACCESS BY INDEX ROWID FND_DEBUG_RULES (cr=1 pr=0 pw=0 time=65 us) 0 INDEX RANGE SCAN FND_DEBUG_RULES_N1 (cr=1 pr=0 pw=0 time=53 us)(object id 589952) 0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID FND_DEBUG_RULES (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN FND_DEBUG_RULES_N1 (cr=0 pr=0 pw=0 time=0 us)(object id 589952) 0 TABLE ACCESS BY INDEX ROWID FND_DEBUG_RULE_OPTIONS (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN FND_DEBUG_RULE_OPTIONS_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 589964) 0 TABLE ACCESS BY INDEX ROWID FND_DEBUG_OPTIONS (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN FND_DEBUG_OPTIONS_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 589924) 0 TABLE ACCESS BY INDEX ROWID FND_DEBUG_OPTION_VALUES (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN FND_DEBUG_OPTION_VALUES_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 589937) ******************************************************************************** DELETE FROM FND_DEBUG_RULE_OPTIONS WHERE DEBUG_RULE_ID IN (SELECT DEBUG_RULE_ID FROM FND_DEBUG_RULES WHERE (START_TIME IS NOT NULL AND END_TIME < SYSDATE) OR (REPEATION_COUNTER = 0) ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.37 0 0 0 0 Execute 1 0.00 16.57 0 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 17.94 0 1 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE (cr=1 pr=0 pw=0 time=1418 us) 0 HASH JOIN SEMI (cr=1 pr=0 pw=0 time=1291 us) 0 INDEX FULL SCAN FND_DEBUG_RULE_OPTIONS_U1 (cr=1 pr=0 pw=0 time=44 us)(object id 589964) 0 TABLE ACCESS FULL FND_DEBUG_RULES (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** DELETE FROM FND_DEBUG_RULES WHERE (START_TIME IS NOT NULL AND END_TIME < SYSDATE) OR (REPEATION_COUNTER = 0) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.68 0 0 0 0 Execute 1 0.00 3.57 0 3 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 5.25 0 3 0 0 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE (cr=3 pr=0 pw=0 time=189 us) 0 TABLE ACCESS FULL FND_DEBUG_RULES (cr=3 pr=0 pw=0 time=184 us) ******************************************************************************** SELECT P.ROLLBACK_SEGMENT FROM FND_CONCURRENT_PROGRAMS P, FND_CONCURRENT_REQUESTS R WHERE R.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID AND R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1.40 0 0 0 0 Execute 1 0.00 3.27 0 0 0 0 Fetch 1 0.00 1.45 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 6.12 0 7 0 1 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=7 pr=0 pw=0 time=362 us) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_REQUESTS (cr=4 pr=0 pw=0 time=310 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_REQUESTS_U1 (cr=3 pr=0 pw=0 time=275 us)(object id 34547) 1 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROGRAMS (cr=3 pr=0 pw=0 time=43 us) 1 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_U1 (cr=2 pr=0 pw=0 time=28 us)(object id 33183) ******************************************************************************** BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 6.52 0 0 0 0 Execute 2 0.00 11.98 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 18.50 0 0 0 2 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 ******************************************************************************** DECLARE -- ln_party_update_cnt NUMBER := 0; ln_order_update_cnt NUMBER := 0; p_cnt_booking_ref NUMBER; p_ordered_date DATE; ld_start_date DATE := TO_DATE ('2003/05/07 00:00:00', 'yyyy/mm/dd hh24:mi:ss'); ld_end_date DATE := TO_DATE ('2003/05/08 00:00:00', 'yyyy/mm/dd hh24:mi:ss'); ln_order_cnt NUMBER := 0; -- -- Cursor to get brand name CURSOR c_brands IS SELECT lookup_code brand_name FROM fnd_lookup_values flv WHERE lookup_type = 'XXINA_CPET_BRAND'; -- TYPE tbl_brands IS TABLE OF c_brands%ROWTYPE INDEX BY BINARY_INTEGER; lt_brands tbl_brands; -- -- CPET CUSTOMER declaration -- -- Cursor to get party CURSOR c_party_c IS SELECT /*+ DRIVING_SITE(cpet_customer) */ su.party_id , COUNT(su.booking_ref) booking_ref_cnt , MAX(su.ordered_date) ordered_date_max FROM cpet_customer@cpet_dblink cust , xxina_oe_order_summary_all su WHERE cust.party_id = su.party_id AND last_update_date BETWEEN ld_start_date AND ld_end_date GROUP BY su.party_id; -- TYPE tbl_party_c IS TABLE OF c_party_c%ROWTYPE INDEX BY BINARY_INTEGER; lt_party_c tbl_party_c; -- -- Cursor to get customer details CURSOR c_customer (p_party_id IN NUMBER, p_order_date IN DATE) IS SELECT osa.total_cost, osa.LOCATION , (SELECT description FROM fnd_lookup_values WHERE lookup_type = 'XX_CCD_POINT' AND lookup_code = osa.LOCATION) location_name , osa.accomodation_code , (SELECT description FROM fnd_lookup_values WHERE lookup_type = 'XX_CCD_POINT' AND lookup_code = osa.accomodation_code) accomodation_name , osa.company , (SELECT description FROM fnd_lookup_values WHERE lookup_type = 'XX_CCD_COMPANY' AND lookup_code = osa.company) company_name , osa.booking_ref, osa.catering_code, osa.holiday_duration , (SELECT description FROM fnd_lookup_values WHERE lookup_type = 'XX_CCD_BRANCH' AND lookup_code = osa.branch) branch_description , osa.order_source, osa.supplier, osa.sales_channel , osa.package_type holiday_type, osa.party_id FROM xxina_oe_order_summary_all osa WHERE party_id = p_party_id AND ordered_date = p_order_date; -- TYPE rec_cpet_cust01 IS TABLE OF cpet_customer.attrib01%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust02 IS TABLE OF cpet_customer.attrib02%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust03 IS TABLE OF cpet_customer.attrib03%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust04 IS TABLE OF cpet_customer.attrib04%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust05 IS TABLE OF cpet_customer.attrib05%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust06 IS TABLE OF cpet_customer.attrib06%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust07 IS TABLE OF cpet_customer.attrib07%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust08 IS TABLE OF cpet_customer.attrib08%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust09 IS TABLE OF cpet_customer.attrib09%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust12 IS TABLE OF cpet_customer.attrib12%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust13 IS TABLE OF cpet_customer.attrib13%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust14 IS TABLE OF cpet_customer.attrib14%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust15 IS TABLE OF cpet_customer.attrib15%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust16 IS TABLE OF cpet_customer.attrib16%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust17 IS TABLE OF cpet_customer.attrib17%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_cust18 IS TABLE OF cpet_customer.attrib18%TYPE INDEX BY BINARY_INTEGER; TYPE rec_cpet_party IS TABLE OF cpet_customer.party_id%TYPE INDEX BY BINARY_INTEGER; -- lr_cpet_cust01 rec_cpet_cust01; lr_cpet_cust02 rec_cpet_cust02; lr_cpet_cust03 rec_cpet_cust03; lr_cpet_cust04 rec_cpet_cust04; lr_cpet_cust05 rec_cpet_cust05; lr_cpet_cust06 rec_cpet_cust06; lr_cpet_cust07 rec_cpet_cust07; lr_cpet_cust08 rec_cpet_cust08; lr_cpet_cust09 rec_cpet_cust09; lr_cpet_cust12 rec_cpet_cust12; lr_cpet_cust13 rec_cpet_cust13; lr_cpet_cust14 rec_cpet_cust14; lr_cpet_cust15 rec_cpet_cust15; lr_cpet_cust16 rec_cpet_cust16; lr_cpet_cust17 rec_cpet_cust17; lr_cpet_cust18 rec_cpet_cust18; lr_cpet_party rec_cpet_party; -- -- CPET ORDER declaration -- -- Cursor to get order details CURSOR c_order_detail (p_party_id IN VARCHAR2) IS SELECT -- category details su.booking_ref , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_INSURANCE' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) insurance , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_PRE_BOOK_SEATS' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) pre_book_seats , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_ATTRACTION_TICKETS' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) excursion , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_CAR_HIRE' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) car_hire , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_SKI_PACKS' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) ski_packs , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_AIRPORT_HOTEL' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) airport_hotel , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_AIRPORT_LOUNGE' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) airport_lounge , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_AIRPORT_CAR_PARKING' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) airport_car_parking , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_TAXI' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) taxi , NVL ((SELECT 'Y' FROM fnd_lookup_values flv, xxina_mi_booking_anc anc WHERE flv.lookup_type = 'XXINA_CAT_AIRPORT_TRANSFER' AND flv.lookup_code = anc.item_code AND anc.booking_ref = su.booking_ref AND ROWNUM = 1) , 'N' ) airport_transfer , NVL ((SELECT 'Y' FROM DUAL WHERE EXISTS (SELECT 1 FROM xxina_mi_booking_anc WHERE booking_ref = su.booking_ref)) , 'N') ancly -- EDOC flag ,NVL ((SELECT 'Y' FROM jtf_ih_interactions WHERE attribute3 = su.booking_ref AND ROWNUM = 1) , 'N' ) edoc_flag -- order details , su.accomodation_code , (SELECT flv.description FROM fnd_lookup_values flv WHERE flv.lookup_type = 'XX_CCD_POINT' AND flv.lookup_code = su.accomodation_code) accomodation_name , su.catering_code , (SELECT flv.description FROM fnd_lookup_values flv WHERE flv.lookup_type = 'XX_CCD_BRANCH' AND flv.lookup_code = su.branch) branch_description , su.order_source , su.supplier , su.sales_channel FROM xxina_oe_order_summary_all su WHERE party_id = p_party_id; -- TYPE rec_cpet_order IS RECORD ( attrib22 DBMS_SQL.varchar2_table , attrib23 DBMS_SQL.varchar2_table , attrib24 DBMS_SQL.varchar2_table , attrib25 DBMS_SQL.varchar2_table , attrib26 DBMS_SQL.varchar2_table , attrib29 DBMS_SQL.varchar2_table , attrib30 DBMS_SQL.varchar2_table , attrib32 DBMS_SQL.varchar2_table , attrib33 DBMS_SQL.varchar2_table , attrib34 DBMS_SQL.varchar2_table , attrib35 DBMS_SQL.varchar2_table , attrib36 DBMS_SQL.varchar2_table , attrib37 DBMS_SQL.varchar2_table , attrib38 DBMS_SQL.varchar2_table , attrib39 DBMS_SQL.varchar2_table , attrib40 DBMS_SQL.varchar2_table , attrib41 DBMS_SQL.varchar2_table , attrib18 DBMS_SQL.varchar2_table ); lr_cpet_order rec_cpet_order; -- BEGIN fnd_file.put_line (fnd_file.LOG , 'Start Date: ' || TO_CHAR (ld_start_date, 'dd-Mon-yyyy') ); fnd_file.put_line (fnd_file.LOG , 'End Date: ' || TO_CHAR (ld_end_date, 'dd-Mon-yyyy') ); fnd_file.put_line (fnd_file.LOG , 'Start time : ' || TO_CHAR (SYSDATE, 'dd-Mon-yyyy HH24:MI:SS') ); -- OPEN c_brands; FETCH c_brands BULK COLLECT INTO lt_brands; CLOSE c_brands; -- -- CPET CUSTOMER updation OPEN c_party_c; -- LOOP -- S L1 FETCH c_party_c BULK COLLECT INTO lt_party_c LIMIT 200; -- EXIT WHEN lt_party_c.COUNT = 0; -- lr_cpet_cust01.DELETE; lr_cpet_cust02.DELETE; lr_cpet_cust03.DELETE; lr_cpet_cust04.DELETE; lr_cpet_cust05.DELETE; lr_cpet_cust06.DELETE; lr_cpet_cust07.DELETE; lr_cpet_cust08.DELETE; lr_cpet_cust09.DELETE; lr_cpet_cust12.DELETE; lr_cpet_cust13.DELETE; lr_cpet_cust14.DELETE; lr_cpet_cust15.DELETE; lr_cpet_cust16.DELETE; lr_cpet_cust17.DELETE; lr_cpet_cust18.DELETE; lr_cpet_party.DELETE; -- lr_cpet_order.attrib23.DELETE; lr_cpet_order.attrib24.DELETE; lr_cpet_order.attrib25.DELETE; lr_cpet_order.attrib26.DELETE; lr_cpet_order.attrib29.DELETE; lr_cpet_order.attrib30.DELETE; lr_cpet_order.attrib32.DELETE; lr_cpet_order.attrib33.DELETE; lr_cpet_order.attrib34.DELETE; lr_cpet_order.attrib35.DELETE; lr_cpet_order.attrib36.DELETE; lr_cpet_order.attrib37.DELETE; lr_cpet_order.attrib38.DELETE; lr_cpet_order.attrib39.DELETE; lr_cpet_order.attrib40.DELETE; lr_cpet_order.attrib41.DELETE; lr_cpet_order.attrib18.DELETE; -- ln_order_cnt := 1; -- FOR i IN lt_party_c.FIRST .. lt_party_c.LAST LOOP -- S L2 lr_cpet_cust01(i) := NULL; lr_cpet_cust02(i) := NULL; lr_cpet_cust03(i) := NULL; lr_cpet_cust04(i) := NULL; lr_cpet_cust05(i) := NULL; lr_cpet_cust06(i) := NULL; lr_cpet_cust07(i) := NULL; lr_cpet_cust08(i) := NULL; lr_cpet_cust09(i) := NULL; lr_cpet_cust12(i) := NULL; lr_cpet_cust13(i) := NULL; lr_cpet_cust14(i) := NULL; lr_cpet_cust15(i) := NULL; lr_cpet_cust16(i) := NULL; lr_cpet_cust17(i) := NULL; lr_cpet_cust18(i) := NULL; lr_cpet_party(i) := NULL; -- FOR rec_customer IN c_customer (lt_party_c(i).party_id, lt_party_c(i).ordered_date_max) LOOP -- S L3 -- Brand Categorization FOR k IN lt_brands.FIRST .. lt_brands.LAST LOOP -- S L4 IF LOWER (rec_customer.branch_description) LIKE '%' || LOWER (lt_brands (k).brand_name) || '%' THEN lr_cpet_cust14(i) := lt_brands (k).brand_name; EXIT; END IF; END LOOP; -- E L4 -- IF lr_cpet_cust14(i) IS NULL THEN IF rec_customer.order_source = 'Travelcat MI' THEN lr_cpet_cust14(i) := NVL (rec_customer.supplier, 'Other 3rd Party'); END IF; END IF; -- check ancillary exists or not with flag Y or N for booking ref BEGIN SELECT NVL ((SELECT 'Y' FROM xxina_mi_booking_anc WHERE booking_ref = rec_customer.booking_ref AND ROWNUM = 1) , 'N' ) INTO lr_cpet_cust17(i) FROM DUAL; EXCEPTION WHEN OTHERS THEN fnd_file.put_line (fnd_file.LOG, 'Following Error Occured '|| SUBSTR (SQLERRM, 1, 200)); RAISE; END; -- lr_cpet_cust01(i) := lt_party_c(i).ordered_date_max; lr_cpet_cust02(i) := rec_customer.total_cost; lr_cpet_cust03(i) := rec_customer.location; lr_cpet_cust04(i) := rec_customer.location_name; lr_cpet_cust05(i) := rec_customer.accomodation_code; lr_cpet_cust06(i) := rec_customer.accomodation_name; lr_cpet_cust07(i) := rec_customer.company; lr_cpet_cust08(i) := rec_customer.company_name; lr_cpet_cust09(i) := lt_party_c(i).booking_ref_cnt; lr_cpet_cust12(i) := rec_customer.booking_ref; lr_cpet_cust13(i) := rec_customer.catering_code; lr_cpet_cust15(i) := rec_customer.holiday_type; lr_cpet_cust16(i) := rec_customer.holiday_duration; lr_cpet_cust18(i) := rec_customer.sales_channel; lr_cpet_party(i) := rec_customer.party_id; -- FOR rec_order_detail IN c_order_detail (rec_customer.party_id) LOOP -- S L5 lr_cpet_order.attrib22 (ln_order_cnt) := NULL; lr_cpet_order.attrib23 (ln_order_cnt) := NULL; lr_cpet_order.attrib24 (ln_order_cnt) := NULL; lr_cpet_order.attrib25 (ln_order_cnt) := NULL; lr_cpet_order.attrib26 (ln_order_cnt) := NULL; lr_cpet_order.attrib29 (ln_order_cnt) := NULL; lr_cpet_order.attrib30 (ln_order_cnt) := NULL; lr_cpet_order.attrib32 (ln_order_cnt) := NULL; lr_cpet_order.attrib33 (ln_order_cnt) := NULL; lr_cpet_order.attrib34 (ln_order_cnt) := NULL; lr_cpet_order.attrib35 (ln_order_cnt) := NULL; lr_cpet_order.attrib36 (ln_order_cnt) := NULL; lr_cpet_order.attrib37 (ln_order_cnt) := NULL; lr_cpet_order.attrib38 (ln_order_cnt) := NULL; lr_cpet_order.attrib39 (ln_order_cnt) := NULL; lr_cpet_order.attrib40 (ln_order_cnt) := NULL; lr_cpet_order.attrib41 (ln_order_cnt) := NULL; lr_cpet_order.attrib18 (ln_order_cnt) := NULL; -- FOR brand_cnt IN lt_brands.FIRST .. lt_brands.LAST LOOP -- S L6 IF LOWER (rec_order_detail.branch_description) LIKE'%' || LOWER (lt_brands (brand_cnt).brand_name)|| '%' THEN lr_cpet_order.attrib26 (ln_order_cnt) := lt_brands (brand_cnt).brand_name; EXIT; END IF; END LOOP; -- E L6 -- IF lr_cpet_order.attrib26 (ln_order_cnt) IS NULL THEN IF rec_order_detail.order_source = 'Travelcat MI' THEN lr_cpet_order.attrib26 (ln_order_cnt) := NVL (rec_order_detail.supplier, 'Other 3rd Party'); END IF; END IF; -- lr_cpet_order.attrib22 (ln_order_cnt) := rec_order_detail.accomodation_name; lr_cpet_order.attrib23 (ln_order_cnt) := rec_order_detail.accomodation_code; lr_cpet_order.attrib24 (ln_order_cnt) := rec_order_detail.ancly; lr_cpet_order.attrib25 (ln_order_cnt) := rec_order_detail.catering_code; lr_cpet_order.attrib29 (ln_order_cnt) := rec_order_detail.edoc_flag; lr_cpet_order.attrib30 (ln_order_cnt) := rec_order_detail.sales_channel; lr_cpet_order.attrib32 (ln_order_cnt) := rec_order_detail.insurance; lr_cpet_order.attrib33 (ln_order_cnt) := rec_order_detail.pre_book_seats; lr_cpet_order.attrib34 (ln_order_cnt) := rec_order_detail.excursion; lr_cpet_order.attrib35 (ln_order_cnt) := rec_order_detail.car_hire; lr_cpet_order.attrib36 (ln_order_cnt) := rec_order_detail.ski_packs; lr_cpet_order.attrib37 (ln_order_cnt) := rec_order_detail.airport_hotel; lr_cpet_order.attrib38 (ln_order_cnt) := rec_order_detail.airport_lounge; lr_cpet_order.attrib39 (ln_order_cnt) := rec_order_detail.airport_car_parking; lr_cpet_order.attrib40 (ln_order_cnt) := rec_order_detail.taxi; lr_cpet_order.attrib41 (ln_order_cnt) := rec_order_detail.airport_transfer; lr_cpet_order.attrib18 (ln_order_cnt) := rec_order_detail.booking_ref; -- ln_order_cnt := ln_order_cnt + 1; END LOOP; -- E L5 -- END LOOP; -- E L3 END LOOP; -- E L2 -- FOR j IN lr_cpet_cust01.FIRST..lr_cpet_cust01.LAST LOOP UPDATE cpet_customer@cpet_dblink SET attrib01 = lr_cpet_cust01(j) , attrib02 = lr_cpet_cust02(j) , attrib03 = lr_cpet_cust03(j) , attrib04 = lr_cpet_cust04(j) , attrib05 = lr_cpet_cust05(j) , attrib06 = lr_cpet_cust06(j) , attrib07 = lr_cpet_cust07(j) , attrib08 = lr_cpet_cust08(j) , attrib09 = lr_cpet_cust09(j) , attrib12 = lr_cpet_cust12(j) , attrib13 = lr_cpet_cust13(j) , attrib14 = lr_cpet_cust14(j) , attrib15 = lr_cpet_cust15(j) , attrib16 = lr_cpet_cust16(j) , attrib17 = lr_cpet_cust17(j) , attrib18 = lr_cpet_cust18(j) WHERE party_id = lr_cpet_party(j); -- ln_party_update_cnt := ln_party_update_cnt + SQL%ROWCOUNT; END LOOP; -- FOR j IN 1 .. lr_cpet_order.attrib18.COUNT LOOP UPDATE cpet_order@cpet_dblink SET attrib22 = lr_cpet_order.attrib22(j) , attrib23 = lr_cpet_order.attrib23(j) , attrib24 = lr_cpet_order.attrib24(j) , attrib25 = lr_cpet_order.attrib25(j) , attrib26 = lr_cpet_order.attrib26(j) , attrib29 = lr_cpet_order.attrib29(j) , attrib30 = lr_cpet_order.attrib30(j) , attrib32 = lr_cpet_order.attrib32(j) , attrib33 = lr_cpet_order.attrib33(j) , attrib34 = lr_cpet_order.attrib34(j) , attrib35 = lr_cpet_order.attrib35(j) , attrib36 = lr_cpet_order.attrib36(j) , attrib37 = lr_cpet_order.attrib37(j) , attrib38 = lr_cpet_order.attrib38(j) , attrib39 = lr_cpet_order.attrib39(j) , attrib40 = lr_cpet_order.attrib40(j) , attrib41 = lr_cpet_order.attrib41(j) WHERE attrib18 = lr_cpet_order.attrib18(j); -- ln_order_update_cnt := ln_order_update_cnt + SQL%ROWCOUNT; END LOOP; -- COMMIT; -- END LOOP; -- E L1 -- CLOSE c_party_c; -- fnd_file.put_line (fnd_file.LOG, 'Customer records affected : ' || ln_party_update_cnt); fnd_file.put_line (fnd_file.LOG, 'Order records affected : ' || ln_order_update_cnt); fnd_file.put_line (fnd_file.LOG, 'End time : '|| TO_CHAR (SYSDATE, 'dd-Mon-yyyy HH24:MI:SS')); -- EXCEPTION WHEN OTHERS THEN fnd_file.put_line (fnd_file.LOG, SQLERRM); RAISE; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 4000.00 12510.49 0 0 1 0 Execute 1 418500.00 175941.49 0 0 232 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 422500.00 188451.98 0 0 233 1 Misses in library cache during parse: 1 Optimizer goal: ALL_ROWS Parsing user id: 173 ******************************************************************************** SELECT LOOKUP_CODE BRAND_NAME FROM FND_LOOKUP_VALUES FLV WHERE LOOKUP_TYPE = 'XXINA_CPET_BRAND' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 3.04 0 0 0 0 Execute 1 0.00 2.56 0 0 0 0 Fetch 1 0.00 3.00 0 3 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 8.60 0 3 0 7 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 7 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=3 pr=0 pw=0 time=203 us)(object id 32878) ******************************************************************************** SELECT /*+ DRIVING_SITE(cpet_customer) */ SU.PARTY_ID , COUNT(SU.BOOKING_REF) BOOKING_REF_CNT , MAX(SU.ORDERED_DATE) ORDERED_DATE_MAX FROM CPET_CUSTOMER@CPET_DBLINK CUST , XXINA_OE_ORDER_SUMMARY_ALL SU WHERE CUST.PARTY_ID = SU.PARTY_ID AND LAST_UPDATE_DATE BETWEEN :B2 AND :B1 GROUP BY SU.PARTY_ID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 11.66 0 0 0 0 Execute 1 300.00 398.60 0 0 0 0 Fetch 58 20600.00 23500.91 0 115202 0 11501 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 60 20900.00 23911.17 0 115202 0 11501 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 11501 SORT GROUP BY (cr=115202 pr=0 pw=0 time=2309969 us) 23417 FILTER (cr=115202 pr=0 pw=0 time=1647023 us) 23417 TABLE ACCESS BY GLOBAL INDEX ROWID XXINA_OE_ORDER_SUMMARY_ALL PARTITION: ROW LOCATION ROW LOCATION (cr=115202 pr=0 pw=0 time=1600167 us) 69248 NESTED LOOPS (cr=91800 pr=0 pw=0 time=1734074 us) 45830 REMOTE (cr=0 pr=0 pw=0 time=1932233 us) 23417 INDEX RANGE SCAN XXINA_OE_SUM_PARTY_ID (cr=91800 pr=0 pw=0 time=936362 us)(object id 705081) ******************************************************************************** SELECT OSA.TOTAL_COST, OSA.LOCATION , (SELECT DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'XX_CCD_POINT' AND LOOKUP_CODE = OSA.LOCATION) LOCATION_NAME , OSA.ACCOMODATION_CODE , (SELECT DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'XX_CCD_POINT' AND LOOKUP_CODE = OSA.ACCOMODATION_CODE) ACCOMODATION_NAME , OSA.COMPANY , (SELECT DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'XX_CCD_COMPANY' AND LOOKUP_CODE = OSA.COMPANY) COMPANY_NAME , OSA.BOOKING_REF, OSA.CATERING_CODE, OSA.HOLIDAY_DURATION , (SELECT DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'XX_CCD_BRANCH' AND LOOKUP_CODE = OSA.BRANCH) BRANCH_DESCRIPTION , OSA.ORDER_SOURCE, OSA.SUPPLIER, OSA.SALES_CHANNEL , OSA.PACKAGE_TYPE HOLIDAY_TYPE, OSA.PARTY_ID FROM XXINA_OE_ORDER_SUMMARY_ALL OSA WHERE PARTY_ID = :B2 AND ORDERED_DATE = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 3.24 0 0 0 0 Execute 11501 51900.00 52408.39 0 0 0 0 Fetch 1150117995900.0017698356.96 73 18891989 0 11637 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2300318047800.0017750768.59 73 18891989 0 11637 using composite index on (ordered_date and party_id) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 17.17 0 0 0 0 Execute 11491 42000.00 42341.51 0 0 0 0 Fetch 1149118601400.0021687147.60 12264 18860954 0 11626 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2298318643400.0021729506.28 12264 18860954 0 11626 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 10704 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=8832223 pr=0 pw=0 time=830245889 us) 10704 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=8820095 pr=0 pw=0 time=829626951 us)(object id 32878) 10718 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=8843077 pr=72 pw=0 time=823251488 us) 10718 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=8831631 pr=0 pw=0 time=820171609 us)(object id 32878) 10716 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=182494 pr=1 pw=0 time=15747887 us) 10716 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=171456 pr=0 pw=0 time=15176988 us)(object id 32878) 10721 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=976222 pr=0 pw=0 time=93219482 us) 10721 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=964890 pr=0 pw=0 time=92820141 us)(object id 32878) 11637 TABLE ACCESS BY GLOBAL INDEX ROWID XXINA_OE_ORDER_SUMMARY_ALL PARTITION: ROW LOCATION ROW LOCATION (cr=57973 pr=0 pw=0 time=2434212 us) 23417 INDEX RANGE SCAN XXINA_OE_SUM_PARTY_ID (cr=34568 pr=0 pw=0 time=957267 us)(object id 705081) ******************************************************************************** SELECT NVL ((SELECT 'Y' FROM XXINA_MI_BOOKING_ANC WHERE BOOKING_REF = :B1 AND ROWNUM = 1) , 'N' ) FROM DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 3.44 0 0 0 0 Execute 11637 28500.00 26554.72 0 0 0 0 Fetch 11637 21800.00 347996.55 952 34964 0 11637 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 23275 50300.00 374554.71 952 34964 0 11637 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 11637 FAST DUAL (cr=0 pr=0 pw=0 time=73077 us) ******************************************************************************** SELECT SU.BOOKING_REF , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_INSURANCE' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) INSURANCE , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_PRE_BOOK_SEATS' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) PRE_BOOK_SEATS , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_ATTRACTION_TICKETS' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) EXCURSION , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_CAR_HIRE' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) CAR_HIRE , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_SKI_PACKS' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) SKI_PACKS , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_AIRPORT_HOTEL' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) AIRPORT_HOTEL , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_AIRPORT_LOUNGE' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) AIRPORT_LOUNGE , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_AIRPORT_CAR_PARKING' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) AIRPORT_CAR_PARKING , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_TAXI' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) TAXI , NVL ((SELECT 'Y' FROM FND_LOOKUP_VALUES FLV, XXINA_MI_BOOKING_ANC ANC WHERE FLV.LOOKUP_TYPE = 'XXINA_CAT_AIRPORT_TRANSFER' AND FLV.LOOKUP_CODE = ANC.ITEM_CODE AND ANC.BOOKING_REF = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) AIRPORT_TRANSFER , NVL ((SELECT 'Y' FROM DUAL WHERE EXISTS (SELECT 1 FROM XXINA_MI_BOOKING_ANC WHERE BOOKING_REF = SU.BOOKING_REF)) , 'N') ANCLY ,NVL ((SELECT 'Y' FROM JTF_IH_INTERACTIONS WHERE ATTRIBUTE3 = SU.BOOKING_REF AND ROWNUM = 1) , 'N' ) EDOC_FLAG , SU.ACCOMODATION_CODE , (SELECT FLV.DESCRIPTION FROM FND_LOOKUP_VALUES FLV WHERE FLV.LOOKUP_TYPE = 'XX_CCD_POINT' AND FLV.LOOKUP_CODE = SU.ACCOMODATION_CODE) ACCOMODATION_NAME , SU.CATERING_CODE , (SELECT FLV.DESCRIPTION FROM FND_LOOKUP_VALUES FLV WHERE FLV.LOOKUP_TYPE = 'XX_CCD_BRANCH' AND FLV.LOOKUP_CODE = SU.BRANCH) BRANCH_DESCRIPTION , SU.ORDER_SOURCE , SU.SUPPLIER , SU.SALES_CHANNEL FROM XXINA_OE_ORDER_SUMMARY_ALL SU WHERE PARTY_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 5.48 0 0 0 0 Execute 11637 115600.00 113444.93 0 0 0 0 Fetch 1163722868900.0023993274.88 5720 22282549 0 23956 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2327522984500.0024106725.29 5720 22282549 0 23956 using outer join call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 72.31 0 0 0 0 Execute 11626 79200.00 79288.46 0 0 0 0 Fetch 1162620410400.0024934694.32 20091 20815617 0 26057 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2325320489600.0025014055.09 20091 20815617 0 26057 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 22306 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=18398343 pr=48 pw=0 time=1735815043 us) 22306 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=18374171 pr=0 pw=0 time=1732982691 us)(object id 32878) 21790 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=1978739 pr=0 pw=0 time=188776561 us) 21790 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=1955607 pr=0 pw=0 time=187623472 us)(object id 32878) 23956 TABLE ACCESS BY GLOBAL INDEX ROWID XXINA_OE_ORDER_SUMMARY_ALL PARTITION: ROW LOC5 ¬41 ¬ÿ¿¥ utü›Ä –X 23956 INDEX RANGE SCAN XXINA_OE_SUM_PARTY_ID (cr=34979 pr=0 pw=0 time=983445 us)(object id 705081) ******************************************************************************** UPDATE CPET_CUSTOMER@CPET_DBLINK SET ATTRIB01 = :B16 , ATTRIB02 = :B15 , ATTRIB03 = :B14 , ATTRIB04 = :B13 , ATTRIB05 = :B12 , ATTRIB06 = :B11 , ATTRIB07 = :B10 , ATTRIB08 = :B9 , ATTRIB09 = :B8 , ATTRIB12 = :B7 , ATTRIB13 = :B6 , ATTRIB14 = :B5 , ATTRIB15 = :B4 , ATTRIB16 = :B3 , ATTRIB17 = :B2 , ATTRIB18 = :B1 WHERE PARTY_ID = :B17 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 115.05 0 0 0 0 Execute 11501 32700.00 90944.26 0 0 58 11501 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11502 32700.00 91059.31 0 0 58 11501 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** UPDATE CPET_ORDER@CPET_DBLINK SET ATTRIB22 = :B17 , ATTRIB23 = :B16 , ATTRIB24 = :B15 , ATTRIB25 = :B14 , ATTRIB26 = :B13 , ATTRIB29 = :B12 , ATTRIB30 = :B11 , ATTRIB32 = :B10 , ATTRIB33 = :B9 , ATTRIB34 = :B8 , ATTRIB35 = :B7 , ATTRIB36 = :B6 , ATTRIB37 = :B5 , ATTRIB38 = :B4 , ATTRIB39 = :B3 , ATTRIB40 = :B2 , ATTRIB41 = :B1 WHERE ATTRIB18 = :B18 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 1015.27 0 0 0 0 Execute 23956 74800.00 190049.71 0 0 0 17614 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 23957 74800.00 191064.98 0 0 0 17614 Misses in library cache during parse: 0 Optimizer goal: ALL_ROWS Parsing user id: 173 (recursive depth: 1) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 4000.00 12520.31 0 0 1 0 Execute 5 418900.00 176493.40 0 0 233 4 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 422900.00 189013.71 0 0 234 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 52 0.00 1301.62 0 0 0 0 Execute 70314 304300.00 474023.50 0 18 62 29117 Fetch 3516640907600.0042064558.56 6748 41325038 0 59041 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10553241211900.0042539883.68 6748 41325056 62 88158 Misses in library cache during parse: 2 Misses in library cache during execute: 1 49 user SQL statements in session. 8 internal SQL statements in session. 57 SQL statements in session. ******************************************************************************** Trace file: tcrm_ora_11191.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 49 user SQL statements in trace file. 8 internal SQL statements in trace file. 57 SQL statements in trace file. 53 unique SQL statements in trace file. 106497 lines in trace file.