script：查看redo产生的历史信息
script：查看redo产生的历史信息
SQL> alter session set nls_date_format='dd-mon-yy';SQL> set lines 160 pages 1000 echo off feedback offSQL> col stat_name for a25SQL> col date_time for a40SQL> col BEGIN_INTERVAL_TIME for a20SQL> col END_INTERVAL_TIME for a20SQL> 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 47308/28/13_10_00_11_00 redo size 42208/28/13_11_00_12_00 redo size 42608/28/13_12_00_13_00 redo size 47408/28/13_13_00_14_00 redo size 51308/28/13_14_00_15_00 redo size 52908/28/13_15_00_16_00 redo size 54408/28/13_16_00_17_00 redo size 41608/28/13_17_00_18_00 redo size 40808/28/13_18_00_19_00 redo size 42308/28/13_19_00_20_00 redo size 42808/28/13_20_00_21_00 redo size 44808/28/13_21_00_22_00 redo size 50108/28/13_22_00_23_00 redo size 45608/28/13_23_00_00_00 redo size 47808/29/13_00_00_01_00 redo size 45408/29/13_01_00_02_00 redo size 48008/29/13_02_00_03_00 redo size 53808/29/13_03_00_04_00 redo size 48908/29/13_04_00_05_00 redo size 51208/29/13_05_00_06_00 redo size 54108/29/13_06_00_07_00 redo size 52608/29/13_07_00_08_00 redo size 55908/29/13_08_00_09_00 redo size 1516808/29/13_09_00_10_00 redo size 57308/29/13_10_00_11_00 redo size 43408/29/13_11_00_12_00 redo size 44108/29/13_12_00_13_00 redo size 43508/29/13_13_00_14_00 redo size 44308/29/13_14_00_15_00 redo size 43008/29/13_15_00_16_00 redo size 44808/29/13_16_00_17_00 redo size 43108/29/13_17_00_18_00 redo size 41308/29/13_18_00_19_00 redo size 42808/29/13_19_00_20_00 redo size 50608/29/13_20_00_21_00 redo size 46008/29/13_21_00_22_00 redo size 50608/29/13_22_00_23_00 redo size 1009808/29/13_23_00_00_00 redo size 58808/30/13_00_00_01_00 redo size 45308/30/13_01_00_02_00 redo size 56208/30/13_02_00_03_00 redo size 54908/30/13_03_00_04_00 redo size 47608/30/13_04_00_05_00 redo size 47108/30/13_05_00_06_00 redo size 51208/30/13_06_00_07_00 redo size 54808/30/13_07_00_08_00 redo size 58508/30/13_08_00_09_00 redo size 1575208/30/13_09_00_10_00 redo size 54108/30/13_10_00_11_00 redo size 45708/30/13_11_00_12_00 redo size 44308/30/13_12_00_13_00 redo size 45608/30/13_13_00_14_00 redo size 47308/30/13_14_00_15_00 redo size 50308/30/13_15_00_16_00 redo size 46008/30/13_16_00_17_00 redo size 49108/30/13_17_00_18_00 redo size 59208/30/13_18_00_19_00 redo size 53608/30/13_19_00_20_00 redo size 53308/30/13_20_00_21_00 redo size 55108/30/13_21_00_22_00 redo size 86908/30/13_22_00_23_00 redo size 989508/30/13_23_00_00_00 redo size 54308/31/13_00_00_01_00 redo size 43208/31/13_01_00_02_00 redo size 46008/31/13_02_00_03_00 redo size 44508/31/13_03_00_04_00 redo size 44408/31/13_04_00_05_00 redo size 50908/31/13_05_00_06_00 redo size 52008/31/13_06_00_07_00 redo size 56608/31/13_07_00_08_00 redo size 50808/31/13_08_00_09_00 redo size 1494708/31/13_09_00_10_00 redo size 44908/31/13_10_00_11_00 redo size 632208/31/13_11_00_12_00 redo size 44408/31/13_12_00_13_00 redo size 46308/31/13_13_00_14_00 redo size 47808/31/13_14_00_15_00 redo size 101908/31/13_15_00_16_00 redo size 46108/31/13_16_00_17_00 redo size 48108/31/13_17_00_18_00 redo size 44008/31/13_18_00_19_00 redo size 185108/31/13_19_00_20_00 redo size 56208/31/13_20_00_21_00 redo size 44208/31/13_21_00_22_00 redo size 42308/31/13_22_00_23_00 redo size 364908/31/13_23_00_00_00 redo size 57309/01/13_00_00_01_00 redo size 50009/01/13_01_00_02_00 redo size 46209/01/13_02_00_03_00 redo size 44709/01/13_03_00_04_00 redo size 46409/01/13_04_00_05_00 redo size 47309/01/13_05_00_06_00 redo size 105809/01/13_06_00_07_00 redo size 192909/01/13_07_00_08_00 redo size 47009/01/13_08_00_09_00 redo size 1561609/01/13_09_00_10_00 redo size 49609/01/13_10_00_11_00 redo size 52409/01/13_11_00_12_00 redo size 57609/01/13_12_00_13_00 redo size 51709/01/13_13_00_14_00 redo size 53309/01/13_14_00_15_00 redo size 51609/01/13_15_00_16_00 redo size 43609/01/13_16_00_17_00 redo size 51809/01/13_17_00_18_00 redo size 45009/01/13_18_00_19_00 redo size 47609/01/13_19_00_20_00 redo size 47309/01/13_20_00_21_00 redo size 51009/01/13_21_00_22_00 redo size 46909/01/13_22_00_23_00 redo size 46509/01/13_23_00_00_00 redo size 54809/02/13_00_00_01_00 redo size 53109/02/13_01_00_02_00 redo size 56409/02/13_02_00_03_00 redo size 53809/02/13_03_00_04_00 redo size 39809/02/13_04_00_05_00 redo size 53209/02/13_05_00_06_00 redo size 56409/02/13_06_00_07_00 redo size 45809/02/13_07_00_08_00 redo size 45709/02/13_08_00_09_00 redo size 1589909/02/13_09_00_10_00 redo size 50709/02/13_10_00_11_00 redo size 42709/02/13_11_00_12_00 redo size 42809/02/13_12_00_13_00 redo size 54109/02/13_13_00_14_00 redo size 54009/02/13_14_00_15_00 redo size 51509/02/13_15_00_16_00 redo size 49909/02/13_16_00_17_00 redo size 52609/02/13_17_00_18_00 redo size 57109/02/13_18_00_19_00 redo size 54209/02/13_19_00_20_00 redo size 49209/02/13_20_00_21_00 redo size 44909/02/13_21_00_22_00 redo size 67809/02/13_22_00_23_00 redo size 969609/02/13_23_00_00_00 redo size 47609/03/13_00_00_01_00 redo size 46409/03/13_01_00_02_00 redo size 49309/03/13_02_00_03_00 redo size 56509/03/13_03_00_04_00 redo size 59509/03/13_04_00_05_00 redo size 51009/03/13_05_00_06_00 redo size 58109/03/13_06_00_07_00 redo size 55009/03/13_07_00_08_00 redo size 55909/03/13_08_00_09_00 redo size 1638109/03/13_09_00_10_00 redo size 48709/03/13_10_00_11_00 redo size 42509/03/13_11_00_12_00 redo size 44309/03/13_12_00_13_00 redo size 45809/03/13_13_00_14_00 redo size 53009/03/13_14_00_15_00 redo size 57409/03/13_15_00_16_00 redo size 47809/03/13_16_00_17_00 redo size 55009/03/13_17_00_18_00 redo size 58809/03/13_18_00_19_00 redo size 59309/03/13_19_00_20_00 redo size 56709/03/13_20_00_21_00 redo size 47309/03/13_21_00_22_00 redo size 45709/03/13_22_00_23_00 redo size 52009/03/13_23_00_00_00 redo size 51609/04/13_00_00_01_00 redo size 49309/04/13_01_00_02_00 redo size 48409/04/13_02_00_03_00 redo size 58109/04/13_03_00_04_00 redo size 57009/04/13_04_00_05_00 redo size 52909/04/13_05_00_06_00 redo size 56209/04/13_06_00_07_00 redo size 56609/04/13_07_00_08_00 redo size 52909/04/13_08_00_09_00 redo size 1599809/04/13_09_00_10_00 redo size 54909/04/13_10_00_11_00 redo size 54809/04/13_11_00_12_00 redo size 49709/04/13_12_00_13_00 redo size 58709/04/13_13_00_14_00 redo size 43609/04/13_15_00_16_00 redo size 58409/04/13_16_00_17_00 redo size 46109/04/13_17_00_18_00 redo size 51209/04/13_18_00_19_00 redo size 40609/04/13_19_00_20_00 redo size 38309/04/13_20_00_21_00 redo size 39609/04/13_21_00_22_00 redo size 105709/04/13_22_00_23_00 redo size 955909/04/13_23_00_00_00 redo size 48509/05/13_00_00_01_00 redo size 43409/05/13_01_00_02_00 redo size 41909/05/13_02_00_03_00 redo size 42909/05/13_03_00_04_00 redo size 39209/05/13_04_00_05_00 redo size 44209/05/13_05_00_06_00 redo size 49809/05/13_06_00_07_00 redo size 50109/05/13_07_00_08_00 redo size 43909/05/13_08_00_09_00 redo size 1615509/05/13_09_00_10_00 redo size 59309/05/13_10_00_11_00 redo size 49409/05/13_12_00_13_00 redo size 56209/05/13_13_00_14_00 redo size 51209/05/13_15_00_16_00 redo size 44309/05/13_16_00_17_00 redo size 40809/05/13_17_00_18_00 redo size 40509/05/13_18_00_19_00 redo size 38309/05/13_19_00_20_00 redo size 40409/05/13_20_00_21_00 redo size 41209/05/13_21_00_22_00 redo size 58109/05/13_22_00_23_00 redo size 1025009/05/13_23_00_00_00 redo size 53709/06/13_00_00_01_00 redo size 41409/06/13_01_00_02_00 redo size 41909/06/13_02_00_03_00 redo size 40909/06/13_03_00_04_00 redo size 40909/06/13_04_00_05_00 redo size 41709/06/13_05_00_06_00 redo size 47309/06/13_06_00_07_00 redo size 49909/06/13_07_00_08_00 redo size 45109/06/13_08_00_09_00 redo size 39209/06/13_09_00_10_00 redo size 43409/06/13_10_00_11_00 redo size 43209/06/13_11_00_12_00 redo size 48309/06/13_12_00_13_00 redo size 38909/06/13_13_00_14_00 redo size 44509/06/13_14_00_15_00 redo size 481