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
ASKED:
Oct 31 2005 2:49 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0
0 pts. | Nov 3 2005 9:53AM GMT
