select
r.id, r.name
, r.dbid, r.dbname, r.dbversion
, (case when rs.parallel > 0 then 'YES' else 'NO' end)
, r.directory
, r.capture_id
, r.status
, r.prepare_time, r.start_time, r.end_time
, round((r.end_time - r.start_time) * 86400)
, r.num_clients
, r.num_clients_done
, r.filter_set_name
, r.default_action
, decode(r.synchronization, 1, 'SCN', 2, 'OBJECT_ID', 'FALSE')
, r.connect_time_scale
, r.think_time_scale
, decode(r.think_time_auto_correct, 1, 'TRUE', 'FALSE')
, r.scale_up_multiplier
, rs.user_calls, rs.dbtime, rs.network_time, rs.think_time, rs.time_paused
, (rs.time_gain - rs.time_loss)
, r.awr_dbid, r.awr_begin_snap, r.awr_end_snap
, decode(r.awr_exported, 1, 'YES', 0, 'NO', 'NOT POSSIBLE')
, r.error_code, r.error_msg
, r.dir_path
, r.replay_dir_number
, r.sqlset_owner
, r.sqlset_name
, r.schedule_name
, r.divergence_load_status
from
wrr$_replays r
, (select id,
sum(decode(parallel,'YES',1,0)) as parallel,
sum(user_calls) as user_calls,
sum(dbtime) as dbtime,
sum(network_time) as network_time,
sum(think_time) as think_time,
sum(time_gain) as time_gain,
sum(time_loss) as time_loss,
sum(time_paused) as time_paused
from wrr$_replay_stats
group by id) rs
where r.id = rs.id(+)
and nvl(r.replay_type, 'DB') = 'DB'
SELECT
R.ID
, R.NAME
, R.DBID
, R.DBNAME
, R.DBVERSION
, (CASE WHEN RS.PARALLEL > 0 THEN 'YES' ELSE 'NO' END)
, R.DIRECTORY
, R.CAPTURE_ID
, R.STATUS
, R.PREPARE_TIME
, R.START_TIME
, R.END_TIME
, ROUND((R.END_TIME - R.START_TIME) * 86400)
, R.NUM_CLIENTS
, R.NUM_CLIENTS_DONE
, R.FILTER_SET_NAME
, R.DEFAULT_ACTION
, DECODE(R.SYNCHRONIZATION
, 1
, 'SCN'
, 2
, 'OBJECT_ID'
, 'FALSE')
, R.CONNECT_TIME_SCALE
, R.THINK_TIME_SCALE
, DECODE(R.THINK_TIME_AUTO_CORRECT
, 1
, 'TRUE'
, 'FALSE')
, R.SCALE_UP_MULTIPLIER
, RS.USER_CALLS
, RS.DBTIME
, RS.NETWORK_TIME
, RS.THINK_TIME
, RS.TIME_PAUSED
, (RS.TIME_GAIN - RS.TIME_LOSS)
, R.AWR_DBID
, R.AWR_BEGIN_SNAP
, R.AWR_END_SNAP
, DECODE(R.AWR_EXPORTED
, 1
, 'YES'
, 0
, 'NO'
, 'NOT POSSIBLE')
, R.ERROR_CODE
, R.ERROR_MSG
, R.DIR_PATH
, R.REPLAY_DIR_NUMBER
, R.SQLSET_OWNER
, R.SQLSET_NAME
, R.SCHEDULE_NAME
, R.DIVERGENCE_LOAD_STATUS
FROM
WRR$_REPLAYS R
, (SELECT ID
,
SUM(DECODE(PARALLEL
, 'YES'
, 1
, 0)) AS PARALLEL
,
SUM(USER_CALLS) AS USER_CALLS
,
SUM(DBTIME) AS DBTIME
,
SUM(NETWORK_TIME) AS NETWORK_TIME
,
SUM(THINK_TIME) AS THINK_TIME
,
SUM(TIME_GAIN) AS TIME_GAIN
,
SUM(TIME_LOSS) AS TIME_LOSS
,
SUM(TIME_PAUSED) AS TIME_PAUSED
FROM WRR$_REPLAY_STATS
GROUP BY ID) RS
WHERE R.ID = RS.ID(+)
AND NVL(R.REPLAY_TYPE
, 'DB') = 'DB'
|
|
|