script：查看redo产生的历史信息
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 426 08/28/13_12_00_13_00 redo size 474 08/28/13_13_00_14_00 redo size 513 08/28/13_14_00_15_00 redo size 529 08/28/13_15_00_16_00 redo size 544 08/28/13_16_00_17_00 redo size 416 08/28/13_17_00_18_00 redo size 408 08/28/13_18_00_19_00 redo size 423 08/28/13_19_00_20_00 redo size 428 08/28/13_20_00_21_00 redo size 448 08/28/13_21_00_22_00 redo size 501 08/28/13_22_00_23_00 redo size 456 08/28/13_23_00_00_00 redo size 478 08/29/13_00_00_01_00 redo size 454 08/29/13_01_00_02_00 redo size 480 08/29/13_02_00_03_00 redo size 538 08/29/13_03_00_04_00 redo size 489 08/29/13_04_00_05_00 redo size 512 08/29/13_05_00_06_00 redo size 541 08/29/13_06_00_07_00 redo size 526 08/29/13_07_00_08_00 redo size 559 08/29/13_08_00_09_00 redo size 15168 08/29/13_09_00_10_00 redo size 573 08/29/13_10_00_11_00 redo size 434 08/29/13_11_00_12_00 redo size 441 08/29/13_12_00_13_00 redo size 435 08/29/13_13_00_14_00 redo size 443 08/29/13_14_00_15_00 redo size 430 08/29/13_15_00_16_00 redo size 448 08/29/13_16_00_17_00 redo size 431 08/29/13_17_00_18_00 redo size 413 08/29/13_18_00_19_00 redo size 428 08/29/13_19_00_20_00 redo size 506 08/29/13_20_00_21_00 redo size 460 08/29/13_21_00_22_00 redo size 506 08/29/13_22_00_23_00 redo size 10098 08/29/13_23_00_00_00 redo size 588 08/30/13_00_00_01_00 redo size 453 08/30/13_01_00_02_00 redo size 562 08/30/13_02_00_03_00