TKPROF: Release 9.2.0.8.0 - Production on Thu May 22 09:55:16 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: rmsprf01_ora_28031.trc Sort options: prsdsk exedsk fchdsk ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** SELECT CSD.UNIT_RETAIL, CSH.REASON, CSD.ACTIVE_DATE FROM CLEAR_SUSP_HEAD CSH, CLEAR_SUSP_DETAIL CSD, CLEAR_RESET_CALC CRC, (SELECT MAX(CSDA.ACTIVE_DATE) MAXACTIVE FROM CLEAR_SUSP_DETAIL CSDA, CLEAR_RESET_CALC CRC WHERE CSDA.ITEM = :B3 AND CSDA.ZONE_ID = :B2 AND CRC.CLEARANCE = CSDA.CLEARANCE AND (CRC.RESET_DATE IS NULL OR CRC.RESET_DATE > :B1 ) AND CSDA.ACTIVE_DATE <= :B1 ) MAXCSD WHERE CSH.CLEARANCE = CSD.CLEARANCE AND CSH.STATUS IN ('A', 'H', 'E', 'O') AND CSD.ITEM = :B3 AND CSD.ZONE_ID = :B2 AND CRC.CLEARANCE = CSD.CLEARANCE AND CRC.ITEM = CSD.ITEM AND CRC.ZONE_ID = CSD.ZONE_ID AND (CRC.RESET_DATE IS NULL OR CRC.RESET_DATE > :B1 ) AND ACTIVE_DATE = MAXCSD.MAXACTIVE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 835142 49.55 46.47 0 0 0 0 Fetch 835142 998.84 1393.80 1938875 16319118 0 64578 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1670285 1048.40 1440.28 1938875 16319118 0 64578 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 VIEW 0 SORT (AGGREGATE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLEAR_RESET_CALC' 0 NESTED LOOPS 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLEAR_SUSP_DETAIL' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CLEAR_SUSP_DETAIL_I2' (NON-UNIQUE) 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_CLEAR_RESET_CALC' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLEAR_SUSP_DETAIL' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CLEAR_SUSP_DETAIL_I2' (NON-UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLEAR_RESET_CALC' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_CLEAR_RESET_CALC' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLEAR_SUSP_HEAD' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_CLEAR_SUSP_HEAD' (UNIQUE) ******************************************************************************** SELECT IT.ZONE_GROUP_ID FROM OD_ZONE_LVL IT WHERE IT.ITEM = :B1 AND IT.ZONE_GROUP_ID NOT IN(99, 9999) AND IT.ACTIVE_DATE = (SELECT MAX(ITA.ACTIVE_DATE) FROM OD_ZONE_LVL ITA WHERE ITA.ITEM = IT.ITEM AND ITA.ACTIVE_DATE <= :B2 AND ITA.ZONE_GROUP_ID NOT IN(99, 9999) AND IT.OD_LAST_UPDATE_DATETIME = (SELECT MAX(ITB.OD_LAST_UPDATE_DATETIME) FROM OD_ZONE_LVL ITB WHERE ITB.ITEM = ITA.ITEM AND ITB.ACTIVE_DATE = ITA.ACTIVE_DATE AND ITB.ZONE_ID = ITA.ZONE_ID AND ITB.ZONE_GROUP_ID = ITA.ZONE_GROUP_ID)) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 886266 20.93 18.40 0 0 0 0 Fetch 886266 576.48 1078.75 1376494 46233366 0 886266 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1772533 597.41 1097.16 1376494 46233366 0 886266 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_ZONE_LVL_I1' (NON-UNIQUE) 0 SORT (AGGREGATE) 0 NESTED LOOPS 0 VIEW OF 'VW_SQ_1' 0 FILTER 0 SORT (GROUP BY) 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_ZONE_LVL_I1' (NON-UNIQUE) 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_OD_ZONE_LVL' (UNIQUE) ******************************************************************************** SELECT UNIT_RETAIL, MULTI_UNIT1, MULTI_UNIT_RETAIL1, MULTI_UNIT2, MULTI_UNIT_RETAIL2, MULTI_UNIT3, MULTI_UNIT_RETAIL3, MULTI_UNIT4, MULTI_UNIT_RETAIL4, MULTI_UNIT5, MULTI_UNIT_RETAIL5, MULTI_UNIT6, MULTI_UNIT_RETAIL6, MULTI_UNIT7, MULTI_UNIT_RETAIL7, MULTI_UNIT8, MULTI_UNIT_RETAIL8, MULTI_UNIT9, MULTI_UNIT_RETAIL9, MULTI_UNIT10, MULTI_UNIT_RETAIL10, MULTI_UNIT11, MULTI_UNIT_RETAIL11, MULTI_UNIT12, MULTI_UNIT_RETAIL12, MULTI_UNIT13, MULTI_UNIT_RETAIL13, MULTI_UNIT14, MULTI_UNIT_RETAIL14, MULTI_UNIT15, MULTI_UNIT_RETAIL15, MULTI_UNIT16, MULTI_UNIT_RETAIL16, ACTIVE_DATE FROM OD_ZONE_LVL IT WHERE IT.ITEM = :B2 AND IT.ZONE_ID = :B1 AND IT.ZONE_GROUP_ID NOT IN(99, 9999) AND IT.ACTIVE_DATE = (SELECT MAX(ITA.ACTIVE_DATE) FROM OD_ZONE_LVL ITA WHERE ITA.ITEM = IT.ITEM AND ITA.ZONE_ID = IT.ZONE_ID AND ITA.ACTIVE_DATE <= :B3 AND ITA.ZONE_GROUP_ID NOT IN(99, 9999) AND ITA.OD_LAST_UPDATE_DATETIME = (SELECT MAX(ITB.OD_LAST_UPDATE_DATETIME) FROM OD_ZONE_LVL ITB WHERE ITB.ITEM = ITA.ITEM AND ITB.ZONE_ID = ITA.ZONE_ID AND ITB.ACTIVE_DATE = ITA.ACTIVE_DATE AND ITB.ZONE_GROUP_ID = ITB.ZONE_GROUP_ID) ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 886266 51.83 50.21 0 0 0 0 Fetch 886266 784.77 1178.16 299083 39589445 0 886265 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1772533 836.60 1228.38 299083 39589445 0 886265 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 FILTER 0 SORT (GROUP BY) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OD_ZONE_LVL' 0 NESTED LOOPS 0 VIEW 0 FILTER 0 SORT (GROUP BY) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OD_ZONE_LVL' 0 NESTED LOOPS 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_ZONE_LVL_I1' (NON-UNIQUE) 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_OD_ZONE_LVL' (UNIQUE) 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_OD_ZONE_LVL' (UNIQUE) ******************************************************************************** SELECT L.ITEM FROM ITEM_LOC L, ITEM_MASTER M WHERE LOC = :B1 AND LENGTH(L.ITEM) <= 6 AND L.STATUS IN ('A', 'C') AND M.ITEM = L.ITEM AND M.STATUS = 'A' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 8 0.01 0.00 0 0 0 0 Fetch 835150 44.43 53.62 48085 4217832 0 835142 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 835159 44.45 53.63 48085 4217832 0 835142 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ITEM_MASTER' 0 PARTITION HASH (SINGLE) PARTITION:KEYKEY 0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'ITEM_LOC' PARTITION:KEYKEY 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_ITEM_LOC' (UNIQUE) PARTITION:KEYKEY ******************************************************************************** SELECT SINGLE_UNIT_RETAIL, MULTI_UNIT1, MULTI_UNIT_RETAIL1, MULTI_UNIT2, MULTI_UNIT_RETAIL2, MULTI_UNIT3, MULTI_UNIT_RETAIL3, MULTI_UNIT4, MULTI_UNIT_RETAIL4, MULTI_UNIT5, MULTI_UNIT_RETAIL5, MULTI_UNIT6, MULTI_UNIT_RETAIL6, MULTI_UNIT7, MULTI_UNIT_RETAIL7, MULTI_UNIT8, MULTI_UNIT_RETAIL8, MULTI_UNIT9, MULTI_UNIT_RETAIL9, MULTI_UNIT10, MULTI_UNIT_RETAIL10, MULTI_UNIT11, MULTI_UNIT_RETAIL11, MULTI_UNIT12, MULTI_UNIT_RETAIL12, MULTI_UNIT13, MULTI_UNIT_RETAIL13, MULTI_UNIT14, MULTI_UNIT_RETAIL14, MULTI_UNIT15, MULTI_UNIT_RETAIL15, MULTI_UNIT16, MULTI_UNIT_RETAIL16, SUBSTR(COMMENT_DESC,1,20), ACTIVE_DATE, END_DATE FROM OD_STORE_LVL WHERE ITEM = :B3 AND LOC = :B2 AND STATUS IN ('A', 'H', 'E', 'O') AND :B1 BETWEEN ACTIVE_DATE AND END_DATE AND STORE_LVL_ID = (SELECT MAX(STORE_LVL_ID) FROM OD_STORE_LVL WHERE ITEM = :B3 AND LOC = :B2 AND STATUS IN ('A', 'H', 'E','O') AND :B1 BETWEEN ACTIVE_DATE AND END_DATE) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 886266 35.52 32.91 0 0 0 0 Fetch 886266 60.84 77.45 41227 10761274 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1772533 96.36 110.37 41227 10761274 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 INLIST ITERATOR 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OD_STORE_LVL' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_STORE_LVL_P3' (NON-UNIQUE) 0 SORT (AGGREGATE) 0 INLIST ITERATOR 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OD_STORE_LVL' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_STORE_LVL_P3' (NON-UNIQUE) ******************************************************************************** SELECT PSD.UNIT_RETAIL, PSD.MULTI_UNIT1, PSD.MULTI_UNIT_RETAIL1, PSD.MULTI_UNIT2, PSD.MULTI_UNIT_RETAIL2, PSD.MULTI_UNIT3, PSD.MULTI_UNIT_RETAIL3, PSD.MULTI_UNIT4, PSD.MULTI_UNIT_RETAIL4, PSD.MULTI_UNIT5, PSD.MULTI_UNIT_RETAIL5, PSD.MULTI_UNIT6, PSD.MULTI_UNIT_RETAIL6, PSD.MULTI_UNIT7, PSD.MULTI_UNIT_RETAIL7, PSD.MULTI_UNIT8, PSD.MULTI_UNIT_RETAIL8, PSD.MULTI_UNIT9, PSD.MULTI_UNIT_RETAIL9, PSD.MULTI_UNIT10, PSD.MULTI_UNIT_RETAIL10, PSD.MULTI_UNIT11, PSD.MULTI_UNIT_RETAIL11, PSD.MULTI_UNIT12, PSD.MULTI_UNIT_RETAIL12, PSD.MULTI_UNIT13, PSD.MULTI_UNIT_RETAIL13, PSD.MULTI_UNIT14, PSD.MULTI_UNIT_RETAIL14, PSD.MULTI_UNIT15, PSD.MULTI_UNIT_RETAIL15, PSD.MULTI_UNIT16, PSD.MULTI_UNIT_RETAIL16, SUBSTR(PSH.COMMENT_DESC,1,20), PSH.REASON, PSH.ACTIVE_DATE FROM PRICE_SUSP_DETAIL PSD, PRICE_SUSP_HEAD PSH WHERE PSH.PRICE_CHANGE = PSD.PRICE_CHANGE AND PSD.ITEM = :B3 AND PSD.ZONE_ID = :B2 AND PSD.STATUS IN ('A', 'H', 'E', 'M') AND PSD.ZONE_GROUP_ID NOT IN(99, 9999) AND PSH.ACTIVE_DATE > :B1 AND PSH.ACTIVE_DATE = (SELECT /*+ USE_CONCAT INDEX_SS (PSDA PRICE_SUSP_DETAIL_I1 ) */MAX(PSHA.ACTIVE_DATE) FROM PRICE_SUSP_DETAIL PSDA, PRICE_SUSP_HEAD PSHA WHERE PSDA.PRICE_CHANGE = PSHA.PRICE_CHANGE AND PSDA.ITEM = :B3 AND PSDA.ZONE_ID = :B2 AND PSHA.ACTIVE_DATE <= :B4 AND PSDA.ZONE_GROUP_ID NOT IN (99, 9999) AND PSDA.STATUS IN ('A', 'H', 'E', 'M') AND PSDA.OD_CREATE_DATETIME = (SELECT MAX(PSDB.OD_CREATE_DATETIME) FROM PRICE_SUSP_DETAIL PSDB, PRICE_SUSP_HEAD PSHB WHERE PSDB.PRICE_CHANGE = PSHB.PRICE_CHANGE AND PSDB.ITEM = :B3 AND PSDB.ZONE_ID = :B2 AND PSHB.ACTIVE_DATE = PSHA.ACTIVE_DATE AND PSDB.ZONE_GROUP_ID NOT IN (99, 9999) AND PSDB.STATUS IN ('A', 'H', 'E', 'M') )) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 886266 54.93 52.29 0 0 0 0 Fetch 886266 141.17 208.36 32036 6762596 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1772533 196.10 260.65 32036 6762596 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 FILTER 0 NESTED LOOPS 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRICE_SUSP_DETAIL' 0 INDEX GOAL: ANALYZED (SKIP SCAN) OF 'PRICE_SUSP_DETAIL_I1' (NON-UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRICE_SUSP_HEAD' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_PRICE_SUSP_HEAD' (UNIQUE) 0 SORT (AGGREGATE) 0 VIEW 0 FILTER 0 SORT (GROUP BY) 0 NESTED LOOPS 0 NESTED LOOPS 0 MERGE JOIN (CARTESIAN) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRICE_SUSP_DETAIL' 0 INDEX GOAL: ANALYZED (SKIP SCAN) OF 'PRICE_SUSP_DETAIL_I1' (NON-UNIQUE) 0 BUFFER (SORT) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRICE_SUSP_DETAIL' 0 INDEX GOAL: ANALYZED (SKIP SCAN) OF 'PRICE_SUSP_DETAIL_I1' (NON-UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRICE_SUSP_HEAD' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_PRICE_SUSP_HEAD' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRICE_SUSP_HEAD' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_PRICE_SUSP_HEAD' (UNIQUE) ******************************************************************************** INSERT /*+ PARALLEL(OJP,DEFAULT) */ INTO OD_JDA_PRICING OJP ( STORE, ITEM, WINNING_PRICE, WINNING_PRICE_TYPE, REGULAR_PRICE, CREATE_SIGN_FLAG, COMMENTS, THEME, EVENT_NAME, CREATION_DATE_TIME, TYPE, ACTION_TYPE, START_DATE, END_DATE, POS_CONSUME_DATE, SIGN_CONSUME_DATE, FUTURE_SUBSCRIBER, SRC_TBL_LAST_UPDATE_BY, SRC_TBL_LAST_UPDATE_DATE ) VALUES ( :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , NULL, NULL, NULL, :B3 , 1, 'ADD', :B2 , :B1 , NULL, NULL, NULL, NULL, NULL ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 823979 80.19 158.51 25156 23912 3417277 823979 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 823980 80.19 158.51 25156 23912 3417277 823979 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE ******************************************************************************** SELECT UNIT_PRICE ,EFFECTIVE_DATE ,END_DATE ,TIER_QTY_01 ,TIER_PRICE_01 , TIER_QTY_02 ,TIER_PRICE_02 ,TIER_QTY_03 ,TIER_PRICE_03 ,TIER_QTY_04 , TIER_PRICE_04 ,TIER_QTY_05 ,TIER_PRICE_05 ,TIER_QTY_06 ,TIER_PRICE_06 , TIER_QTY_07 ,TIER_PRICE_07 ,TIER_QTY_08 ,TIER_PRICE_08 ,TIER_QTY_09 , TIER_PRICE_09 ,TIER_QTY_10 ,TIER_PRICE_10 ,TIER_QTY_11 ,TIER_PRICE_11 , TIER_QTY_12 ,TIER_PRICE_12 ,TIER_QTY_13 ,TIER_PRICE_13 ,TIER_QTY_14 , TIER_PRICE_14 ,TIER_QTY_15 ,TIER_PRICE_15 ,TIER_QTY_16 ,TIER_PRICE_16 , SUBSTR(COMMENT_DESC,1,20) FROM OD_JDA_WNPROMO OJW1 WHERE ITEM = :B3 AND STORE = :B2 AND EFFECTIVE_DATE <= :B1 AND END_DATE >= :B1 AND ACTION_TYPE = 'ADD' AND GETPRC_FLAG = 'Y' AND NOT EXISTS (SELECT 1 FROM OD_JDA_WNPROMO OJW2 WHERE OJW2.ITEM = OJW1.ITEM AND OJW2.STORE = OJW1.STORE AND OJW2.EFFECTIVE_DATE >= OJW1.EFFECTIVE_DATE AND OJW2.EVENT_NAME = OJW1.EVENT_NAME AND OJW2.ACTION_TYPE = 'DEL' AND OJW2.CREATION_DATE_TIME > OJW1.CREATION_DATE_TIME) AND NOT EXISTS (SELECT 1 FROM OD_JDA_WNPROMO OJW3 WHERE OJW3.ITEM = OJW1.ITEM AND OJW3.STORE = OJW1.STORE AND OJW3.EFFECTIVE_DATE BETWEEN OJW1.EFFECTIVE_DATE AND OJW1.END_DATE AND OJW3.EFFECTIVE_DATE <= :B1 AND OJW3.ACTION_TYPE = 'ADD' AND OJW3.EVENT_NAME = 'NOPROMO' AND OJW3.CREATION_DATE_TIME > OJW1.CREATION_DATE_TIME) ORDER BY CREATION_DATE_TIME DESC call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 835142 36.79 35.49 0 0 4 0 Fetch 835142 30.63 31.11 12172 2634868 0 5637 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1670285 67.42 66.60 12172 2634868 4 5637 Misses in library cache during parse: 0 Misses in library cache during execute: 2 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (ORDER BY) 0 FILTER 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OD_JDA_WNPROMO' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_JDA_WNPROMO_PK' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OD_JDA_WNPROMO' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_JDA_WNPROMO_PK' (UNIQUE) 0 FILTER 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OD_JDA_WNPROMO' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OD_JDA_WNPROMO_PK' (UNIQUE) ******************************************************************************** UPDATE OD_JDA_WNPROMO SET CONSUME_FLAG='Y' WHERE STORE=:B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 8 4.51 16.63 10155 70104 51441 50753 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 4.51 16.63 10155 70104 51441 50753 Misses in library cache during parse: 1 Misses in library cache during execute: 2 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 0 UPDATE OF 'OD_JDA_WNPROMO' 0 INDEX GOAL: ANALYZED (SKIP SCAN) OF 'OD_JDA_WNPROMO_PK' (UNIQUE) ******************************************************************************** INSERT /*+ APPEND */ INTO OD_JDA_TIER_PRICING ( STORE, ITEM, TIER_LVL, TIER_QTY, TIER_PRICE, START_DATE_TIME, END_DATE_TIME, CREATION_DATE_TIME, TYPE, ACTION_TYPE, POS_CONSUME_DATE, SIGN_CONSUME_DATE, FUTURE_SUBSCRIBER ) VALUES ( :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 , 1, 'ADD', NULL, NULL, NULL ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 162424 11.53 29.49 4318 4881 675901 162424 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 162425 11.53 29.49 4318 4881 675901 162424 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 998 (JDA_POS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.02 0.01 0 0 0 0 Execute 4 1829.25 1828.68 4242 334849 174 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 1829.27 1828.69 4242 334849 174 1 Misses in library cache during parse: 2 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 835294 14.65 13.78 0 0 0 0 Execute 9692363 471.65 560.42 39652 100347 4591946 1147102 Fetch 9541218 2884.51 4259.15 3750930 140540200 1 6168523 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 20068875 3370.81 4833.36 3790582 140640547 4591947 7315625 Misses in library cache during parse: 20 Misses in library cache during execute: 6 835170 user SQL statements in session. 128 internal SQL statements in session. 835298 SQL statements in session. 10 statements EXPLAINed in this session. ******************************************************************************** Trace file: rmsprf01_ora_28031.trc Trace file compatibility: 9.02.00 Sort options: prsdsk exedsk fchdsk 1 session in tracefile. 835170 user SQL statements in trace file. 128 internal SQL statements in trace file. 835298 SQL statements in trace file. 37 unique SQL statements in trace file. 10 SQL statements EXPLAINed using schema: RMS10.prof$plan_table Default table was used. Table was created. Table was dropped. 24245683 lines in trace file.