script：查看redo产生的历史信息

SQL> alter session set nls_date_format='dd-mon-yy';
SQL>  set lines 160 pages 1000 echo off feedback off
SQL>  col stat_name for a25
SQL>  col date_time for a40
SQL>  col BEGIN_INTERVAL_TIME for a20
SQL>  col END_INTERVAL_TIME for a20
SQL>  prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
"Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
SQL>
SQL>  WITH sysstat AS
  2   (select sn.begin_interval_time begin_interval_time,
  3            sn.end_interval_time end_interval_time,
  4            ss.stat_name stat_name,
  5            ss.value e_value,
  6            lag(ss.value, 1) over(order by ss.snap_id) b_value
  7       from dba_hist_sysstat ss, dba_hist_snapshot sn
  8      where trunc(sn.begin_interval_time) > sysdate-10
  9        and ss.snap_id = sn.snap_id
 10        and ss.dbid = sn.dbid
 11        and ss.instance_number = sn.instance_number
 12        and ss.dbid = (select dbid from v$database)
 13        and ss.instance_number = 1
 14        and ss.stat_name = 'redo size')
 15   select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
 16   stat_name,
 17   round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
 18   + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
 19   + extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
 20   from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
 21  /

DATE_TIME                                STAT_NAME                    PER_SEC
---------------------------------------- ------------------------- ----------
08/28/13_09_00_10_00                     redo size                        473
08/28/13_10_00_11_00                     redo size                        422
08/28/13_11_00_12_00                     redo size                     &