Thursday, September 30, 2010

Gather AWR Information about estimated MTTR time

set linesize 200
set pagesize 200
column name format a30
column parameter format a30
column value format a30
column size format 999,999,999,999,999

select a.name, b.* from v$database a, (select to_char(completion_time,'YYYY/MM/DD:HH24') dat, sum(blocks*1024) "size"
from v$archived_log
group by to_char(completion_time,'YYYY/MM/DD:HH24') ) b
order by 2;

select b.name, a.name parameter, a.value
from v$parameter a, v$database b
where a.name in ('fast_start_mttr_target','statistics_level');

SELECT c.name,a.instance_number, to_char(begin_interval_time,'YYYY/MM/DD:HH24:MI:SS') dat, TARGET_MTTR, ESTIMATED_MTTR
FROM DBA_HIST_INSTANCE_RECOVERY a, dba_hist_snapshot b, v$database c
where a.snap_id = b.snap_id
and a.instance_number = b.instance_number;