Performance degraded after changing optimizer mode

Tags:
Oracle
Windows
Hi all, I facing a strange problem. Please help me. Server = Windows 2003 Oracle version - oracle 9.2.0.1.0 (standard edition) I have done the some changes at my client side. I done changes as follows.. 1) Tunning has done on dated 22/10/05 i) The database archive log mode has changed to Archive log mode. ii) Changed the value of prameter Cursor_sharing=FORCE Optimizer_mode=all_rows iii) The statistics has generated of database schema using DBMS_STATS package. iv) Big and Fragmented Index has analyzed. After this tuning The performance was degraded rather improveing. The export which was taking 5-6 min it took 50 min. After this i revert back the optimzer mode to 'choose'. And gether staistics on schema level using method option 'for all indexed column'. It improve the performance some wat. There is only one module is running slow. Other module of applications are fast. And the export take time around 3 min (measn its fast than before) When i run the same module from another machine, it run little bit fast than that users machne. I have attached all report regarding this database. Please help me. When i take tkprof output. it shows very much hardparsing there also the full table scan. But client said it the perforamance was better before doing this changes. wat will be the problem. Please give me reply as soon as possible. The tkprof output as follws---- -------------------------------------------------------- TKPROF: Release 9.2.0.1.0 - Production on Wed Oct 26 10:58:05 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: E:oracleadminorcludumporcl_ora_3212.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 ******************************************************************************** error connecting to database using: y ORA-01005: null password given; logon denied EXPLAIN PLAN option disabled. ******************************************************************************** select count ( *) from me_userg1 , me_user1 where me_user1.userg_code =me_userg1.userg_code and user_code =:"SYS_B_0" and ( funct_code in ( :"SYS_B_1" , :"SYS_B_2" , :"SYS_B_3" , :"SYS_B_4" , :"SYS_B_5" , :"SYS_B_6" , :"SYS_B_7" ) ) 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.00 0 14 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 14 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 1 NESTED LOOPS 8 TABLE ACCESS FULL ME_USERG1 1 INDEX UNIQUE SCAN ME_USER1 (object id 29509) ******************************************************************************** SELECT me_user2.user_posx , me_user2.user_posy , me_user2.user_sizew , me_user2.user_sizeh FROM me_user2 WHERE ( me_user2.user_code =:"SYS_B_0" ) AND ( me_user2.user_win = :"SYS_B_1" ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.01 0.00 0 0 0 0 Fetch 3 0.00 0.00 1 8 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.01 0.00 1 8 0 2 Misses in library cache during parse: 3 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ME_USER2 1 INDEX UNIQUE SCAN ME_USER2P (object id 29512) ******************************************************************************** SELECT "MG_TX"."TX_CODE", "MG_TX"."TX_NAME" FROM "MG_TX" 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 2 0.00 0.00 1 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 1 3 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS FULL MG_TX ******************************************************************************** select sysdate from dual call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 19 0.00 0.00 0 0 0 0 Execute 19 0.00 0.00 0 0 0 0 Fetch 19 0.01 0.00 2 57 0 19 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 57 0.01 0.00 2 57 0 19 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS FULL DUAL ******************************************************************************** update me_user2 set user_posx =:"SYS_B_0" , user_posy =:"SYS_B_1" , user_sizew =:"SYS_B_2" , user_sizeh =:"SYS_B_3" , USER_LAST = TO_DATE(:"SYS_B_4",:"SYS_B_5") WHERE ( me_user2.user_code =:"SYS_B_6" ) AND ( me_user2.user_win =:"SYS_B_7" ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.01 2 6 4 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.01 2 6 4 2 Misses in library cache during parse: 2 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY INDEX ROWID ME_USER2 1 INDEX UNIQUE SCAN ME_USER2P (object id 29512) ******************************************************************************** SELECT count(1) from me_function a where a.FUNCT_CODE like '%' || :b1 || '%' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 14 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 14 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 7 INDEX RANGE SCAN ME_FUNCTIONP (object id 29494) ******************************************************************************** SELECT "TP_CBS_DPS"."CHANNEL_CODE", "TP_CBS_DPS"."TX_CODE", "TP_CBS_DPS"."CBS_DATE", "TP_CBS_DPS"."DATE_OK" FROM "TP_CBS_DPS" WHERE ( "TP_CBS_DPS"."CHANNEL_CODE" = :"SYS_B_0" ) AND ( "TP_CBS_DPS"."CBS_DATE" >= to_date(:"SYS_B_1",:"SYS_B_2") ) AND ( "TP_CBS_DPS"."CBS_DATE" <= to_date(:"SYS_B_3", :"SYS_B_4") ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 20 0.01 0.05 52 130 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 40 0.01 0.05 52 130 0 10 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 FILTER 1 TABLE ACCESS FULL TP_CBS_DPS ******************************************************************************** SELECT TP_CBS_DPS1.CHANNEL_CODE, TP_CBS_DPS1.TX_CODE, TP_CBS_DPS1.CBS_DATE, TP_CBS_DPS1.SLOT_TTIME, TP_CBS_DPS1.SLOT_DUR_DEFINE, TP_CBS_DPS1.SLOT_DUR_CB, TP_CBS_DPS1.SLOT_DUR_PACK, TP_CBS_DPS1.CATSLOT_CODE, TP_CBS_DPS1.FILM_POC, TP_CBS_DPS1.ROW_ID_POC, TP_CBS_DPS1.FILM_EPI, TP_CBS_DPS1.SLOT_FIXED, TP_CBS_DPS1.PACK_CODE, TP_CBS_DPS1.SLOT_OK, TP_CBS_DPS1.HOUSE_NO, TP_CBS_DPS1.TAPE_NO, TP_CBS_DPS1.TAPE_DURATION, TP_CBS_DPS1.FILM_DURATION, TP_CBS_DPS1.MACHINE_CODE, TP_CBS_DPS1.SLOT_RATE, TP_CBS_DPS1.CURR_CODE, TP_CBS_DPS1.RATE_CODE, TP_CBS_DPS1.FILM_CHANGE, TP_CBS_DPS1.UPDATE_USER, TP_CBS_DPS1.UPDATE_DATE, TP_CBS_DPS1.ROW_ID, TP_CBS_DPS1.SLOT_DUR_DEFINE_S, ME_PACKAGE.PACK_NAME, MP_CATSLOT.CATSLOT_NAME, substr(rtrim(MP_FILM.FILM_POC_TITLE),:"SYS_B_00",:"SYS_B_01") || :"SYS_B_02" || substr(nvl(MP_FILM1.FILM_EPI_TITLE,:"SYS_B_03"),:"SYS_B_04",:"SYS_B_05")as FILM_POC_TITLE, MP_FILM1.FILM_EPI_TITLE, MG_MACHINE.MACHINE_NAME, MA_DEPEND.DEPEND_TITLE , win_support.get_dur_spot(TP_CBS_DPS1.ROW_ID,DEPEND_DURATION) as tot_duration FROM TP_CBS_DPS1, ME_PACKAGE, MP_CATSLOT, MP_FILM, MP_FILM1, MG_MACHINE, MA_DEPEND WHERE ( tp_cbs_dps1.pack_code = me_package.pack_code (+)) and ( tp_cbs_dps1.catslot_code = mp_catslot.catslot_code (+)) and ( tp_cbs_dps1.film_poc = mp_film.film_poc (+)) and ( tp_cbs_dps1.row_id_poc = mp_film.row_id (+)) and ( tp_cbs_dps1.film_poc = mp_film1.film_poc (+)) and ( tp_cbs_dps1.film_epi = mp_film1.film_epi (+)) and ( tp_cbs_dps1.row_id_poc = mp_film1.row_id_poc (+)) and ( tp_cbs_dps1.machine_code = mg_machine.machine_code (+)) and ( tp_cbs_dps1.house_no = ma_depend.house_no (+)) and ( TP_CBS_DPS1.CHANNEL_CODE = :"SYS_B_06" ) AND ( TP_CBS_DPS1.CBS_DATE >= TO_DATE(:"SYS_B_07", :"SYS_B_08") )AND ( TP_CBS_DPS1.CBS_DATE <= TO_DATE(:"SYS_B_09" , :"SYS_B_10") ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 240 0.62 2.39 2542 12792 0 1800 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 260 0.62 2.40 2542 12792 0 1800 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 180 FILTER 180 NESTED LOOPS OUTER 180 MERGE JOIN OUTER 180 SORT JOIN 180 MERGE JOIN OUTER 180 SORT JOIN 180 MERGE JOIN OUTER 180 SORT JOIN 180 MERGE JOIN OUTER 180 SORT JOIN 180 MERGE JOIN OUTER 180 SORT JOIN 180 TABLE ACCESS BY INDEX ROWID TP_CBS_DPS1 180 INDEX RANGE SCAN TP_CBS_DPS1P (object id 29976) 180 SORT JOIN 1 TABLE ACCESS FULL MP_CATSLOT 0 SORT JOIN 1 TABLE ACCESS FULL MG_MACHINE 180 SORT JOIN 193 TABLE ACCESS FULL MP_FILM 165 SORT JOIN 2817 TABLE ACCESS FULL MA_DEPEND 166 SORT JOIN 5407 TABLE ACCESS FULL MP_FILM1 0 TABLE ACCESS BY INDEX ROWID ME_PACKAGE 0 INDEX UNIQUE SCAN ME_PACKAGEP (object id 29498) ******************************************************************************** SELECT sum(f_jam_to_detik (tt_dts.CB_DUR_SPOT)) from tt_dts where row_id_slot = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1831 0.04 0.03 0 0 0 0 Fetch 1831 0.09 0.17 57 6708 0 1831 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3662 0.14 0.20 57 6708 0 1831 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT TT_DTS.CHANNEL_CODE, TT_DTS.TX_CODE, TT_DTS.CBS_DATE, TT_DTS.ROW_ID_SLOT, TT_DTS.CB_SEQ, TT_DTS.CB_TSTART, TT_DTS.CB_TTIME, TT_DTS.CB_DUR_DEFINE, TT_DTS.CB_DUR_SPOT, TT_DTS.CB_DUR_PACK, TT_DTS.SCHOBJ_CODE, TT_DTS.CB_NAME, TT_DTS.MACHINE_CODE, TT_DTS.CB_INCLUDE, TT_DTS.CB_OK, TT_DTS.UPDATE_USER, TT_DTS.UPDATE_DATE, TT_DTS.ROW_ID, TT_DTS.CB_DUR_DEFINE_S, TT_DTS.CB_DUR_DEFINE_F, TT_DTS.CB_DUR_PACK_S, TT_DTS.CB_DUR_PACK_F, TT_DTS.CB_DUR_SPOT_S, TT_DTS.CB_DUR_SPOT_F, MG_MACHINE.MACHINE_NAME, MG_SCHOBJ.SCHOBJ_NAME, LPad(TT_DTS.CB_SEQ,:"SYS_B_0",:"SYS_B_1") as CBSeq FROM TT_DTS, MG_MACHINE, MG_SCHOBJ WHERE ( tt_dts.machine_code = mg_machine.machine_code (+)) and ( tt_dts.schobj_code = mg_schobj.schobj_code (+)) and ( TT_DTS.CHANNEL_CODE = :"SYS_B_2" ) AND ( TT_DTS.TX_CODE = :"SYS_B_3" ) AND ( TT_DTS.CBS_DATE >= TO_DATE(:"SYS_B_4",:"SYS_B_5") ) AND ( TT_DTS.CBS_DATE <= TO_DATE(:"SYS_B_6",:"SYS_B_7" ) ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 20 0.01 0.05 17 300 0 2050 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 40 0.01 0.05 17 300 0 2050 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 205 FILTER 205 MERGE JOIN OUTER 205 SORT JOIN 205 MERGE JOIN OUTER 205 SORT JOIN 205 TABLE ACCESS BY INDEX ROWID TT_DTS 205 INDEX RANGE SCAN TT_DTSP (object id 30038) 44 SORT JOIN 1 TABLE ACCESS FULL MG_MACHINE 0 SORT JOIN 5 TABLE ACCESS FULL MG_SCHOBJ ******************************************************************************** SELECT /*+ FIRST_ROWS */ TT_DTS1.CHANNEL_CODE, TT_DTS1.TX_CODE, TT_DTS1.CBS_DATE, TT_DTS1.ROW_ID_SLOT, TT_DTS1.CB_SEQ, TT_DTS1.SPOT_SEQ, TT_DTS1.SPOT_SEQ_ADV, TT_DTS1.SEQ_PREMIUM, TT_DTS1.SPOT_TTIME, TT_DTS1.SPOT_DURATION, TT_DTS1.SPOT_TYPE, TT_DTS1.PROMO_PRIORITY, TT_DTS1.SPOT_CODE, TT_DTS1.SPOT_VERSION, TT_DTS1.SCHOBJ_CODE, TT_DTS1.SPOT_OK, TT_DTS1.SCHOBJ_TIME, TT_DTS1.ROW_ID_SPOT, TT_DTS1.HOUSE_NO, TT_DTS1.MACHINE_CODE, TT_DTS1.ROW_ID_PREEMP, TT_DTS1.SPOT_TTIME_TX, TT_DTS1.UPDATE_USER, TT_DTS1.UPDATE_DATE, TT_DTS1.ROW_ID, TT_DTS1.MO_NO, MT_PROD1.PROD_VERSION_NAME, MT_PROD.PROD_NAME , MT_PROD.PRODG_CODE , Lpad(TT_DTS1.SPOT_SEQ,:"SYS_B_00",:"SYS_B_01") as spotseq , :"SYS_B_02" AS PROMO_NAME FROM TT_DTS1, MT_PROD1,MT_PROD WHERE ( TT_DTS1.CHANNEL_CODE = :"SYS_B_03" ) AND ( TT_DTS1.TX_CODE = :"SYS_B_04" or TT_DTS1.TX_CODE in (select tx_code1 from mg_tx1 where tx_code=:"SYS_B_05" ) ) AND ( TT_DTS1.CBS_DATE >= to_date(:"SYS_B_06",:"SYS_B_07") ) AND ( TT_DTS1.CBS_DATE <= to_date(:"SYS_B_08" , :"SYS_B_09")) AND ( TT_DTS1.SPOT_TYPE = :"SYS_B_10" ) AND ( TT_DTS1.SPOT_CODE = MT_PROD.PROD_CODE ) AND ( TT_DTS1.SPOT_CODE = MT_PROD1.PROD_CODE ) AND ( TT_DTS1.SPOT_VERSION = MT_PROD1.PROD_VERSION ) UNION SELECT TT_DTS1.CHANNEL_CODE, TT_DTS1.TX_CODE, TT_DTS1.CBS_DATE, TT_DTS1.ROW_ID_SLOT, TT_DTS1.CB_SEQ, TT_DTS1.SPOT_SEQ, TT_DTS1.SPOT_SEQ_ADV, TT_DTS1.SEQ_PREMIUM, TT_DTS1.SPOT_TTIME, TT_DTS1.SPOT_DURATION, TT_DTS1.SPOT_TYPE, TT_DTS1.PROMO_PRIORITY, TT_DTS1.SPOT_CODE, TT_DTS1.SPOT_VERSION, TT_DTS1.SCHOBJ_CODE, TT_DTS1.SPOT_OK, TT_DTS1.SCHOBJ_TIME, TT_DTS1.ROW_ID_SPOT, TT_DTS1.HOUSE_NO, TT_DTS1.MACHINE_CODE, TT_DTS1.ROW_ID_PREEMP, TT_DTS1.SPOT_TTIME_TX, TT_DTS1.UPDATE_USER, TT_DTS1.UPDATE_DATE, TT_DTS1.ROW_ID, TT_DTS1.MO_NO, :"SYS_B_11" AS PROD_VERSION_NAME, (MR_PROMO.PROMO_NAME) AS PROD_NAME , :"SYS_B_12" AS PRODG_CODE , Lpad(TT_DTS1.SPOT_SEQ,:"SYS_B_13",:"SYS_B_14") as spotseq, MR_PROMO.PROMO_NAME FROM TT_DTS1, MR_PROMO WHERE ( TT_DTS1.CHANNEL_CODE = :"SYS_B_15" ) AND ( TT_DTS1.TX_CODE = :"SYS_B_16" or TT_DTS1.TX_CODE in (select tx_code1 from mg_tx1 where tx_code=:"SYS_B_17" )) AND ( TT_DTS1.CBS_DATE >= to_date(:"SYS_B_18",:"SYS_B_19") ) AND ( TT_DTS1.CBS_DATE <= to_date(:"SYS_B_20" , :"SYS_B_21")) AND ( TT_DTS1.SPOT_TYPE = :"SYS_B_22" ) AND ( TT_DTS1.SPOT_CODE = MR_PROMO.PROMO_CODE (+)) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 30 3.39 7.31 7184 213899 0 8689 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 50 3.39 7.31 7184 213899 0 8689 Misses in library cache during parse: 1 Optimizer goal: FIRST_ROWS Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 832 SORT UNIQUE 832 UNION-ALL 432 FILTER 432 FILTER 432 MERGE JOIN 432 MERGE JOIN 432 SORT JOIN 432 TABLE ACCESS BY INDEX ROWID TT_DTS1 832 INDEX RANGE SCAN TT_DTS1P (object id 30043) 432 SORT JOIN 1645 TABLE ACCESS FULL MT_PROD1 432 SORT JOIN 268 TABLE ACCESS FULL MT_PROD 0 INDEX UNIQUE SCAN MG_TX1P (object id 29546) 400 FILTER 400 FILTER 400 MERGE JOIN OUTER 400 SORT JOIN 400 TABLE ACCESS BY INDEX ROWID TT_DTS1 832 INDEX RANGE SCAN TT_DTS1P (object id 30043) 400 SORT JOIN 2910 TABLE ACCESS FULL MR_PROMO 0 INDEX UNIQUE SCAN MG_TX1P (object id 29546) ******************************************************************************** SELECT "TR_PRESEN"."CHANNEL_CODE", "TR_PRESEN"."CBS_DATE", "TR_PRESEN"."ROW_ID_SLOT", "TR_PRESEN"."PROMO_PRIORITY", "TR_PRESEN"."PROMO_CODE", "TR_PRESEN"."SCHOBJ_CODE", "TR_PRESEN"."PROMO_DURATION", "TR_PRESEN"."SCHOBJ_TIME", "TR_PRESEN"."HOUSE_NO", "TR_PRESEN"."MACHINE_CODE", "TR_PRESEN"."PROMO_TX_STATUS", "TR_PRESEN"."PROMO_TX_TIME_REAL", "TR_PRESEN"."PROMO_TX_REMARK", "TR_PRESEN"."UPDATE_USER", "TR_PRESEN"."UPDATE_DATE", "TR_PRESEN"."ROW_ID", "TR_PRESEN"."PROMO_DURATION_S", "TR_PRESEN"."PROMO_DURATION_F", "MR_PROMO"."PROMO_PRODNO", "MR_PROMO"."PROMO_NAME", "MR_PROMO"."PROMO_HOUSE_NO", "TR_PRESEN"."TX_CODE" FROM "TR_PRESEN", "MR_PROMO" WHERE ( tr_presen.promo_code = mr_promo.promo_code (+)) and ( ( "TR_PRESEN"."CHANNEL_CODE" = :"SYS_B_0" ) AND ( "TR_PRESEN"."TX_CODE" = :"SYS_B_1" ) AND ( "TR_PRESEN"."CBS_DATE" >= to_date(:"SYS_B_2",:"SYS_B_3") ) AND ( "TR_PRESEN"."CBS_DATE" <= to_date(:"SYS_B_4", :"SYS_B_5" )) ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 376 0.14 0.31 286 5071 0 11540 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 396 0.14 0.31 286 5071 0 11540 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 974 FILTER 974 MERGE JOIN OUTER 974 SORT JOIN 974 TABLE ACCESS BY INDEX ROWID TR_PRESEN 974 INDEX RANGE SCAN TR_PRESENP (object id 30011) 974 SORT JOIN 2910 TABLE ACCESS FULL MR_PROMO ******************************************************************************** SELECT :"SYS_B_0" as spot_seq , "MR_PROMO"."PROMO_CODE", "MR_PROMO"."PROMO_NAME", "MR_PROMO"."PROMO_HOUSE_NO", "MR_PROMO"."HOUSE_NO", "MR_PROMO"."PROMO_DURATION", "MR_PROMO"."PROMO_SOM", "MR_PROMO"."PROMO_EOM", "MR_PROMO"."PROMO_STD", "MR_PROMO"."SCHOBJ_CODE", "MR_PROMO"."MATERIAL_CODE", "MR_PROMO"."TAPET_CODE", "MR_PROMO"."MACHINE_CODE", "MR_PROMO"."PROMO_PRODNO", "MR_PROMO"."PROMO_DFROM", "MR_PROMO"."PROMO_DTO", "MR_PROMO"."PROMO_DPROG", "MR_PROMO"."PROMO_REMARK", "MR_PROMO"."UPDATE_USER", "MR_PROMO"."UPDATE_DATE", "MR_PROMO"."ROW_ID", "MG_SCHOBJ"."SCHOBJ_NAME", "MG_MACHINE"."MACHINE_NAME", "MG_MATERIAL"."MATERIAL_NAME", "MA_TAPE_TYPE"."TAPET_NAME", "SR_PROMO"."PROMO_LAST_TX", "MR_PROMO"."PROMO_DURATION_S", "MR_PROMO"."PROMO_DURATION_F", "MG_SCHOBJ"."SCHOBJ_TIME" FROM "MR_PROMO", "MG_SCHOBJ", "MA_TAPE_TYPE", "MG_MACHINE", "MG_MATERIAL", "SR_PROMO" WHERE ( mr_promo.schobj_code = mg_schobj.schobj_code (+)) and ( mr_promo.material_code = mg_material.material_code (+)) and ( mr_promo.tapet_code = ma_tape_type.tapet_code (+)) and ( mr_promo.machine_code = mg_machine.machine_code (+)) and ( mr_promo.promo_code = sr_promo.promo_code (+)) and ( ( "MR_PROMO"."PROMO_STD" = :"SYS_B_1" ) ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 210 0.14 0.12 7 1620 0 6230 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 230 0.14 0.12 7 1620 0 6230 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 623 NESTED LOOPS OUTER 623 NESTED LOOPS OUTER 623 NESTED LOOPS OUTER 623 MERGE JOIN OUTER 623 SORT JOIN 623 MERGE JOIN OUTER 623 SORT JOIN 623 TABLE ACCESS FULL MR_PROMO 0 SORT JOIN 1 TABLE ACCESS FULL MG_MATERIAL 623 SORT JOIN 5 TABLE ACCESS FULL MG_SCHOBJ 0 TABLE ACCESS BY INDEX ROWID SR_PROMO 0 INDEX UNIQUE SCAN SR_PROMOP (object id 29786) 0 TABLE ACCESS BY INDEX ROWID MG_MACHINE 0 INDEX UNIQUE SCAN MG_MACHINEP (object id 29532) 0 TABLE ACCESS BY INDEX ROWID MA_TAPE_TYPE 0 INDEX UNIQUE SCAN MA_TAPE_TYPEP (object id 29467) ******************************************************************************** SELECT "MR_PROMO"."PROMO_CODE", "MR_PROMO"."PROMO_NAME", "MR_PROMO"."PROMO_HOUSE_NO", "MR_PROMO"."HOUSE_NO", "MR_PROMO"."PROMO_DURATION", "MR_PROMO"."PROMO_SOM", "MR_PROMO"."PROMO_EOM", "MR_PROMO"."PROMO_STD", "MR_PROMO"."SCHOBJ_CODE", "MR_PROMO"."MATERIAL_CODE", "MR_PROMO"."TAPET_CODE", "MR_PROMO"."MACHINE_CODE", "MR_PROMO"."PROMO_PRODNO", "MR_PROMO"."PROMO_DFROM", "MR_PROMO"."PROMO_DTO", "MR_PROMO"."PROMO_DPROG", "MR_PROMO"."PROMO_REMARK", "MR_PROMO"."UPDATE_USER", "MR_PROMO"."UPDATE_DATE", "MR_PROMO"."ROW_ID", "MG_SCHOBJ"."SCHOBJ_NAME", "MG_MACHINE"."MACHINE_NAME", "MG_MATERIAL"."MATERIAL_NAME", "MA_TAPE_TYPE"."TAPET_NAME", "SR_PROMO"."PROMO_LAST_TX", "MR_PROMO"."PROMO_DURATION_S", "MR_PROMO"."PROMO_DURATION_F", "MG_SCHOBJ"."SCHOBJ_TIME", :"SYS_B_0" as spot_seq FROM "MR_PROMO", "MG_SCHOBJ", "MA_TAPE_TYPE", "MG_MACHINE", "MG_MATERIAL", "SR_PROMO" WHERE ( mr_promo.schobj_code = mg_schobj.schobj_code (+)) and ( mr_promo.material_code = mg_material.material_code (+)) and ( mr_promo.tapet_code = ma_tape_type.tapet_code (+)) and ( mr_promo.machine_code = mg_machine.machine_code (+)) and ( mr_promo.promo_code = sr_promo.promo_code (+)) AND ( MR_PROMO.PROMO_STD <> :"SYS_B_1" OR MR_PROMO.PROMO_STD IS NULL) AND ("MR_PROMO"."PROMO_DFROM" <= to_date(:"SYS_B_2",:"SYS_B_3") ) and ( "MR_PROMO"."PROMO_DTO" >= to_date(:"SYS_B_4",:"SYS_B_5") ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 20 0.03 0.02 2 1630 0 230 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 40 0.03 0.02 2 1630 0 230 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 23 NESTED LOOPS OUTER 23 NESTED LOOPS OUTER 23 NESTED LOOPS OUTER 23 NESTED LOOPS OUTER 23 NESTED LOOPS OUTER 23 TABLE ACCESS FULL MR_PROMO 0 TABLE ACCESS BY INDEX ROWID SR_PROMO 0 INDEX UNIQUE SCAN SR_PROMOP (object id 29786) 0 TABLE ACCESS BY INDEX ROWID MG_MACHINE 0 INDEX UNIQUE SCAN MG_MACHINEP (object id 29532) 0 TABLE ACCESS BY INDEX ROWID MA_TAPE_TYPE 0 INDEX UNIQUE SCAN MA_TAPE_TYPEP (object id 29467) 0 TABLE ACCESS BY INDEX ROWID MG_MATERIAL 0 INDEX UNIQUE SCAN MG_MATERIALP (object id 29534) 23 TABLE ACCESS BY INDEX ROWID MG_SCHOBJ 23 INDEX UNIQUE SCAN MG_SCHOBJP (object id 29536) ******************************************************************************** select tx_name from mg_tx where tx_code =:"SYS_B_0" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.01 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 10 0.00 0.00 2 20 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 30 0.01 0.00 2 20 0 10 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID MG_TX 1 INDEX UNIQUE SCAN MG_TXP (object id 29544) ******************************************************************************** SELECT distinct TP_CBS_DPS1.TX_CODE, MP_CATSLOT.CATSLOT_NAME, TP_CBS_DPS1.SLOT_TTIME, MP_FILM.FILM_POC_TITLE, TP_CBS_DPS1.FILM_DURATION, TP_CBS_DPS1.FILM_POC, TP_CBS_DPS1.FILM_EPI, TP_CBS_DPS1.HOUSE_NO, TP_CBS_DPS1.UPDATE_USER, mp_film.FILM_LICEN_RUN as actual_runs, F_RUNS_AIRED_All( TP_CBS_DPS1.FILM_POC, TP_CBS_DPS1.ROW_ID_POC, TP_CBS_DPS1.FILM_EPI ) as runs, MP_FILM1.FILM_LICEN_DFROM, MP_FILM1.FILM_LICEN_Dto, TP_CBS_DPS1.cbs_date, SLOT_RATE_CODE, BLOCK_PO_NUMBER, MP_FILM1.EPI_NO ,DEPEND_DURATION, win_support.get_dur_spot(TP_CBS_DPS1.ROW_ID,DEPEND_DURATION) as tot_duration, TP_CBS_DPS1.po_no, "TT_DTS"."CB_SEQ", "TT_DTS"."CB_NAME" , "TT_DTS".tx_code, f_jam_to_detik("TT_DTS"."CB_DUR_DEFINE")/ :"SYS_B_0" as CB_Dur, f_jam_to_detik("TT_DTS"."CB_DUR_SPOT")/:"SYS_B_1" as Occup_dur, f_jam_to_detik(subtime(CB_DUR_DEFINE,CB_DUR_SPOT))/:"SYS_B_2" as avail_dur, mg_tx.priority FROM MP_CATSLOT, MP_FILM, TP_CBS_DPS1, MP_FILM1,MA_DEPEND, tt_dts,mg_tx WHERE( TP_CBS_DPS1.FILM_POC = MP_FILM.FILM_POC(+) ) and ( TP_CBS_DPS1.ROW_ID_POC = MP_FILM.ROW_ID(+) ) and ( TP_CBS_DPS1.CATSLOT_CODE = MP_CATSLOT.CATSLOT_CODE(+) ) and ( TP_CBS_DPS1.FILM_POC = MP_FILM1.FILM_POC (+) ) and ( TP_CBS_DPS1.ROW_ID = MP_FILM1.ROW_ID_POC (+) ) and (TP_CBS_DPS1.film_epi = MP_FILM1.EPI_NO (+)) and (TP_CBS_DPS1.house_no = MA_DEPEND.house_no (+)) and (( TP_CBS_DPS1.ROW_ID = :"SYS_B_3" ) ) and TP_CBS_DPS1.row_id = tt_dts.row_id_slot (+) and tt_dts.tx_code = mg_tx.tx_code (+) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 9 0.00 0.01 0 0 0 0 Execute 9 0.00 0.00 0 0 0 0 Fetch 18 0.03 0.09 24 211 0 31 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 36 0.03 0.10 24 211 0 31 Misses in library cache during parse: 7 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 4 SORT UNIQUE 4 NESTED LOOPS OUTER 4 MERGE JOIN OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 TABLE ACCESS BY INDEX ROWID TP_CBS_DPS1 1 INDEX UNIQUE SCAN TPCBSDPS1_ROWID (object id 29977) 1 TABLE ACCESS BY INDEX ROWID MP_FILM 1 INDEX UNIQUE SCAN MP_FILMP (object id 29564) 1 TABLE ACCESS BY INDEX ROWID MP_CATSLOT 1 INDEX UNIQUE SCAN MP_CATSLOTP (object id 29555) 1 TABLE ACCESS BY INDEX ROWID MA_DEPEND 1 INDEX UNIQUE SCAN MA_DEPENDP (object id 29458) 0 TABLE ACCESS BY INDEX ROWID MP_FILM1 0 INDEX RANGE SCAN MP_FILM1P (object id 29572) 4 BUFFER SORT 4 TABLE ACCESS BY INDEX ROWID TT_DTS 4 INDEX RANGE SCAN INDEX_SLOT (object id 41616) 4 TABLE ACCESS BY INDEX ROWID MG_TX 4 INDEX UNIQUE SCAN MG_TXP (object id 29544) ******************************************************************************** SELECT COUNT( 1) FROM TP_CBS_DPS1 WHERE FILM_POC = :b3 AND ROW_ID_POC = :b2 AND FILM_EPI = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 31 0.00 0.00 0 0 0 0 Fetch 31 0.00 0.08 17 89 0 31 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 62 0.00 0.08 17 89 0 31 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT count ( *) FROM "TT_DTS" WHERE ( "TT_DTS"."CHANNEL_CODE" =:"SYS_B_0" ) AND ( "TT_DTS"."TX_CODE" =:"SYS_B_1" ) AND ( "TT_DTS"."CBS_DATE" =to_date ( :"SYS_B_2" , :"SYS_B_3" ) ) AND ( "TT_DTS"."ROW_ID_SLOT" =:"SYS_B_4" ) AND ( "TT_DTS"."CB_SEQ" =to_number ( :"SYS_B_5" ) ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 32 0.00 0.00 0 0 0 0 Execute 32 0.00 0.00 0 0 0 0 Fetch 32 0.00 0.00 1 96 0 32 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96 0.00 0.01 1 96 0 32 Misses in library cache during parse: 7 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 1 INDEX UNIQUE SCAN TT_DTSP (object id 30038) ******************************************************************************** SELECT "TT_DTS"."TX_CODE", "TT_DTS"."CB_SEQ", "TT_DTS"."CB_NAME", "TT_DTS"."CB_TSTART", "TT_DTS"."CB_TTIME", "TT_DTS"."CB_DUR_DEFINE", f_jam_to_detik( "TT_DTS"."CB_DUR_DEFINE") as total_dur, f_jam_to_detik( nvl("TT_DTS"."CB_DUR_SPOT",:"SYS_B_00")) as used_dur , SUBTIME(nvl("TT_DTS"."CB_DUR_DEFINE",:"SYS_B_01") , nvl("TT_DTS"."CB_DUR_SPOT",:"SYS_B_02")) as avail_dur, PROMO_TIME_IN_CB(tt_dts.CHANNEL_CODE,:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06") AS P_TIME, "TT_DTS"."CB_INCLUDE", "TT_DTS"."UPDATE_USER", tp_cbs_dps1.slot_rate_code FROM "TT_DTS", tp_cbs_dps1 Where "TT_DTS"."CHANNEL_CODE" = :"SYS_B_07" And "TT_DTS"."TX_CODE" = :"SYS_B_08" And "TT_DTS"."CBS_DATE" = to_date(:"SYS_B_09",:"SYS_B_10") And "TT_DTS"."ROW_ID_SLOT" = :"SYS_B_11" And "TT_DTS"."CB_SEQ" = :"SYS_B_12" and tp_cbs_dps1.CHANNEL_CODE = tt_dts.CHANNEL_CODE and tp_cbs_dps1.ROW_ID = tt_dts.ROW_ID_SLOT call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 32 0.03 0.02 0 0 0 0 Execute 32 0.00 0.00 0 0 0 0 Fetch 64 0.00 0.01 0 256 0 32 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 128 0.03 0.03 0 256 0 32 Misses in library cache during parse: 20 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID TP_CBS_DPS1 1 INDEX UNIQUE SCAN TPCBSDPS1_ROWID (object id 29977) 1 TABLE ACCESS BY INDEX ROWID TT_DTS 1 INDEX UNIQUE SCAN TT_DTSP (object id 30038) ******************************************************************************** SELECT SPOT_DURATION from TT_DTS1 where CHANNEL_CODE = :b5 AND TX_CODE = :b4 AND CBS_DATE = to_date(:b3,'YYYY/MM/DD') AND ROW_ID_SLOT = :b2 AND CB_SEQ = :b1 AND SPOT_TYPE = 'P' AND NVL(SCHOBJ_TIME,'Y') = 'Y' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 32 0.00 0.00 0 0 0 0 Fetch 37 0.00 0.00 5 400 0 5 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 69 0.00 0.00 5 400 0 5 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT count ( *) FROM "TT_DTS1" WHERE ( "TT_DTS1"."CHANNEL_CODE" =:"SYS_B_0" ) AND ( "TT_DTS1"."TX_CODE" =:"SYS_B_1" ) AND ( "TT_DTS1"."CBS_DATE" =to_date ( :"SYS_B_2" , :"SYS_B_3" ) ) AND ( "TT_DTS1"."ROW_ID_SLOT" =:"SYS_B_4" ) AND ( "TT_DTS1"."CB_SEQ" =to_number ( :"SYS_B_5" ) ) AND ( "TT_DTS1"."SPOT_SEQ" =:"SYS_B_6" ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 88 0.06 0.05 0 0 0 0 Execute 88 0.00 0.00 0 0 0 0 Fetch 88 0.00 0.02 4 354 0 88 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 264 0.06 0.08 4 354 0 88 Misses in library cache during parse: 63 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 1 INDEX RANGE SCAN TT_DTS1_SORTED_IDX_081 (object id 30047) ******************************************************************************** SELECT "MT_CLIENT"."CLIENT_CODE", "MT_CLIENT"."CLIENT_COM_NAME", "MT_CLIENT"."CLIENT_PIC", "MT_CLIENT"."CLIENTG_CODE", "MT_CLIENT"."CLIENT_PRIORITY", "MT_CLIENT"."TCOUNTRY_CODE", "MT_CLIENT"."CLIENT_BLOCKD", "MT_CLIENT1"."AGEN_CODE" FROM "MT_CLIENT", "MT_CLIENT1" WHERE ( "MT_CLIENT"."CLIENT_CODE" = "MT_CLIENT1"."CLIENT_CODE" ) ORDER BY "MT_CLIENT"."CLIENT_CODE" ASC 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.00 0.02 16 18 0 159 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.02 16 18 0 159 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 159 MERGE JOIN 159 SORT JOIN 159 TABLE ACCESS FULL MT_CLIENT1 159 SORT JOIN 162 TABLE ACCESS FULL MT_CLIENT ******************************************************************************** SELECT "MT_AGEN"."AGEN_CODE", "MT_AGEN"."AGEN_COM_NAME", "MT_AGEN"."AGEN_PIC", nvl("MT_AGEN"."AGEN_TOP",:"SYS_B_0") , "MT_AGEN"."AGEN_CREDIT_LIMIT", nvl("MT_AGEN"."AGEN_DISC0",:"SYS_B_1"), "MT_AGEN"."TCOUNTRY_CODE", "MT_AGEN"."AGEN_TYPE", "MT_AGEN"."AGENG_CODE", "MT_AGEN"."AGEN_PRIORITY", "MT_AGEN"."AGEN_COM_ADDR1", "MT_AGEN"."AGEN_COM_ADDR2", "MT_AGEN"."AGEN_COM_PHONE", "MT_AGEN"."AGEN_COM_FAX", "MT_AGEN"."AGEN_COM_EMAIL", "MT_AGEN"."AGEN_COM_WEB", "MT_AGEN"."POST_USER", nvl("MT_COUNTRY"."VAT_P", :"SYS_B_2"), AGEN_TAX_ID, agen_top, mt_agen.agen_com_addr3 FROM "MT_AGEN", "MT_COUNTRY" WHERE ( mt_agen.tcountry_code = mt_country.tcountry_code (+)) and ( ( "MT_AGEN"."POST_USER" is not null ) ) 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.00 0.02 13 15 0 94 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.02 13 15 0 94 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 94 MERGE JOIN OUTER 94 SORT JOIN 94 TABLE ACCESS FULL MT_AGEN 94 SORT JOIN 1 TABLE ACCESS FULL MT_COUNTRY ******************************************************************************** SELECT mt_prod.prod_code , mt_prod.prod_name , mt_prod.prodg_code , mt_prod.prodg_code1 , mt_prod.client_code , mt_client.client_com_name FROM mt_prod , mt_client WHERE ( mt_prod.client_code = mt_client.client_code (+)) 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.00 0.00 0 13 0 268 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 13 0 268 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 268 NESTED LOOPS OUTER 268 TABLE ACCESS FULL MT_PROD 0 TABLE ACCESS BY INDEX ROWID MT_CLIENT 0 INDEX UNIQUE SCAN MT_CLIENTP (object id 29631) ******************************************************************************** select TD1.TX_CODE, TM.AGEN_CODE, TM.CLIENT_CODE, TM1.PROD_CODE, TM1.PROD_VERSION, TD1.SPOT_DURATION, TM1.RATE_CODE, TM1.RATE_PRICE_TRX, TM.UPDATE_USER, TM.UPDATE_DATE, TD1.HOUSE_NO, nvl( TD1.SCHOBJ_TIME, :"SYS_B_0" ) as SCHOBJ_TIME, TM.MO_NO, TM.MO_DATE, TM1.MO_REMARK, MP1.PROD_VERSION_NAME, MSR1.slot_rate, TD1.SCHOBJ_CODE, TM1.flag_rate, book_valid.is_spots_ros( TM1.row_id_res1, TM1.row_id_matrix1 ) as isROS, nvl( TM1.LINE_NU_RES1, TM1.LINE_NU_MAT1 ) as po_line_no, F_SPOTS_PER_LINE_SLOT( TM1.row_id_res1, TM1.row_id_matrix1,TM1.ROW_ID_SLOT) AS SPOTS_PER_LINE, TM1.special_pos, MF.FILM_POC_TITLE, TM1.MKGD_FROM_DATE from TT_DTS1 TD1, TT_MO TM, TT_MO1 TM1, MT_PROD1 MP1, mt_slot_rate1 MSR1, mp_film MF where TM.CHANNEL_CODE = TD1.CHANNEL_CODE and TM.MO_NO = TD1.MO_NO and TM1.CHANNEL_CODE = TM.CHANNEL_CODE and TM1.MO_NO = TM.MO_NO and TM1.ROW_ID = TD1.ROW_ID_SPOT and TM1.PROD_CODE = MP1.PROD_CODE and TM1.PROD_VERSION = MP1.PROD_VERSION and TM1.FILM_POC = MF.FILM_POC(+) and TM1.ROW_ID_POC = MF.ROW_ID(+) and row_id_slot_rate1 = MSR1.row_id(+) and TD1.CHANNEL_CODE = :"SYS_B_1" and TD1.TX_CODE = :"SYS_B_2" and TD1.CBS_DATE = to_date( :"SYS_B_3", :"SYS_B_4" ) and TD1.ROW_ID_SLOT = :"SYS_B_5" and TD1.CB_SEQ = :"SYS_B_6" and TD1.SPOT_SEQ = :"SYS_B_7" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 88 0.12 0.13 0 0 0 0 Execute 88 0.00 0.00 0 0 0 0 Fetch 176 0.01 0.61 190 1852 0 88 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 352 0.14 0.75 190 1852 0 88 Misses in library cache during parse: 70 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID TT_DTS1 1 INDEX RANGE SCAN TT_DTS1_SORTED_IDX_081 (object id 30047) 1 TABLE ACCESS BY INDEX ROWID TT_MO 1 INDEX UNIQUE SCAN TT_MOP (object id 30059) 1 TABLE ACCESS BY INDEX ROWID TT_MO1 1 INDEX UNIQUE SCAN TT_MO1P (object id 30071) 1 TABLE ACCESS BY INDEX ROWID MP_FILM 1 INDEX UNIQUE SCAN MP_FILMP (object id 29564) 1 TABLE ACCESS BY INDEX ROWID MT_SLOT_RATE1 1 INDEX UNIQUE SCAN PK_SLOT_RATE1 (object id 29702) 1 TABLE ACCESS BY INDEX ROWID MT_PROD1 1 INDEX UNIQUE SCAN MT_PROD1P (object id 29662) ******************************************************************************** SELECT FILM_POC, AIRTIME_FROM, AIRTIME_TO from ST_RESERVATION_MATRIX1 where row_id = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 89 0.00 0.00 0 0 0 0 Fetch 89 0.00 0.11 47 267 0 89 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 178 0.00 0.11 47 267 0 89 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT COUNT(1) FROM TT_MO1 WHERE ROW_ID_SLOT = :b2 AND MO_BOOK_STATUS = '2' AND ROW_ID_MATRIX1 = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 88 0.00 0.00 0 0 0 0 Fetch 88 0.00 0.34 224 764 0 88 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 176 0.00 0.35 224 764 0 88 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_0" ) =:"SYS_B_1" and tx_code = :"SYS_B_2" and channel_code =:"SYS_B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and row_id =:"SYS_B_6" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 7 5.85 7.74 3570 70399 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 21 5.85 7.75 3570 70399 0 7 Misses in library cache during parse: 7 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TT_DTS1 1 INDEX RANGE SCAN TT_DTS1P (object id 30043) ******************************************************************************** Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_0" ) =:"SYS_B_1" and tx_code = :"SYS_B_2" and channel_code =:"SYS_B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and spot_seq =:"SYS_B_6" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.01 0.00 0 0 0 0 Execute 8 0.00 0.00 0 0 0 0 Fetch 8 6.43 12.25 26993 71536 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 24 6.45 12.25 26993 71536 0 2 Misses in library cache during parse: 8 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 INDEX FAST FULL SCAN TT_DTS1_SORTED_IDX_081 (object id 30047) ******************************************************************************** Select Max ( spot_seq ) from tt_dts1 where to_char ( cbs_date , :"SYS_B_0" ) =:"SYS_B_1" and tx_code = :"SYS_B_2" and channel_code =:"SYS_B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 1.45 3.49 8924 17884 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 1.45 3.49 8924 17884 0 2 Misses in library cache during parse: 2 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 15 INDEX FAST FULL SCAN TT_DTS1_SORTED_IDX_081 (object id 30047) ******************************************************************************** UPDATE TT_DTS1 SET spot_seq = :"SYS_B_00", update_user = :"SYS_B_01", update_date = TO_DATE(:"SYS_B_02",:"SYS_B_03") WHERE channel_code = :"SYS_B_04" AND tx_code = :"SYS_B_05" AND cbs_date = TO_DATE(:"SYS_B_06",:"SYS_B_07") AND row_id_slot = :"SYS_B_08" AND cb_seq = :"SYS_B_09" AND row_id_spot = :"SYS_B_10" AND row_id = :"SYS_B_11" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 7 0.00 0.00 4 28 73 7 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 14 0.00 0.01 4 28 73 7 Misses in library cache during parse: 7 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN TT_DTS1P (object id 30043) ******************************************************************************** select book_valid.is_spots_ros ( :"SYS_B_0" ) from dual 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.00 1 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 1 3 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS FULL DUAL ******************************************************************************** SELECT ROW_ID_RES1, ROW_ID_MATRIX1 from TT_MO1 A where ROW_ID = :b1 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 2 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 2 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** select book_valid.is_CB_in_ros ( :"SYS_B_0" , :"SYS_B_1" , :"SYS_B_2" ) from dual 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.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 3 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS FULL DUAL ******************************************************************************** SELECT SLOT_TTIME FROM TP_CBS_DPS1 WHERE ROW_ID = :b1 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 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT CB_TSTART FROM TT_DTS WHERE ROW_ID_SLOT = :b2 AND CB_SEQ = :b1 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 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** select slot_rate_code from tp_cbs_dps1 where CHANNEL_CODE =:"SYS_B_0" and TX_CODE =:"SYS_B_1" and CBS_DATE =to_date ( :"SYS_B_2" , :"SYS_B_3" ) and ROW_ID =:"SYS_B_4" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 8 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 8 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TP_CBS_DPS1 1 INDEX UNIQUE SCAN TP_CBS_DPS1P (object id 29976) ******************************************************************************** select curr_code from tt_mo where CHANNEL_CODE =:"SYS_B_0" and MO_NO =:"SYS_B_1" 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 1 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 1 3 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TT_MO 1 INDEX UNIQUE SCAN TT_MOP (object id 30059) ******************************************************************************** select cb_dur_define , cb_dur_spot from tt_dts where channel_code =:"SYS_B_0" and tx_code =:"SYS_B_1" and cbs_date = TO_DATE(:"SYS_B_2",:"SYS_B_3") and row_id_slot =:"SYS_B_4" and cb_seq = :"SYS_B_5" 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 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TT_DTS 1 INDEX UNIQUE SCAN TT_DTSP (object id 30038) ******************************************************************************** Select tp_cbs_dps1.film_poc , tp_cbs_dps1.row_id_poc , tp_cbs_dps1.film_epi from tp_cbs_dps1 where tp_cbs_dps1.channel_code =:"SYS_B_0" and tp_cbs_dps1.tx_code =:"SYS_B_1" and tp_cbs_dps1.cbs_date = TO_DATE(:"SYS_B_2",:"SYS_B_3") and tp_cbs_dps1.row_id =:"SYS_B_4" 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 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TP_CBS_DPS1 1 INDEX UNIQUE SCAN TP_CBS_DPS1P (object id 29976) ******************************************************************************** update TT_MO1 SET TT_MO1.film_poc =:"SYS_B_00" , TT_MO1.row_id_poc = :"SYS_B_01" , TT_MO1.film_epi =:"SYS_B_02" , TT_MO1.update_user = :"SYS_B_03" , TT_MO1.update_date =TO_DATE(:"SYS_B_04",:"SYS_B_05") , TT_MO1.mo_book_cb =:"SYS_B_06" , tt_mo1.mo_book_seq =:"SYS_B_07" , tt_mo1.row_id_slot =:"SYS_B_08" , tt_mo1.mo_book_date =TO_DATE(:"SYS_B_09", :"SYS_B_10") where TT_MO1.CHANNEL_CODE =:"SYS_B_11" and TT_MO1.MO_NO =:"SYS_B_12" and TT_MO1.ROW_ID =:"SYS_B_13" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 2 4 6 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 2 4 6 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY INDEX ROWID TT_MO1 1 INDEX UNIQUE SCAN TT_MO1P (object id 30071) ******************************************************************************** SELECT count( 1 ) from tb_v_lock 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 4 6 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 4 6 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 0 TABLE ACCESS FULL TB_V_LOCK ******************************************************************************** SELECT count( 1 ) from "TT_MO2" where "TT_MO2"."ROW_ID_SPOT" = :b1 and "TT_MO2"."VERIFIED" = 'Y' 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 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 3 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 0 TABLE ACCESS BY INDEX ROWID TT_MO2 0 INDEX UNIQUE SCAN TT_MO2P (object id 30083) ******************************************************************************** SELECT nvl( POSTED, 'N' ), nvl( LEVEL_OPEN, 'N' ) from st_reservations where row_id = :b1 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 2 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 2 3 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ST_RESERVATIONS 1 INDEX UNIQUE SCAN SR_PK (object id 29818) ******************************************************************************** SELECT COLUMN_NAME from INT_CHANGE where TABLE_NAME = 'TT_MO1' 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 12 0.00 0.00 1 13 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 14 0.00 0.00 1 13 0 11 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 11 TABLE ACCESS FULL INT_CHANGE ******************************************************************************** UPDATE TT_DTS1 SET channel_code = :"SYS_B_00", tx_code = :"SYS_B_01", cbs_date = TO_DATE(:"SYS_B_02",:"SYS_B_03"), row_id_slot = :"SYS_B_04", cb_seq = :"SYS_B_05", spot_seq = :"SYS_B_06", update_user = :"SYS_B_07", update_date = TO_DATE(:"SYS_B_08",:"SYS_B_09") WHERE channel_code = :"SYS_B_10" AND tx_code = :"SYS_B_11" AND cbs_date = TO_DATE(:"SYS_B_12",:"SYS_B_13") AND row_id_slot = :"SYS_B_14" AND cb_seq = :"SYS_B_15" AND row_id_spot = :"SYS_B_16" AND row_id = :"SYS_B_17" 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 5 22 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 5 22 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY INDEX ROWID TT_DTS1 1 INDEX UNIQUE SCAN TT_DTS1P (object id 30043) ******************************************************************************** UPDATE TT_DTS set CB_DUR_SPOT = ADDTIME( nvl( CB_DUR_SPOT, '00000000' ), :b6 ) where CHANNEL_CODE = :b5 and TX_CODE = :b4 and CBS_DATE = :b3 and ROW_ID_SLOT = :b2 and CB_SEQ = :b1 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 4 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 4 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY INDEX ROWID TT_DTS 1 INDEX UNIQUE SCAN TT_DTSP (object id 30038) ******************************************************************************** UPDATE TT_DTS set CB_DUR_SPOT = SUBTIME( nvl( CB_DUR_SPOT, '00000000' ), :b1 ) where CHANNEL_CODE = :b6 and TX_CODE = :b5 and CBS_DATE = :b4 and ROW_ID_SLOT = :b3 and CB_SEQ = :b2 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 4 4 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 4 4 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY INDEX ROWID TT_DTS 1 INDEX UNIQUE SCAN TT_DTSP (object id 30038) ******************************************************************************** UPDATE MT_PROMO_SCHEDULE SET last_line_num = :"SYS_B_0" WHERE row_id = :"SYS_B_1" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.04 3 4 4 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.04 3 4 4 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN SYS_C009499 (object id 39218) ******************************************************************************** UPDATE PROMO_SCHEDULE1 SET promo_code = :"SYS_B_0" WHERE row_id = :"SYS_B_1" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.02 4 3 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.02 4 3 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN SYS_C009511 (object id 39222) ******************************************************************************** SELECT a.CHANNEL_CODE, a.YYYYMM, a.pr_sch_code, a.pr_sch_remark, a.update_user, a.update_date, a.row_id, a.last_line_num FROM MT_PROMO_SCHEDULE a call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.01 10 26 0 712 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 8 0.00 0.01 10 26 0 712 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 356 TABLE ACCESS FULL MT_PROMO_SCHEDULE ******************************************************************************** SELECT "PROMO_SCHEDULE"."D1", "PROMO_SCHEDULE"."D2", "PROMO_SCHEDULE"."D3", "PROMO_SCHEDULE"."D4", "PROMO_SCHEDULE"."D5", "PROMO_SCHEDULE"."D6", "PROMO_SCHEDULE"."D7", "PROMO_SCHEDULE"."D8", "PROMO_SCHEDULE"."D9", "PROMO_SCHEDULE"."D10", "PROMO_SCHEDULE"."D11", "PROMO_SCHEDULE"."D12", "PROMO_SCHEDULE"."D13", "PROMO_SCHEDULE"."D14", "PROMO_SCHEDULE"."D15", "PROMO_SCHEDULE"."D16", "PROMO_SCHEDULE"."D17", "PROMO_SCHEDULE"."D18", "PROMO_SCHEDULE"."D19", "PROMO_SCHEDULE"."D20", "PROMO_SCHEDULE"."D21", "PROMO_SCHEDULE"."D22", "PROMO_SCHEDULE"."D23", "PROMO_SCHEDULE"."D24", "PROMO_SCHEDULE"."D25", "PROMO_SCHEDULE"."D26", "PROMO_SCHEDULE"."D27", "PROMO_SCHEDULE"."D28", "PROMO_SCHEDULE"."D29", "PROMO_SCHEDULE"."D30", "PROMO_SCHEDULE"."D31", "PROMO_SCHEDULE"."TIME_FROM", "PROMO_SCHEDULE"."TIME_TO", "PROMO_SCHEDULE"."FILM_POC", "PROMO_SCHEDULE"."ROW_ID_POC", "PROMO_SCHEDULE"."SCH", "PROMO_SCHEDULE"."UPDATE_USER", "PROMO_SCHEDULE"."UPDATE_DATE", "PROMO_SCHEDULE"."ROW_ID", "PROMO_SCHEDULE"."TX_CODE", "PROMO_SCHEDULE"."LINE_NUM", "PROMO_SCHEDULE"."ROW_ID_MT", "PROMO_SCHEDULE"."YYYYMM" , mp_film.film_poc_title film_poc_title, :"SYS_B_0" tmp FROM "PROMO_SCHEDULE", mp_film where PROMO_SCHEDULE.film_poc = mp_film.film_poc(+) and promo_schedule.row_id_poc = mp_film.row_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 10 0.01 0.13 138 152 0 3107 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.01 0.13 138 152 0 3107 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 3107 MERGE JOIN OUTER 3107 SORT JOIN 3107 TABLE ACCESS FULL PROMO_SCHEDULE 1697 SORT JOIN 193 TABLE ACCESS FULL MP_FILM ******************************************************************************** SELECT "PROMO_SCHEDULE1"."ROW_ID", "PROMO_SCHEDULE1"."PROMO_CODE", "PROMO_SCHEDULE1"."CB_NAME", "PROMO_SCHEDULE1"."PREF_CB_SEQ", "PROMO_SCHEDULE1"."PREF_PROMO_SEQ", "PROMO_SCHEDULE1"."PROMO_PRIORITY", "PROMO_SCHEDULE1"."PLAN_PROMO", "PROMO_SCHEDULE1"."REMARK", "PROMO_SCHEDULE1"."ROW_ID_MT", "PROMO_SCHEDULE1"."ROW_ID_SCH", "PROMO_SCHEDULE1"."UPDATE_USER", "PROMO_SCHEDULE1"."UPDATE_DATE" , mr_promo.promo_name promo_desc, promo_schedule.sch flg, to_char(sysdate,:"SYS_B_0") as kill_date FROM "PROMO_SCHEDULE1", mr_promo, promo_schedule where "PROMO_SCHEDULE1"."PROMO_CODE" = mr_promo.promo_code(+) and promo_schedule1.row_id_sch = promo_schedule.row_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 81 0.23 0.81 2609 1116 14 29508 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 83 0.23 0.81 2609 1116 14 29508 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 29508 MERGE JOIN OUTER 29508 SORT JOIN 29508 MERGE JOIN OUTER 29508 SORT JOIN 29508 TABLE ACCESS FULL PROMO_SCHEDULE1 29508 SORT JOIN 3107 TABLE ACCESS FULL PROMO_SCHEDULE 29462 SORT JOIN 2910 TABLE ACCESS FULL MR_PROMO ******************************************************************************** select config_param from me_config where config_code =:"SYS_B_0" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 7 0.00 0.00 0 21 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 21 0.00 0.00 0 21 0 7 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ME_CONFIG 1 INDEX UNIQUE SCAN ME_CONFIGP (object id 29492) ******************************************************************************** UPDATE PROMO_SCHEDULE SET sch = :"SYS_B_0", update_user = :"SYS_B_1", update_date = TO_DATE(:"SYS_B_2",:"SYS_B_3"), row_id_mt = :"SYS_B_4" WHERE row_id = :"SYS_B_5" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 2 35 0 0 Execute 1 0.78 0.91 2 10803 2865 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.79 0.93 4 10838 2865 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY INDEX ROWID PROMO_SCHEDULE 1 INDEX UNIQUE SCAN SYS_C009503 (object id 39220) ******************************************************************************** SELECT row_id AS row_id_slot, BLOCK_PO_NUMBER FROM tp_cbs_dps1 WHERE channel_code = F_Get_Config( 'CH' ) AND tx_code = :b6 AND cbs_date = :b5 AND (film_poc = :b4 OR trim(:b4) IS NULL) AND (row_id_poc = :b3 OR trim(:b3) IS NULL ) AND slot_ttime BETWEEN NVL( trim( :b2 ), '00000000' ) AND NVL( trim( :b1 ), '40000000' ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 181 0.00 0.02 5 363 0 180 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 183 0.01 0.02 5 363 0 180 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT CONFIG_PARAM from ME_CONFIG where CONFIG_CODE = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 3602 0.09 0.07 0 0 0 0 Fetch 3602 0.00 0.03 0 10806 0 3602 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7205 0.09 0.11 0 10806 0 3602 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 2) ******************************************************************************** SELECT PROMO_SCHEDULE1.promo_code, cb_name, pref_cb_seq, pref_promo_seq, promo_priority, plan_promo, MR_PROMO.HOUSE_NO, MR_PROMO.PROMO_DURATION, MR_PROMO.SCHOBJ_CODE, MR_PROMO.MATERIAL_CODE, MR_PROMO.MACHINE_CODE, NVL( mg_schobj.SCHOBJ_TIME, 'Y' ) AS SCHOBJ_TIME , PROMO_SCHEDULE1.ROW_ID rowdet FROM PROMO_SCHEDULE1, MR_PROMO, mg_schobj WHERE ROW_ID_MT = :b2 AND ROW_ID_SCH = :b1 AND PROMO_SCHEDULE1.promo_code = MR_PROMO.promo_code AND MR_PROMO.SCHOBJ_CODE = mg_schobj.SCHOBJ_CODE(+) ORDER BY decode(nvl(promo_priority,0),-1,nvl(INT_PRIORITY,0),promo_priority) desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 180 0.01 0.00 0 0 0 0 Fetch 1980 2.14 17.44 150352 157500 0 1800 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2161 2.15 17.45 150352 157500 0 1800 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT A.CB_SEQ, NVL( A.CB_DUR_SPOT, '0' ) AS DUR_SPOT FROM TT_DTS A WHERE CHANNEL_CODE = :b8 AND TX_CODE = :b7 AND CBS_DATE = :b6 AND ROW_ID_SLOT = :b5 AND( Subtime( A.CB_DUR_DEFINE, A.CB_DUR_SPOT ) >= :b4 OR NVL( :b3, 'N' ) = 'N' ) AND( cb_name LIKE :b2 OR :b2 IS NULL ) AND (:b1 = 0 OR NOT EXISTS( SELECT 1 FROM tt_dts1 WHERE CHANNEL_CODE = A.CHANNEL_CODE AND TX_CODE = a.tx_code AND CBS_DATE = a.cbs_date AND ROW_ID_SLOT = a.ROW_ID_SLOT AND CB_SEQ = a.cb_seq AND spot_seq = :b1) ) ORDER BY Subtime( A.CB_DUR_DEFINE, A.CB_DUR_SPOT ) DESC, CB_SEQ ASC call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1800 0.06 0.02 0 0 0 0 Fetch 1800 0.25 0.16 0 6580 0 369 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3601 0.31 0.19 0 6580 0 369 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT LPAD(TO_CHAR(SEQ_RID.NEXTVAL),18,'X') FROM DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1800 0.00 0.03 0 0 0 0 Fetch 1800 0.06 0.06 0 5400 90 1800 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3601 0.06 0.10 0 5400 90 1800 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** INSERT INTO tt_dts1 ( CHANNEL_CODE, TX_CODE, CBS_DATE, ROW_ID_SLOT, CB_SEQ, SPOT_SEQ, SPOT_DURATION, SPOT_TYPE, PROMO_PRIORITY, SPOT_CODE, SCHOBJ_CODE, SPOT_OK, SCHOBJ_TIME, ROW_ID_SPOT, HOUSE_NO, MACHINE_CODE, UPDATE_USER, UPDATE_DATE, ROW_ID, INSERT_DATE, SPOT_REMARK ) VALUES ( :b15, :b14, :b13, :b12, :b11, :b10, :b9, 'P', :b8, :b7, :b6, 'Y', :b5, :b1, :b4, :b3, :b2, SYSDATE, :b1, SYSDATE, 'BULK PROMO' ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 1800 0.23 0.34 31 154 13197 369 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1803 0.23 0.34 31 154 13197 369 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** INSERT INTO TR_PRESEN ( CHANNEL_CODE, CBS_DATE, ROW_ID_SLOT, PROMO_PRIORITY, PROMO_CODE, SCHOBJ_CODE, PROMO_DURATION, SCHOBJ_TIME, HOUSE_NO, MACHINE_CODE, PROMO_TX_STATUS, PROMO_TX_TIME_REAL, PROMO_TX_REMARK, UPDATE_USER, UPDATE_DATE, ROW_ID, PROMO_DURATION_S, PROMO_DURATION_F, TX_CODE ) VALUES ( :b14, :b13, :b12, :b11, :b10, :b9, :b2, :b8, :b7, :b6, :b5, NULL, NULL, :b4, SYSDATE, :b3, F_Jam_To_Detik( :b2 ), NULL, :b1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1800 0.42 0.44 51 334 29330 1800 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1801 0.42 0.44 51 334 29330 1800 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT NVL( MAX( SPOT_SEQ ), 0 ) FROM TT_DTS1 A WHERE CHANNEL_CODE = :b5 AND TX_CODE = :b4 AND CBS_DATE = :b3 AND ROW_ID_SLOT = :b2 AND CB_SEQ = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 369 0.00 0.01 0 0 0 0 Fetch 369 0.00 0.00 0 1485 0 369 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 739 0.01 0.02 0 1485 0 369 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** UPDATE TT_DTS set CB_DUR_SPOT = ADDTIME( nvl( CB_DUR_SPOT, '00000000' ), :b6 ) where CHANNEL_CODE = :b5 and TX_CODE = :b4 and CBS_DATE = :b3 and ROW_ID_SLOT = :b2 and CB_SEQ = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 369 0.14 0.07 0 1478 750 369 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 370 0.14 0.07 0 1478 750 369 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 2) ******************************************************************************** UPDATE PROMO_SCHEDULE1 set INT_PRIORITY =:b2 where row_id = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 369 0.01 0.01 0 1108 389 369 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 370 0.01 0.01 0 1108 389 369 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 59 (recursive depth: 1) ******************************************************************************** SELECT nvl ( count ( *) , :"SYS_B_0" ) FROM me_user4 WHERE ( me_user4.user_code =:"SYS_B_1" ) AND ( me_user4.user_win = :"SYS_B_2" ) and ( DATAOBJECT =:"SYS_B_3" ) and ( DATA_COLUMN =:"SYS_B_4" ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 27 0.00 0.01 0 0 0 0 Execute 27 0.00 0.00 0 0 0 0 Fetch 27 0.00 0.00 2 81 0 27 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 81 0.00 0.01 2 81 0 27 Misses in library cache during parse: 27 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 1 INDEX RANGE SCAN SYS_C009711 (object id 47253) ******************************************************************************** update me_user4 set me_user4.user_posx =:"SYS_B_0" , me_user4.user_posy = :"SYS_B_1" , me_user4.user_sizew =:"SYS_B_2" , me_user4.user_sizeh = :"SYS_B_3" WHERE ( me_user4.user_code =:"SYS_B_4" ) AND ( me_user4.user_win =:"SYS_B_5" ) and ( DATAOBJECT =:"SYS_B_6" ) and ( DATA_COLUMN =:"SYS_B_7" ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 27 0.01 0.01 0 0 0 0 Execute 27 0.01 0.02 1 81 30 27 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 54 0.03 0.03 1 81 30 27 Misses in library cache during parse: 27 Optimizer goal: CHOOSE Parsing user id: 59 Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 INDEX RANGE SCAN SYS_C009711 (object id 47253) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 460 0.31 0.31 2 35 0 0 Execute 460 0.82 1.05 18 10934 3005 42 Fetch 1488 18.45 35.56 52592 399599 14 64753 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2408 19.59 36.94 52612 410568 3019 64795 Misses in library cache during parse: 279 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 202 0.04 0.11 20 66 0 0 Execute 14471 1.04 1.11 84 3172 43852 2999 Fetch 12372 2.59 18.89 150851 191718 90 10600 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 27045 3.68 20.12 150955 194956 43942 13599 Misses in library cache during parse: 57 Misses in library cache during execute: 1 488 user SQL statements in session. 179 internal SQL statements in session. 667 SQL statements in session. 0 statements EXPLAINed in this session. ******************************************************************************** Trace file: E:oracleadminorcludumporcl_ora_3212.trc Trace file compatibility: 9.00.01 Sort options: default 2 sessions in tracefile. 488 user SQL statements in trace file. 179 internal SQL statements in trace file. 667 SQL statements in trace file. 105 unique SQL statements in trace file. 49021 lines in trace file. ----------------------------------------------------- The statspack report is as follows----------------- ----------------------------------------------------- STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ ORCL 1061616106 orcl 1 9.2.0.1.0 NO TTMUMADS Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 9 26-Oct-05 10:13:40 12 5.3 End Snap: 11 26-Oct-05 11:04:35 19 8.6 Elapsed: 50.92 (mins) Cache Sizes (end) 16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)~~ Buffer Cache: 96M Std Block Size: 4K Shared Pool Size: 48M Log Buffer: 512K Load Profile 16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)~~ Per Second Per Transaction --------------- --------------- Redo size: 3,391.14 45,840.42 Logical reads: 2,287.44 30,920.91 Block changes: 22.50 304.20 Physical reads: 186.17 2,516.56 Physical writes: 39.54 534.43 User calls: 12.61 170.46 Parses: 4.06 54.86 Hard parses: 0.53 7.10 Sorts: 4.78 64.65 Logons: 0.00 0.06 Executes: 23.28 314.75 Transactions: 0.07 % Blocks changed per Read: 0.98 Recursive Call %: 80.16 Rollback per transaction %: 8.85 Rows per Sort: 442.32 Instance Efficiency Percentages (Target 100%) 16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT) Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Buffer Hit %: 92.94 In-memory Sort %: 99.72 Library Hit %: 98.48 Soft Parse %: 87.05 Execute to Parse %: 82.57 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 45.40 % Non-Parse CPU: 99.10 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 88.25 90.00 % SQL with executions>1: 94.15 72.59 % Memory for SQL w/exec>1: 67.02 97.49 Top 5 Timed Events 16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)16:21, 31 July 2007 (CDT)[[User:Ashishbokey07|Ashishbokey07]] % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 247 62.01 db file scattered read 50,528 74 18.59 db file sequential read 102,911 71 17.79 SQL*Net more data to client 33,590 2 .41 direct path read 1,826 1 .36 ------------------------------------------------------------- �Wait Events for DB: ORCL Instance: orcl Snaps: 9 -11 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file scattered read 50,528 0 74 1 223.6 db file sequential read 102,911 0 71 1 455.4 SQL*Net more data to client 33,590 0 2 0 148.6 direct path read 1,826 0 1 1 8.1 control file parallel write 1,014 0 1 1 4.5 db file parallel write 288 143 0 2 1.3 log file parallel write 2,350 657 0 0 10.4 control file sequential read 421 0 0 1 1.9 buffer busy waits 433 0 0 1 1.9 log file sync 222 0 0 0 1.0 direct path write 178 0 0 0 0.8 SQL*Net break/reset to clien 8 0 0 0 0.0 LGWR wait for redo copy 145 1 0 0 0.6 SQL*Net message from client 35,695 0 15,074 422 157.9 virtual circuit status 102 102 3,052 29925 0.5 wakeup time manager 97 96 2,932 30225 0.4 SQL*Net more data from clien 1,230 0 1 1 5.4 SQL*Net message to client 35,701 0 0 0 158.0 ------------------------------------------------------------- �Background Wait Events for DB: ORCL Instance: orcl Snaps: 9 -11 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- control file parallel write 993 0 1 1 4.4 db file sequential read 947 0 1 1 4.2 db file scattered read 309 0 1 2 1.4 db file parallel write 288 143 0 2 1.3 log file parallel write 2,350 657 0 0 10.4 rdbms ipc reply 5 0 0 3 0.0 LGWR wait for redo copy 145 1 0 0 0.6 buffer busy waits 2 0 0 0 0.0 rdbms ipc message 7,201 5,868 18,688 2595 31.9 smon timer 17 8 2,928 ###### 0.1 ------------------------------------------------------------- �SQL ordered by Gets for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 2,207,977 102 21,646.8 31.6 33.67 39.14 2481220078 SELECT /*+ FIRST_ROWS */ TT_DTS1.CHANNEL_CODE, TT_DTS1.TX_CODE, TT_DTS1.CBS_DATE, TT_DTS1.ROW_ID_SLOT, TT_DTS1.CB_SEQ, TT_DTS1.SPOT_SEQ, TT_DTS1.SPOT_SEQ_ADV, TT_DTS1.SEQ_PREMIUM, TT_DTS1.SPOT_TTIME, 599,715 78 7,688.7 8.6 2.44 6.32 2487643083 Module: TOAD 7.6.0.11 Select round(sum(bytes)/:"SYS_B_0"/:"SYS_B_1", :"SYS_B_2"), sum(extents), sum(bytes) from sys.dba_segments Where segmen t_type in (:"SYS_B_3", :"SYS_B_4", :"SYS_B_5") and segment_name =:tblname and OWNER = :LOWNER 503,781 50 10,075.6 7.2 42.13 42.21 1556374035 Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_ 0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_code =:"SYS _B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and row _id =:"SYS_B_6" 447,835 50 8,956.7 6.4 38.41 45.87 1180104719 Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_ 0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_code =:"SYS _B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and spo t_seq =:"SYS_B_6" 406,291 79 5,142.9 5.8 0.97 1.02 1351854432 Module: TOAD 7.6.0.11 Select A.UNIQUENESS AS "Uniqueness", A.INDEX_NAME AS "Index Name ", A.TABLE_OWNER AS "Table Owner", A.TABLE_NAME AS "Table Name" , A.TABLE_TYPE AS "Table Type", A.STATUS AS "Status", A.TABLESPA CE_NAME AS "Tablespace", A.INITIAL_EXTENT AS "Initial Extent Siz e", A.NEXT_EXTENT AS "Next Extent Size", A.MIN_EXTENTS AS "Minim 233,396 1 233,396.0 3.3 4.23 20.19 2478742100 UPDATE PROMO_SCHEDULE SET sch = :"SYS_B_0", update_user = :"SYS_ B_1", update_date = TO_DATE(:"SYS_B_2",:"SYS_B_3"), row_id_mt = :"SYS_B_4" WHERE row_id = :"SYS_B_5" 201,904 105 1,922.9 2.9 6.77 10.56 583912756 SELECT TP_CBS_DPS1.CHANNEL_CODE, TP_CBS_DPS1.TX_C ODE, TP_CBS_DPS1.CBS_DATE, TP_CBS_DPS1 .SLOT_TTIME, TP_CBS_DPS1.SLOT_DUR_DEFINE, TP_CBS_DPS1.SLOT_DUR_CB, TP_CBS_DPS1.SLOT_DUR_P ACK, TP_CBS_DPS1.CATSLOT_CODE, TP_CBS_ 198,663 4 49,665.8 2.8 1.78 1.78 1320200665 select nvl(TM1.line_nu_res1, TM1.line_nu_mat1) as line_num, TM1.mo_book_date, TM1.row_id_poc, MF.film_poc_ti tle, TCD1.slot_ttime, TM1.mo_book_cb, TM1.mo_book �SQL ordered by Gets for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- _seq, TM1.prod_version, MP1.prod_duration, SRS.ro w_id_po_line, TM1.row_id, TM1.channel_code, TM1.m 177,787 2,802 63.5 2.5 1.17 1.74 1279876093 SELECT TO_CHAR( NVL( LINE_NUM, '' ) ) AS LINE_NO FROM ST _RESERVATION_MATRIX1 WHERE RID_STRESMATRIX IN( SELECT RO W_ID FROM ST_RESERVATION_MATRIX WHERE rid_sr IN( SELECT row_id FROM ST_RESERV 163,280 4 40,820.0 2.3 1.64 3.51 371763378 select nvl(TM1.line_nu_res1, TM1.line_nu_mat1) as line_num, TM1.mo_book_date, TM1.row_id_poc, MF.film_poc_ti tle, TCD1.slot_ttime, TM1.mo_book_cb, TM1.mo_book _seq, TM1.rate_price_home, TM1.mo_disc0_home, TM1 .mo_dpp_home, TM1.prod_version, MP1.prod_duration, 157,500 180 875.0 2.3 2.16 17.45 887576575 SELECT PROMO_SCHEDULE1.promo_code, cb_name, pref_cb_seq, pref_promo_seq, promo_priority, plan_promo, MR_PROMO.HOUSE_NO, MR_PROMO.PROMO_DURATION, MR_PROMO.SCHOBJ_CODE, MR_PROMO. 125,406 15 8,360.4 1.8 11.02 13.91 2853179198 Select Max ( spot_seq ) from tt_dts1 where to_char ( cbs_date , :"SYS_B_0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_cod e =:"SYS_B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" 98,176 105 935.0 1.4 2.22 2.44 3010293517 SELECT "TR_PRESEN"."CHANNEL_CODE", "TR_PRESEN"."C BS_DATE", "TR_PRESEN"."ROW_ID_SLOT", " TR_PRESEN"."PROMO_PRIORITY", "TR_PRESEN"."PROMO_COD E", "TR_PRESEN"."SCHOBJ_CODE", "TR_PRE SEN"."PROMO_DURATION", "TR_PRESEN"."SCHOBJ_TIME", 68,141 18,619 3.7 1.0 1.17 1.22 1728164327 SELECT sum(f_jam_to_detik (tt_dts.CB_DUR_SPOT)) from tt_dts where row_id_slot = :b1 40,120 96 417.9 0.6 0.13 0.97 238087931 select t.schema, t.name, t.flags, q.name from system.aq$_queue_t ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = : ------------------------------------------------------------- �SQL ordered by Reads for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 150,590 1 150,590.0 26.5 4.23 20.19 2478742100 UPDATE PROMO_SCHEDULE SET sch = :"SYS_B_0", update_user = :"SYS_ B_1", update_date = TO_DATE(:"SYS_B_2",:"SYS_B_3"), row_id_mt = :"SYS_B_4" WHERE row_id = :"SYS_B_5" 150,352 180 835.3 26.4 2.16 17.45 887576575 SELECT PROMO_SCHEDULE1.promo_code, cb_name, pref_cb_seq, pref_promo_seq, promo_priority, plan_promo, MR_PROMO.HOUSE_NO, MR_PROMO.PROMO_DURATION, MR_PROMO.SCHOBJ_CODE, MR_PROMO. 47,052 50 941.0 8.3 38.41 45.87 1180104719 Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_ 0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_code =:"SYS _B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and spo t_seq =:"SYS_B_6" 22,183 6 3,697.2 3.9 0.45 13.58 77843863 UPDATE run_master set update_user = :b7, update_dat e = sysdate, last_date = :b6, last_time = :b5, st_time = :b5, ED_TIME = f_detik_t o_jam(f_jam_to_detik(:b5) + f_jam_to_detik(:b4) ) , row_id_slot = :b3 where row_id_epi = :b2 and run_id = 20,865 8 2,608.1 3.7 2.08 6.18 2899295189 SELECT "PROMO_SCHEDULE1"."ROW_ID", "PROMO_SCHEDUL E1"."PROMO_CODE", "PROMO_SCHEDULE1"."CB_NAME", "PROMO_SCHEDULE1"."PREF_CB_SEQ", "PROMO_SC HEDULE1"."PREF_PROMO_SEQ", "PROMO_SCHEDULE1"."PROMO _PRIORITY", "PROMO_SCHEDULE1"."PLAN_PROMO", 13,913 78 178.4 2.4 2.44 6.32 2487643083 Module: TOAD 7.6.0.11 Select round(sum(bytes)/:"SYS_B_0"/:"SYS_B_1", :"SYS_B_2"), sum(extents), sum(bytes) from sys.dba_segments Where segmen t_type in (:"SYS_B_3", :"SYS_B_4", :"SYS_B_5") and segment_name =:tblname and OWNER = :LOWNER 12,386 1 12,386.0 2.2 0.16 3.27 2996504550 SELECT "STOCK_MATERIAL"."FILM_EPI", "STOCK_MATERI AL"."MATERIAL_ID", "STOCK_MATERIAL"."MATERIAL_FORMA T", "STOCK_MATERIAL"."STATUS", "STOCK_ MATERIAL"."MATERIAL_TYPE", "STOCK_MATERIAL "."ROW_ID", "STOCK_MATERIAL"."TXN_ID", 9,518 15 634.5 1.7 11.02 13.91 2853179198 Select Max ( spot_seq ) from tt_dts1 where to_char ( cbs_date , :"SYS_B_0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_cod e =:"SYS_B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" 9,285 102 91.0 1.6 33.67 39.14 2481220078 SELECT /*+ FIRST_ROWS */ TT_DTS1.CHANNEL_CODE, �SQL ordered by Reads for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- TT_DTS1.TX_CODE, TT_DTS1.CBS_DATE, TT_DTS1.ROW_ID_SLOT, TT_DTS1.CB_SEQ, TT_DTS1.SPOT_SEQ, TT_DTS1.SPOT_SEQ_ADV, TT_DTS1.SEQ_PREMIUM, TT_DTS1.SPOT_TTIME, 8,974 2 4,487.0 1.6 1.13 2.34 2330562062 SELECT Distinct "STOCK_MATERIAL"."MATERIAL_ID", " STOCK_MATERIAL"."MATERIAL_FORMAT", "STOCK_MATERIAL" ."STATUS", "STOCK_MATERIAL"."MATERIAL_TYPE", "STOCK_MATERIAL"."COPY_REASON", "STOCK_MATER IAL"."COPY_SOURCE", "STOCK_MATERIAL"."IDEN_FLAG", 7,589 4 1,897.3 1.3 1.64 3.51 371763378 select nvl(TM1.line_nu_res1, TM1.line_nu_mat1) as line_num, TM1.mo_book_date, TM1.row_id_poc, MF.film_poc_ti tle, TCD1.slot_ttime, TM1.mo_book_cb, TM1.mo_book _seq, TM1.rate_price_home, TM1.mo_disc0_home, TM1 .mo_dpp_home, TM1.prod_version, MP1.prod_duration, 6,600 105 62.9 1.2 6.77 10.56 583912756 SELECT TP_CBS_DPS1.CHANNEL_CODE, TP_CBS_DPS1.TX_C ODE, TP_CBS_DPS1.CBS_DATE, TP_CBS_DPS1 .SLOT_TTIME, TP_CBS_DPS1.SLOT_DUR_DEFINE, TP_CBS_DPS1.SLOT_DUR_CB, TP_CBS_DPS1.SLOT_DUR_P ACK, TP_CBS_DPS1.CATSLOT_CODE, TP_CBS_ 3,779 1 3,779.0 0.7 0.81 2.08 3097764003 select tl_suppport.get_hit_count ( :1 , :2 , :3 ) from dual 3,774 1 3,774.0 0.7 0.81 2.06 1932008286 select count( DISTINCT "STOCK_MATERIAL"."MATERIAL_ID"|| "STOCK_MATERIAL"."MATERIAL_FORMAT"|| "STOCK_MATERIAL". "STATUS"|| "STOCK_MATERIAL"."MATERIAL_TYPE"|| "STOCK_MATERIAL"."COPY_REASON"|| "STOCK_MATERIAL"."COPY _SOURCE"|| "STOCK_MATERIAL"."IDEN_FLAG"|| "STO 2,950 2 1,475.0 0.5 0.80 3.54 3376831664 BEGIN statspack.snap; END; 1,867 6 311.2 0.3 0.05 1.27 3015466825 SELECT ROW_ID_EPI FROM stock_material_epi WHERE SCH_FILM _POC = :b3 AND SCH_ROW_ID_POC = :b2 AND SCH_FILM_EPI ------------------------------------------------------------- �SQL ordered by Executions for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 18,619 18,619 1.0 0.00 0.00 1728164327 SELECT sum(f_jam_to_detik (tt_dts.CB_DUR_SPOT)) from tt_dts where row_id_slot = :b1 6,590 6,590 1.0 0.00 0.00 4190354625 SELECT CONFIG_PARAM from ME_CONFIG where CONFIG_CODE = : b1 2,802 28,511 10.2 0.00 0.00 1279876093 SELECT TO_CHAR( NVL( LINE_NUM, '' ) ) AS LINE_NO FROM ST _RESERVATION_MATRIX1 WHERE RID_STRESMATRIX IN( SELECT RO W_ID FROM ST_RESERVATION_MATRIX WHERE rid_sr IN( SELECT row_id FROM ST_RESERV 2,112 2,112 1.0 0.00 0.00 3460529092 select t.name, (select owner_instance from sys.aq$_queue_table_ affinities where table_objno = t.objno) from system.aq$_queue _tables t where t.name = :1 and t.schema = :2 for update skip lo cked 1,800 369 0.2 0.00 0.00 2735724471 SELECT A.CB_SEQ, NVL( A.CB_DUR_SPOT, '0' ) AS DU R_SPOT FROM TT_DTS A WHERE CHANNEL_CODE = :b8 AND TX_CODE = :b7 AND CBS_ DATE = :b6 AND ROW_ID_SLOT = :b5 AND( Subtime( A.CB_DUR_DEFINE, A.CB_DUR_SPOT ) >= :b4 1,800 369 0.2 0.00 0.00 3560871066 INSERT INTO tt_dts1 ( CHANNEL_CODE, TX_CODE, CBS_DATE, ROW_ID_SLOT, CB_SEQ, SPOT_SEQ, SP OT_DURATION, SPOT_TYPE, PROMO_PRIORITY, SPO T_CODE, SCHOBJ_CODE, SPOT_OK, SCHOBJ_TIME, ROW_ID_SPOT, HOUSE_NO, MACHINE_CODE, UPDATE 1,800 1,800 1.0 0.00 0.00 4138120402 INSERT INTO TR_PRESEN ( CHANNEL_CODE, CBS_DAT E, ROW_ID_SLOT, PROMO_PRIORITY, PROMO_CODE, SCHOBJ_CODE, PROMO_DURATION, SCHOBJ_TIME, HOUSE_NO, MACHINE_CODE, PROMO_TX_STATUS, PR OMO_TX_TIME_REAL, PROMO_TX_REMARK, UPDATE_USER, 1,491 1,491 1.0 0.00 0.00 914642680 SELECT NVL(CALC_AVG,'N') FROM ME_PACKAGE WHERE PACK_CODE = :b1 1,047 1,389 1.3 0.01 0.01 213097314 Module: TOAD 7.6.0.11 SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE=T O_NUMBER(:hash) ORDER BY PIECE 956 2,611 2.7 0.01 0.01 2648549866 Module: TOAD 7.6.0.11 select sql_text from V$sqltext_with_newlines where address = (se �SQL ordered by Executions for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- lect prev_sql_addr from V$session where username = :uname a nd sid = :snum) ORDER BY piece 596 0 0.0 0.00 0.00 2963598673 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n ext_date) and (next_date < :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5) ) and (this_date is null) order by next_date, job 596 596 1.0 0.00 0.00 2964743345 select count(*) from sys.job$ where (next_date > sysdate) and (n ext_date < (sysdate+5/86400)) 517 517 1.0 0.00 0.00 2469958056 select config_param from me_config where config_code =:"SYS_B_0" 495 741 1.5 0.00 0.00 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1 380 7,004 18.4 0.00 0.00 31235412 Module: TOAD 7.6.0.11 SELECT S.STATUS "Status", S.SERIAL# "Serial#", S.TYPE "Ty pe", S.USERNAME "DB User", S.OSUSER "Client User", S.SERVE R "Server", S.MACHINE "Machine", S.MODULE "Module", S.CLIE NT_INFO "Client Info", S.TERMINAL "Terminal", S.PROGRAM "Pro gram", P.PROGRAM "O.S. Program", s.logon_time "Connect Time", 377 377 1.0 0.00 0.00 1860511898 UPDATE TT_DTS set CB_DUR_SPOT = ADDTIME( nvl( CB_DUR_SPOT, '00000000' ), :b6 ) wher e CHANNEL_CODE = :b5 and TX_CODE = :b4 and CBS_DATE = :b3 and ROW_ID_SLOT = :b2 and CB_SEQ = :b1 369 369 1.0 0.00 0.00 4083874532 UPDATE PROMO_SCHEDULE1 set INT_PRIORITY =:b2 where row_id = :b1 369 369 1.0 0.00 0.00 4230995440 SELECT NVL( MAX( SPOT_SEQ ), 0 ) FROM TT_DTS1 A WHERE CHANNEL_CODE = :b5 AND TX_CODE = :b4 AND CBS_DATE = :b3 AND ROW_ID_SLOT = :b2 AND CB_SEQ = :b1 307 307 1.0 0.00 0.00 1490567752 SELECT FILM_POC, AIRTIME_FROM, A IRTIME_TO from ST_RESERVATION_MATRIX1 wh ere row_id = :b1 ------------------------------------------------------------- �SQL ordered by Parse Calls for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 1,047 1,047 8.44 213097314 Module: TOAD 7.6.0.11 SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE=T O_NUMBER(:hash) ORDER BY PIECE 956 956 7.71 2648549866 Module: TOAD 7.6.0.11 select sql_text from V$sqltext_with_newlines where address = (se lect prev_sql_addr from V$session where username = :uname a nd sid = :snum) ORDER BY piece 517 517 4.17 2469958056 select config_param from me_config where config_code =:"SYS_B_0" 380 380 3.07 31235412 Module: TOAD 7.6.0.11 SELECT S.STATUS "Status", S.SERIAL# "Serial#", S.TYPE "Ty pe", S.USERNAME "DB User", S.OSUSER "Client User", S.SERVE R "Server", S.MACHINE "Machine", S.MODULE "Module", S.CLIE NT_INFO "Client Info", S.TERMINAL "Terminal", S.PROGRAM "Pro gram", P.PROGRAM "O.S. Program", s.logon_time "Connect Time", 282 282 2.27 1705880752 select file# from file$ where ts#=:1 280 280 2.26 2037944383 select sysdate from dual 239 239 1.93 3521736783 update me_user4 set me_user4.user_posx =:"SYS_B_0" , me_user4.us er_posy =:"SYS_B_1" , me_user4.user_sizew =:"SYS_B_2" , me_user4 .user_sizeh =:"SYS_B_3" WHERE ( me_user4.user_code =:"SYS_B_4" ) AND ( me_user4.user_win =:"SYS_B_5" ) and ( DATAOBJECT =:"SYS_B _6" ) and ( DATA_COLUMN =:"SYS_B_7" ) 239 239 1.93 3767408732 SELECT nvl ( count ( *) , :"SYS_B_0" ) FROM me_user4 WHERE ( me_ user4.user_code =:"SYS_B_1" ) AND ( me_user4.user_win =:"SYS_B_2 " ) and ( DATAOBJECT =:"SYS_B_3" ) and ( DATA_COLUMN =:"SYS_B_4" ) 114 114 0.92 238439246 SELECT count ( *) FROM "TT_DTS" WHERE ( "TT_DTS"."CHANNEL_CODE" =:"SYS_B_0" ) AND ( "TT_DTS"."TX_CODE" =:"SYS_B_1" ) AND ( "TT_D TS"."CBS_DATE" =to_date ( :"SYS_B_2" , :"SYS_B_3" ) ) AND ( "TT_ DTS"."ROW_ID_SLOT" =:"SYS_B_4" ) AND ( "TT_DTS"."CB_SEQ" =to_num ber ( :"SYS_B_5" ) ) 108 108 0.87 4059714361 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user# ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 �SQL ordered by Parse Calls for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 105 105 0.85 583912756 SELECT TP_CBS_DPS1.CHANNEL_CODE, TP_CBS_DPS1.TX_C ODE, TP_CBS_DPS1.CBS_DATE, TP_CBS_DPS1 .SLOT_TTIME, TP_CBS_DPS1.SLOT_DUR_DEFINE, TP_CBS_DPS1.SLOT_DUR_CB, TP_CBS_DPS1.SLOT_DUR_P ACK, TP_CBS_DPS1.CATSLOT_CODE, TP_CBS_ 105 105 0.85 775819658 SELECT "MR_PROMO"."PROMO_CODE", "MR_PROMO"."PROMO_NAME ", "MR_PROMO"."PROMO_HOUSE_NO", "MR_PROMO"."HO USE_NO", "MR_PROMO"."PROMO_DURATION", "MR_PROM O"."PROMO_SOM", "MR_PROMO"."PROMO_EOM", "MR_PR OMO"."PROMO_STD", "MR_PROMO"."SCHOBJ_CODE", "M 105 105 0.85 1855637200 select tx_name from mg_tx where tx_code =:"SYS_B_0" 105 105 0.85 2437786413 SELECT :"SYS_B_0" as spot_seq , "MR_PROMO"."PROMO_CODE", "MR_PROMO"."PROMO_NAME", "MR_PROMO"."P ROMO_HOUSE_NO", "MR_PROMO"."HOUSE_NO", "MR_PROMO"."PROMO_DURATION", "MR_PROMO"."PROMO_SOM ", "MR_PROMO"."PROMO_EOM", "MR_PROMO". 105 105 0.85 2649093900 SELECT "TP_CBS_DPS"."CHANNEL_CODE", "TP_CBS_DPS". "TX_CODE", "TP_CBS_DPS"."CBS_DATE", "T P_CBS_DPS"."DATE_OK" FROM "TP_CBS_DPS" WHERE ( "TP_ CBS_DPS"."CHANNEL_CODE" = :"SYS_B_0" ) AND ( "TP_CBS_ DPS"."CBS_DATE" >= to_date(:"SYS_B_1",:"SYS_B_2") ) AND 105 105 0.85 3010293517 SELECT "TR_PRESEN"."CHANNEL_CODE", "TR_PRESEN"."C BS_DATE", "TR_PRESEN"."ROW_ID_SLOT", " TR_PRESEN"."PROMO_PRIORITY", "TR_PRESEN"."PROMO_COD E", "TR_PRESEN"."SCHOBJ_CODE", "TR_PRE SEN"."PROMO_DURATION", "TR_PRESEN"."SCHOBJ_TIME", 105 105 0.85 3620651011 SELECT TT_DTS.CHANNEL_CODE, TT_DTS.TX_CODE, TT_DTS.CBS_DATE, TT_DTS.ROW_ID_SLOT, TT_DTS.CB_SEQ, TT_DTS.CB_TSTART, TT_DTS.CB_TTIME, TT_DTS.CB_DUR_DEFINE, TT_DTS.CB_DUR_SPOT, TT_DTS.CB_DUR_PACK, 102 102 0.82 578861073 SELECT "TT_DTS"."TX_CODE", "TT_DTS"."CB_SEQ", "TT_DTS"."CB_NAME", "TT_DTS"."CB_TSTART", ------------------------------------------------------------- �SQL ordered by Sharable Memory for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value ---------------- ------------ ------- ------------ 7,007,166 239 10.4 3521736783 update me_user4 set me_user4.user_posx =:"SYS_B_0" , me_user4.us er_posy =:"SYS_B_1" , me_user4.user_sizew =:"SYS_B_2" , me_user4 .user_sizeh =:"SYS_B_3" WHERE ( me_user4.user_code =:"SYS_B_4" ) AND ( me_user4.user_win =:"SYS_B_5" ) and ( DATAOBJECT =:"SYS_B _6" ) and ( DATA_COLUMN =:"SYS_B_7" ) 6,914,432 102 10.3 578861073 SELECT "TT_DTS"."TX_CODE", "TT_DTS"."CB_SEQ", "TT_DTS"."CB_NAME", "TT_DTS"."CB_TSTART", "TT_DTS"."CB_TTIME", "TT_DTS"."CB_DUR_DEFI NE", f_jam_to_detik( "TT_DTS"."CB_DUR_DEFINE") as total_dur, f_jam_to_detik( nvl("TT_DTS"."CB_DUR_SPOT",:"SYS_B_00")) as 6,739,416 239 10.0 3767408732 SELECT nvl ( count ( *) , :"SYS_B_0" ) FROM me_user4 WHERE ( me_ user4.user_code =:"SYS_B_1" ) AND ( me_user4.user_win =:"SYS_B_2 " ) and ( DATAOBJECT =:"SYS_B_3" ) and ( DATA_COLUMN =:"SYS_B_4" ) 6,617,514 60 9.9 930144632 SELECT distinct TP_CBS_DPS1.TX_CODE, MP_CATSLOT.CATSLOT_NAM E, TP_CBS_DPS1.SLOT_TTIME, MP_FILM.FILM_POC_TITLE, TP_CBS_DPS1.FILM_DURATION, TP_CBS_DPS1.FILM_POC, TP_ CBS_DPS1.FILM_EPI, TP_CBS_DPS1.HOUSE_NO, TP_CBS_DPS1.U PDATE_USER, mp_film.FILM_LICEN_RUN as actual_runs, F_R 4,217,840 24 6.3 2225552704 select TD1.TX_CODE, TM.AGEN_CODE, TM.CLIENT_CODE, TM1.PROD_CODE, TM1.PROD_VERSION, TD1.SPOT_DURATI ON, TM1.RATE_CODE, TM1.RATE_PRICE_TRX, TM.UPDATE_ USER, TM.UPDATE_DATE, TD1.HOUSE_NO, nvl( TD1.SCHO BJ_TIME, :"SYS_B_0" ) as SCHOBJ_TIME, TM.MO_NO, TM.MO_ 3,106,856 37 4.6 3520613462 SELECT count ( *) FROM "TT_DTS1" WHERE ( "TT_DTS1"."CHANNEL_CODE " =:"SYS_B_0" ) AND ( "TT_DTS1"."TX_CODE" =:"SYS_B_1" ) AND ( "T T_DTS1"."CBS_DATE" =to_date ( :"SYS_B_2" , :"SYS_B_3" ) ) AND ( "TT_DTS1"."ROW_ID_SLOT" =:"SYS_B_4" ) AND ( "TT_DTS1"."CB_SEQ" = to_number ( :"SYS_B_5" ) ) AND ( "TT_DTS1"."SPOT_SEQ" =:"SYS_B_6 2,947,721 50 4.4 317335297 UPDATE TT_DTS1 SET spot_seq = :"SYS_B_00", update_user = :"SYS_B _01", update_date = TO_DATE(:"SYS_B_02",:"SYS_B_03") WHERE chann el_code = :"SYS_B_04" AND tx_code = :"SYS_B_05" AND cbs_date = T O_DATE(:"SYS_B_06",:"SYS_B_07") AND row_id_slot = :"SYS_B_08" AN D cb_seq = :"SYS_B_09" AND row_id_spot = :"SYS_B_10" AND row_id 2,784,844 50 4.1 1180104719 Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_ 0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_code =:"SYS _B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and spo t_seq =:"SYS_B_6" 2,772,047 50 4.1 1556374035 �SQL ordered by Sharable Memory for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value ---------------- ------------ ------- ------------ Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_ 0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_code =:"SYS _B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and row _id =:"SYS_B_6" 2,643,244 114 3.9 238439246 SELECT count ( *) FROM "TT_DTS" WHERE ( "TT_DTS"."CHANNEL_CODE" =:"SYS_B_0" ) AND ( "TT_DTS"."TX_CODE" =:"SYS_B_1" ) AND ( "TT_D TS"."CBS_DATE" =to_date ( :"SYS_B_2" , :"SYS_B_3" ) ) AND ( "TT_ DTS"."ROW_ID_SLOT" =:"SYS_B_4" ) AND ( "TT_DTS"."CB_SEQ" =to_num ber ( :"SYS_B_5" ) ) 1,536,482 29 2.3 3400484077 DELETE FROM TT_DTS1 WHERE channel_code = :"SYS_B_0" AND tx_code = :"SYS_B_1" AND cbs_date = TO_DATE(:"SYS_B_2",:"SYS_B_3") AND r ow_id_slot = :"SYS_B_4" AND cb_seq = :"SYS_B_5" AND row_id_spot = :"SYS_B_6" AND row_id = :"SYS_B_7" 1,427,360 27 2.1 1114195870 UPDATE TR_PRESEN SET promo_tx_status = :"SYS_B_00" WHERE channel _code = :"SYS_B_01" AND cbs_date = TO_DATE(:"SYS_B_02",:"SYS_B_0 3") AND row_id_slot = :"SYS_B_04" AND promo_priority = -:"SYS_B_ 05" AND promo_code = :"SYS_B_06" AND schobj_code = :"SYS_B_07" A ND promo_duration = :"SYS_B_08" AND schobj_time = :"SYS_B_09" AN ------------------------------------------------------------- �SQL ordered by Version Count for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Version Count Threshold: 20 Version Count Executions Hash Value -------- ------------ ------------ 142 239 3521736783 update me_user4 set me_user4.user_posx =:"SYS_B_0" , me_user4.us er_posy =:"SYS_B_1" , me_user4.user_sizew =:"SYS_B_2" , me_user4 .user_sizeh =:"SYS_B_3" WHERE ( me_user4.user_code =:"SYS_B_4" ) AND ( me_user4.user_win =:"SYS_B_5" ) and ( DATAOBJECT =:"SYS_B _6" ) and ( DATA_COLUMN =:"SYS_B_7" ) 142 239 3767408732 SELECT nvl ( count ( *) , :"SYS_B_0" ) FROM me_user4 WHERE ( me_ user4.user_code =:"SYS_B_1" ) AND ( me_user4.user_win =:"SYS_B_2 " ) and ( DATAOBJECT =:"SYS_B_3" ) and ( DATA_COLUMN =:"SYS_B_4" ) 90 102 578861073 SELECT "TT_DTS"."TX_CODE", "TT_DTS"."CB_SEQ", "TT_DTS"."CB_NAME", "TT_DTS"."CB_TSTART", "TT_DTS"."CB_TTIME", "TT_DTS"."CB_DUR_DEFI NE", f_jam_to_detik( "TT_DTS"."CB_DUR_DEFINE") as total_dur, f_jam_to_detik( nvl("TT_DTS"."CB_DUR_SPOT",:"SYS_B_00")) as 76 37 3520613462 SELECT count ( *) FROM "TT_DTS1" WHERE ( "TT_DTS1"."CHANNEL_CODE " =:"SYS_B_0" ) AND ( "TT_DTS1"."TX_CODE" =:"SYS_B_1" ) AND ( "T T_DTS1"."CBS_DATE" =to_date ( :"SYS_B_2" , :"SYS_B_3" ) ) AND ( "TT_DTS1"."ROW_ID_SLOT" =:"SYS_B_4" ) AND ( "TT_DTS1"."CB_SEQ" = to_number ( :"SYS_B_5" ) ) AND ( "TT_DTS1"."SPOT_SEQ" =:"SYS_B_6 74 24 2225552704 select TD1.TX_CODE, TM.AGEN_CODE, TM.CLIENT_CODE, TM1.PROD_CODE, TM1.PROD_VERSION, TD1.SPOT_DURATI ON, TM1.RATE_CODE, TM1.RATE_PRICE_TRX, TM.UPDATE_ USER, TM.UPDATE_DATE, TD1.HOUSE_NO, nvl( TD1.SCHO BJ_TIME, :"SYS_B_0" ) as SCHOBJ_TIME, TM.MO_NO, TM.MO_ 62 60 930144632 SELECT distinct TP_CBS_DPS1.TX_CODE, MP_CATSLOT.CATSLOT_NAM E, TP_CBS_DPS1.SLOT_TTIME, MP_FILM.FILM_POC_TITLE, TP_CBS_DPS1.FILM_DURATION, TP_CBS_DPS1.FILM_POC, TP_ CBS_DPS1.FILM_EPI, TP_CBS_DPS1.HOUSE_NO, TP_CBS_DPS1.U PDATE_USER, mp_film.FILM_LICEN_RUN as actual_runs, F_R 60 50 1180104719 Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_ 0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_code =:"SYS _B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and spo t_seq =:"SYS_B_6" 59 50 317335297 UPDATE TT_DTS1 SET spot_seq = :"SYS_B_00", update_user = :"SYS_B _01", update_date = TO_DATE(:"SYS_B_02",:"SYS_B_03") WHERE chann el_code = :"SYS_B_04" AND tx_code = :"SYS_B_05" AND cbs_date = T O_DATE(:"SYS_B_06",:"SYS_B_07") AND row_id_slot = :"SYS_B_08" AN D cb_seq = :"SYS_B_09" AND row_id_spot = :"SYS_B_10" AND row_id �SQL ordered by Version Count for DB: ORCL Instance: orcl Snaps: 9 -11 -> End Version Count Threshold: 20 Version Count Executions Hash Value -------- ------------ ------------ 59 50 1556374035 Select spot_seq from tt_dts1 where to_char ( cbs_date , :"SYS_B_ 0" ) =:"SYS_B_1" and tx_code =:"SYS_B_2" and channel_code =:"SYS _B_3" and cb_seq =:"SYS_B_4" and row_id_slot =:"SYS_B_5" and row _id =:"SYS_B_6" 56 114 238439246 SELECT count ( *) FROM "TT_DTS" WHERE ( "TT_DTS"."CHANNEL_CODE" =:"SYS_B_0" ) AND ( "TT_DTS"."TX_CODE" =:"SYS_B_1" ) AND ( "TT_D TS"."CBS_DATE" =to_date ( :"SYS_B_2" , :"SYS_B_3" ) ) AND ( "TT_ DTS"."ROW_ID_SLOT" =:"SYS_B_4" ) AND ( "TT_DTS"."CB_SEQ" =to_num ber ( :"SYS_B_5" ) ) 29 28 2938811454 select count ( *) from me_userg1 , me_user1 where me_user1.userg _code =me_userg1.userg_code and user_code =:"SYS_B_0" and funct_ code in ( :"SYS_B_1" , :"SYS_B_2" , :"SYS_B_3" ) 29 29 3400484077 DELETE FROM TT_DTS1 WHERE channel_code = :"SYS_B_0" AND tx_code = :"SYS_B_1" AND cbs_date = TO_DATE(:"SYS_B_2",:"SYS_B_3") AND r ow_id_slot = :"SYS_B_4" AND cb_seq = :"SYS_B_5" AND row_id_spot = :"SYS_B_6" AND row_id = :"SYS_B_7" 27 27 1114195870 UPDATE TR_PRESEN SET promo_tx_status = :"SYS_B_00" WHERE channel _code = :"SYS_B_01" AND cbs_date = TO_DATE(:"SYS_B_02",:"SYS_B_0 3") AND row_id_slot = :"SYS_B_04" AND promo_priority = -:"SYS_B_ 05" AND promo_code = :"SYS_B_06" AND schobj_code = :"SYS_B_07" A ND promo_duration = :"SYS_B_08" AND schobj_time = :"SYS_B_09" AN 23 7 1441901415 SELECT me_user2.user_posx , me_user2.user_posy , me_user2.user_s izew , me_user2.user_sizeh FROM me_user2 WHERE ( me_user2.user_c ode =:"SYS_B_0" ) AND ( me_user2.user_win =:"SYS_B_1" ) ------------------------------------------------------------- �Instance Activity Stats for DB: ORCL Instance: orcl Snaps: 9 -11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 24,652 8.1 109.1 CPU used when call started 24,650 8.1 109.1 CR blocks created 148 0.1 0.7 DBWR buffers scanned 103,114 33.8 456.3 DBWR checkpoint buffers written 279 0.1 1.2 DBWR checkpoints 0 0.0 0.0 DBWR free buffers found 101,444 33.2 448.9 DBWR lru scans 96 0.0 0.4 DBWR make free requests 120 0.0 0.5 DBWR summed scan depth 103,114 33.8 456.3 DBWR transaction table writes 32 0.0 0.1 DBWR undo block writes 1,247 0.4 5.5 SQL*Net roundtrips to/from client 35,670 11.7 157.8 active txn count during cleanout 406 0.1 1.8 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 3,641 1.2 16.1 branch node splits 8 0.0 0.0 buffer is not pinned count 2,040,086 667.8 9,026.9 buffer is pinned count 2,383,725 780.3 10,547.5 bytes received via SQL*Net from c 7,492,333 2,452.5 33,151.9 bytes sent via SQL*Net to client 91,950,328 30,098.3 406,859.9 calls to get snapshot scn: kcmgss 115,197 37.7 509.7 calls to kcmgas 7,625 2.5 33.7 calls to kcmgcs 176 0.1 0.8 change write time 16 0.0 0.1 cleanout - number of ktugct calls 544 0.2 2.4 cleanouts and rollbacks - consist 2 0.0 0.0 cleanouts only - consistent read 77 0.0 0.3 cluster key scan block gets 127,932 41.9 566.1 cluster key scans 65,257 21.4 288.8 commit cleanout failures: block l 6 0.0 0.0 commit cleanout failures: callbac 6 0.0 0.0 commit cleanouts 8,538 2.8 37.8 commit cleanouts successfully com 8,526 2.8 37.7 commit txn count during cleanout 350 0.1 1.6 consistent changes 4,630 1.5 20.5 consistent gets 6,910,616 2,262.1 30,578.0 consistent gets - examination 969,758 317.4 4,291.0 current blocks converted for CR 0 0.0 0.0 cursor authentications 578 0.2 2.6 data blocks consistent reads - un 4,530 1.5 20.0 db block changes 68,750 22.5 304.2 db block gets 77,511 25.4 343.0 deferred (CURRENT) block cleanout 4,676 1.5 20.7 dirty buffers inspected 332 0.1 1.5 enqueue conversions 119 0.0 0.5 enqueue releases 22,981 7.5 101.7 enqueue requests 22,987 7.5 101.7 enqueue timeouts 0 0.0 0.0 execute count 71,134 23.3 314.8 free buffer inspected 333 0.1 1.5 free buffer requested 497,444 162.8 2,201.1 hot buffers moved to head of LRU 62,784 20.6 277.8 immediate (CR) block cleanout app 79 0.0 0.4 immediate (CURRENT) block cleanou 909 0.3 4.0 �Instance Activity Stats for DB: ORCL Instance: orcl Snaps: 9 -11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ index fast full scans (full) 310 0.1 1.4 index fetch by key 732,779 239.9 3,242.4 index scans kdiixs1 77,897 25.5 344.7 leaf node 90-10 splits 7 0.0 0.0 leaf node splits 217 0.1 1.0 logons cumulative 13 0.0 0.1 messages received 2,633 0.9 11.7 messages sent 2,633 0.9 11.7 no buffer to keep pinned count 7 0.0 0.0 no work - consistent read gets 5,848,508 1,914.4 25,878.4 opened cursors cumulative 7,479 2.5 33.1 parse count (failures) 5 0.0 0.0 parse count (hard) 1,605 0.5 7.1 parse count (total) 12,398 4.1 54.9 parse time cpu 222 0.1 1.0 parse time elapsed 489 0.2 2.2 physical reads 568,743 186.2 2,516.6 physical reads direct 75,085 24.6 332.2 physical writes 120,781 39.5 534.4 physical writes direct 118,486 38.8 524.3 physical writes non checkpoint 120,740 39.5 534.3 pinned buffers inspected 1 0.0 0.0 prefetched blocks 340,271 111.4 1,505.6 process last non-idle time 10,172,728,830 3,329,862.1 ############ recovery array read time 0 0.0 0.0 recovery array reads 0 0.0 0.0 recovery blocks read 0 0.0 0.0 recursive calls 155,680 51.0 688.9 recursive cpu usage 1,099 0.4 4.9 redo blocks written 22,194 7.3 98.2 redo buffer allocation retries 0 0.0 0.0 redo entries 39,932 13.1 176.7 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo size 10,359,936 3,391.1 45,840.4 redo synch time 6 0.0 0.0 redo synch writes 220 0.1 1.0 redo wastage 615,268 201.4 2,722.4 redo write time 58 0.0 0.3 redo writer latching time 1 0.0 0.0 redo writes 2,350 0.8 10.4 rollback changes - undo records a 5 0.0 0.0 rollbacks only - consistent read 529 0.2 2.3 rows fetched via callback 318,830 104.4 1,410.8 session connect time 10,172,728,830 3,329,862.1 ############ session logical reads 6,988,126 2,287.4 30,920.9 session pga memory 3,910,880 1,280.2 17,304.8 session pga memory max 14,730,980 4,821.9 65,181.3 session uga memory 2,309,856 756.1 10,220.6 session uga memory max 11,556,808 3,782.9 51,136.3 shared hash latch upgrades - no w 80,841 26.5 357.7 sorts (disk) 41 0.0 0.2 sorts (memory) 14,571 4.8 64.5 sorts (rows) 6,463,153 2,115.6 28,598.0 summed dirty queue length 529 0.2 2.3 switch current to new buffer 2,118 0.7 9.4 �Instance Activity Stats for DB: ORCL Instance: orcl Snaps: 9 -11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ table fetch by rowid 1,394,807 456.6 6,171.7 table fetch continued row 18,688 6.1 82.7 table scan blocks gotten 745,822 244.1 3,300.1 table scan rows gotten 14,104,819 4,617.0 62,410.7 table scans (long tables) 359 0.1 1.6 table scans (short tables) 10,991 3.6 48.6 transaction rollbacks 5 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 user calls 38,525 12.6 170.5 user commits 206 0.1 0.9 user rollbacks 20 0.0 0.1 workarea executions - multipass 18 0.0 0.1 workarea executions - onepass 54 0.0 0.2 workarea executions - optimal 20,058 6.6 88.8 write clones created in foregroun 1 0.0 0.0 ------------------------------------------------------------- �Tablespace IO Stats for DB: ORCL Instance: orcl Snaps: 9 -11 ->ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ BMS_DATA 143,420 47 0.9 3.3 4,752 2 427 0.9 TEMP 5,733 2 2.3 13.1 5,296 2 0 0.0 SYSTEM 9,845 3 1.3 2.3 396 0 0 0.0 UNDOTBS1 84 0 4.5 1.0 1,281 0 6 0.0 EXAMPLE 11 0 4.5 1.0 0 0 0 0.0 ODM 1 0 0.0 1.0 0 0 0 0.0 ------------------------------------------------------------- �File IO Stats for DB: ORCL Instance: orcl Snaps: 9 -11 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ BMS_DATA E:ORACLEORADATAGEN21BMS_DATA01.DBF 72,380 24 0.9 3.2 2,388 1 202 0.8 E:ORACLEORADATAGEN21BMS_DATA02.DBF 71,040 23 0.9 3.4 2,364 1 225 0.9 EXAMPLE E:ORACLEORADATAORCLEXAMPLE01.DBF 11 0 4.5 1.0 0 0 0 ODM E:ORACLEORADATAORCLODM01.DBF 1 0 0.0 1.0 0 0 0 SYSTEM E:ORACLEORADATAORCLSYSTEM01.DBF 9,845 3 1.3 2.3 354 0 0 E:ORACLEORADATAORCLSYSTEM02.DBF 0 0 42 0 0 TEMP E:ORACLEORADATAORCLTEMP01.DBF 5,733 2 2.3 13.1 5,296 2 0 UNDOTBS1 E:ORACLEORADATAORCLUNDOTBS01.DBF 84 0 4.5 1.0 1,281 0 6 0.0 ------------------------------------------------------------- �Buffer Pool Statistics for DB: ORCL Instance: orcl Snaps: 9 -11 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Write Buffer Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 23,496 95.9 11,958,472 493,259 2,295 0 0 433 ------------------------------------------------------------- Instance Recovery Stats for DB: ORCL Instance: orcl Snaps: 9 -11 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- ---------- ---------- ---------- ---------- ---------- B 0 0 20359 19881 184320 19881 E 0 0 17519 18118 184320 18118 ------------------------------------------------------------- Buffer Pool Advisory for DB: ORCL Instance: orcl End Snap: 11 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 8 .1 1,958 4.36 88,383,485 D 16 .2 3,916 3.51 71,136,162 D 24 .3 5,874 3.45 69,993,482 D 32 .3 7,832 3.44 69,766,023 D 40 .4 9,790 3.24 65,789,495 D 48 .5 11,748 1.80 36,498,200 D 56 .6 13,706 1.53 31,145,428 D 64 .7 15,664 1.43 29,043,378 D 72 .8 17,622 1.36 27,549,287 D 80 .8 19,580 1.23 24,998,339 D 88 .9 21,538 1.13 23,009,733 D 96 1.0 23,496 1.00 20,291,468 D 104 1.1 25,454 0.96 19,541,761 D 112 1.2 27,412 0.89 18,023,444 D 120 1.3 29,370 0.71 14,427,018 D 128 1.3 31,328 0.46 9,320,831 D 136 1.4 33,286 0.39 7,930,471 D 144 1.5 35,244 0.35 7,173,900 D 152 1.6 37,202 0.30 6,101,733 D 160 1.7 39,160 0.19 3,765,261 ------------------------------------------------------------- Buffer wait Statistics for DB: ORCL Instance: orcl Snaps: 9 -11 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- data block 427 0 1 undo header 6 0 0 ------------------------------------------------------------- �PGA Aggr Target Stats for DB: ORCL Instance: orcl Snaps: 9 -11 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 66.2 1,345 687 Warning: pga_aggregate_target was set too low for current workload, as this value was exceeded during this interval. Use the PGA Advisory view to help identify a different value for pga_aggregate_target. %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 16 4 26.4 0.0 .0 .0 .0 819 E 16 4 30.4 0.0 .0 .0 .0 819 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: ORCL Instance: orcl Snaps: 9 -11 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 4K 8K 15,258 15,258 0 0 8K 16K 612 612 0 0 16K 32K 62 62 0 0 32K 64K 1,564 1,564 0 0 64K 128K 938 938 0 0 128K 256K 954 954 0 0 256K 512K 606 606 0 0 512K 1024K 58 58 0 0 1M 2M 18 0 18 0 2M 4M 36 0 18 18 4M 8M 10 4 6 0 8M 16M 4 0 4 0 16M 32M 4 0 4 0 32M 64M 6 2 4 0 ------------------------------------------------------------- PGA Memory Advisory for DB: ORCL Instance: orcl End Snap: 11 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 12 0.8 20,008.4 7,949.0 72.0 478 16 1.0 20,008.4 3,038.5 87.0 478 19 1.2 20,008.4 3,038.5 87.0 461 22 1.4 20,008.4 2,864.0 87.0 288 26 1.6 20,008.4 2,461.7 89.0 7 29 1.8 20,008.4 2,260.8 90.0 0 32 2.0 20,008.4 2,143.3 90.0 0 48 3.0 20,008.4 1,415.4 93.0 0 64 4.0 20,008.4 1,200.8 94.0 0 96 6.0 20,008.4 1,088.4 95.0 0 128 8.0 20,008.4 1,066.1 95.0 0 ------------------------------------------------------------- �Rollback Segment Stats for DB: ORCL Instance: orcl Snaps: 9 -11 ->A high value for "Pct Waits" suggests more rollback segments may be required ->RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- ------- --------------- -------- -------- -------- 0 20.0 0.00 0 0 0 0 1 5,429.0 0.00 2,102,392 4 0 2 2 969.0 0.41 74,764 2 1 1 3 1,319.0 0.00 99,190 0 0 0 4 1,545.0 0.06 127,308 1 1 1 5 414.0 0.00 37,518 0 0 0 6 1,511.0 0.00 313,426 0 0 0 7 1,182.0 0.00 86,574 0 1 0 8 1,481.0 0.00 277,390 1 0 0 9 1,695.0 0.00 115,664 0 0 0 10 1,771.0 0.00 211,758 0 0 0 ------------------------------------------------------------- �Rollback Segment Storage for DB: ORCL Instance: orcl Snaps: 9 -11 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 389,120 0 389,120 1 3,272,704 937,346 4,321,280 2 258,048 159,644 4,190,208 3 2,224,128 548,335 4,321,280 4 1,175,552 380,580 3,272,704 5 1,175,552 479,631 3,272,704 6 1,175,552 910,845 13,758,464 7 1,175,552 449,281 4,321,280 8 1,175,552 1,002,268 9,564,160 9 2,224,128 467,717 4,321,280 10 2,224,128 445,086 3,272,704 ------------------------------------------------------------- �Undo Segment Summary for DB: ORCL Instance: orcl Snaps: 9 -11 -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/ TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU ---- -------------- ---------- -------- ---------- -------- ------ ------------- 1 1,058 710,282 20 3 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: ORCL Instance: orcl Snaps: 9 -11 -> ordered by Time desc Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/ End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU ------------ ------------ -------- ------- -------- ------- ------ ------------- 26-Oct 11:00 718 144,273 20 3 0 0 0/0/0/0/0/0 26-Oct 10:50 112 143,045 7 1 0 0 0/0/0/0/0/0 26-Oct 10:40 65 142,091 7 3 0 0 0/0/0/0/0/0 26-Oct 10:30 45 140,906 4 1 0 0 0/0/0/0/0/0 26-Oct 10:20 118 139,967 3 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- �Latch Activity for DB: ORCL Instance: orcl Snaps: 9 -11 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ Consistent RBA 2,349 0.0 0 0 FIB s.o chain latch 12 0.0 0 0 FOB s.o list latch 5,527 0.0 0 0 SQL memory manager latch 2 0.0 0 993 0.0 SQL memory manager worka 69,417 0.0 0 0 active checkpoint queue 1,308 0.0 0 0 archive control 100 0.0 0 0 archive process latch 60 0.0 0 0 begin backup scn array 4,142 0.0 0 0 cache buffer handles 5,896 0.0 0 0 cache buffers chains 13,751,101 0.0 0.0 0 845,159 0.0 cache buffers lru chain 261,038 0.0 0.0 0 541,682 0.0 channel handle pool latc 31 0.0 0 0 channel operations paren 2,096 0.0 0 0 checkpoint queue latch 120,788 0.0 0 2,235 0.0 child cursor hash table 25,578 0.0 0 0 commit callback allocati 14 0.0 0 0 dictionary lookup 10 0.0 0 0 dml lock allocation 20,267 0.0 0 0 dummy allocation 19 0.0 0 0 enqueue hash chains 46,085 0.0 0 0 enqueues 15,431 0.0 0.0 0 0 event group latch 12 0.0 0 0 file number translation 23,549 0.0 0 0 hash table column usage 232 0.0 0 224,900 0.0 job_queue_processes para 50 0.0 0 0 ktm global data 21 0.0 0 0 kwqit: protect wakeup ti 97 0.0 0 0 lgwr LWN SCN 3,026 0.1 0.0 0 0 library cache 9,048,327 0.0 0.0 0 12,819 0.1 library cache load lock 1,314 0.0 0 0 library cache pin 342,523 0.0 0 0 library cache pin alloca 128,551 0.0 0 0 list of block allocation 48 0.0 0 0 loader state object free 174 0.0 0 0 longop free list parent 2 0.0 0 2 0.0 message pool operations 14 0.0 0 0 messages 17,619 0.1 0.0 0 0 mostly latch-free SCN 3,049 0.3 0.0 0 0 multiblock read objects 114,873 0.0 0 0 ncodef allocation latch 49 0.0 0 0 object stats modificatio 81 0.0 0 0 post/wait queue 4,700 0.0 0 220 0.0 process allocation 12 0.0 0 12 0.0 process group creation 17 0.0 0 0 redo allocation 45,314 0.1 0.0 0 0 redo copy 0 0 40,094 0.4 redo writing 10,321 0.0 0 0 row cache enqueue latch 188,179 0.0 0.0 0 0 row cache objects 209,907 0.0 0 3,085 0.0 �Latch Activity for DB: ORCL Instance: orcl Snaps: 9 -11 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ sequence cache 5,628 0.0 0 0 session allocation 12,116 0.0 0 0 session idle bit 77,604 0.0 0 0 session switching 54 0.0 0 0 session timer 1,028 0.0 0 0 shared pool 325,300 0.0 0.0 0 0 simulator hash latch 460,420 0.0 0 0 simulator lru latch 37,930 0.0 0.0 0 2,829 0.0 sort extent pool 668 0.0 0 0 temporary table state ob 3 0.0 0 0 transaction allocation 40 0.0 0 0 transaction branch alloc 49 0.0 0 0 undo global data 22,498 0.0 0 0 user lock 26 0.0 0 0 ------------------------------------------------------------- �Dictionary Cache Stats for DB: ORCL Instance: orcl Snaps: 9 -11 ->"Pct Misses" should be very low (< 2% in most cases) ->"Cache Usage" is the number of cache entries being used ->"Pct SGA" is the ratio of usage to allocated size for that cache Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_global_oids 4 0.0 0 0 62 dc_histogram_data 8,756 0.0 0 0 458 dc_histogram_data_values 11,285 0.0 0 0 316 dc_histogram_defs 21,407 0.4 0 0 2,087 dc_object_ids 46,358 0.3 0 1 846 dc_objects 4,303 3.4 0 23 898 dc_profiles 9 0.0 0 0 1 dc_rollback_segments 759 0.0 0 0 22 dc_segments 16,287 0.7 0 44 1,037 dc_sequences 93 1.1 0 93 3 dc_tablespace_quotas 14 7.1 0 14 1 dc_tablespaces 1,299 0.6 0 0 12 dc_user_grants 471 0.0 0 0 16 dc_usernames 871 0.3 0 0 11 dc_users 2,709 0.0 0 0 19 ------------------------------------------------------------- Library Cache Activity for DB: ORCL Instance: orcl Snaps: 9 -11 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 125 4.0 126 5.6 2 0 CLUSTER 537 0.0 537 0.0 0 0 INDEX 700 0.9 700 1.7 0 0 SQL AREA 9,434 1.6 97,767 2.1 306 23 TABLE/PROCEDURE 43,542 0.3 77,962 0.8 228 0 TRIGGER 600 2.2 600 4.2 12 0 ------------------------------------------------------------- �Shared Pool Advisory for DB: ORCL Instance: orcl End Snap: 11 -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Estd Shared Pool SP Estd Estd Estd Lib LC Time Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits ----------- ----- ---------- ------------ ------------ ------- --------------- 24 .5 25 3,455 9,457 1.0 1,531,673 32 .7 32 4,615 9,460 1.0 1,533,123 40 .8 40 6,825 9,461 1.0 1,533,459 48 1.0 47 9,743 9,461 1.0 1,533,663 56 1.2 53 11,116 9,464 1.0 1,534,188 64 1.3 60 12,402 9,465 1.0 1,534,981 72 1.5 67 14,690 9,470 1.0 1,536,283 80 1.7 74 16,030 9,470 1.0 1,536,548 88 1.8 81 17,324 9,470 1.0 1,536,583 96 2.0 94 19,805 9,470 1.0 1,536,583 ------------------------------------------------------------- �SGA Memory Summary for DB: ORCL Instance: orcl Snaps: 9 -11 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 100,663,296 Fixed Size 453,352 Redo Buffers 667,648 Variable Size 109,051,904 ---------------- sum 210,836,200 ------------------------------------------------------------- SGA breakdown difference for DB: ORCL Instance: orcl Snaps: 9 -11 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 33,554,432 33,554,432 0.00 large free memory 8,388,608 8,388,608 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared FileOpenBlock 695,504 695,504 0.00 shared KGK heap 3,756 3,756 0.00 shared KGLS heap 8,243,568 2,002,156 -75.71 shared KQR L PO 1,024 1,024 0.00 shared KQR L SO 323,680 323,680 0.00 shared KQR M PO 5,783,748 3,906,712 -32.45 shared KQR M SO 237,724 237,724 0.00 shared KQR S PO 456,976 379,664 -16.92 shared KQR S SO 4,352 4,352 0.00 shared KSXR pending messages que 841,036 841,036 0.00 shared KSXR receive buffers 1,033,000 1,033,000 0.00 shared PL/SQL DIANA 1,300,316 682,888 -47.48 shared PL/SQL MPCODE 1,778,188 701,364 -60.56 shared PL/SQL PPCODE 328,828 153,928 -53.19 shared PLS non-lib hp 2,068 2,068 0.00 shared dictionary cache 1,610,880 1,610,880 0.00 shared errors 155,776 63,612 -59.16 shared event statistics per sess 1,718,360 1,718,360 0.00 shared fixed allocation callback 260 260 0.00 shared free memory 7,886,056 6,707,868 -14.94 shared joxs heap init 4,220 4,220 0.00 shared kgl simulator 2,067,364 2,236,376 8.18 shared library cache 10,413,736 13,797,280 32.49 shared message pool freequeue 834,752 834,752 0.00 shared miscellaneous 7,979,972 8,134,520 1.94 shared parameters 28,192 29,232 3.69 shared sim memory hea 97,412 97,412 0.00 shared sql area 5,813,452 18,728,152 222.15 shared subheap 46,580 46,580 0.00 shared table definiti 924 3,200 246.32 shared trigger defini 4,512 26,992 498.23 shared trigger inform 1,076 1,076 0.00 shared trigger source 1,248 100 -91.99 buffer_cache 100,663,296 100,663,296 0.00 fixed_sga 453,352 453,352 0.00 log_buffer 656,384 656,384 0.00 ------------------------------------------------------------- �init.ora Parameters for DB: ORCL Instance: orcl Snaps: 9 -11 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 background_dump_dest e:oracleadminorclbdump compatible 9.2.0.0.0 control_files e:oracleoradataorclCONTROL01. core_dump_dest e:oracleadminorclcdump cursor_sharing SIMILAR db_block_size 4096 db_cache_size 100663296 db_domain db_file_multiblock_read_count 8 db_name orcl dispatchers (PROTOCOL=TCP) (SERVICE=orclXDB) fast_start_mttr_target 0 global_names FALSE hash_join_enabled FALSE instance_name orcl java_pool_size 33554432 job_queue_processes 10 large_pool_size 8388608 log_archive_dest G:Oracle_archive log_archive_start TRUE open_cursors 300 optimizer_mode CHOOSE pga_aggregate_target 16777216 processes 150 query_rewrite_enabled FALSE remote_login_passwordfile EXCLUSIVE shared_pool_size 50331648 sort_area_size 1024288 star_transformation_enabled FALSE timed_statistics TRUE undo_management AUTO undo_retention 900 undo_tablespace UNDOTBS1 user_dump_dest e:oracleadminorcludump ------------------------------------------------------------- End of Report --------------------------------------------------------- Thanks & Regards, Ashish Bokey

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,
When optimizer values are changed for the instance and tables are analyzed, it takes the server sometime to settle down. Esp. if the tables are being analyzed after a long time. The parser has to generate new explain plan which may or may not be the best plan (depending on the options used with the analyze command….using for all indexed cols helps!!!). Using stored outlines would be a good idea before making changes to the optimizer values.
I would say let your settle down a bit…give it a day or so and the performance will start improving.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bozzo999
    Identify the slow processes you are most concerned with. Trace (with binds and waits) the process. Start with tkprof against the trace file to see if you can find the problem SQL (e.g. longest elapsed time) Tune that SQL first Repeat If nothing jumps out in the tkprof output take a look at the trace analyzer that comes with Oracle. It will help identify excessive wait time. HTH Ken F.
    0 pointsBadges:
    report
  • Bozzo999
    Identify the slow processes you are most concerned with. Trace (with binds and waits) the process. Start with tkprof against the trace file to see if you can find the problem SQL (e.g. longest elapsed time) Tune that SQL first Repeat If nothing jumps out in the tkprof output take a look at the trace analyzer that comes with Oracle. It will help identify excessive wait time. HTH Ken F.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following