欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-22 12:32:58
script:查看redo产生的历史信息   script:查看redo产生的历史信息   sql> alter session set...

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                     redo size                        549
08/30/13_03_00_04_00                     redo size                        476
08/30/13_04_00_05_00                     redo size                        471
08/30/13_05_00_06_00                     redo size                        512
08/30/13_06_00_07_00                     redo size                        548
08/30/13_07_00_08_00                     redo size                        585
08/30/13_08_00_09_00                     redo size                      15752
08/30/13_09_00_10_00                     redo size                        541
08/30/13_10_00_11_00                     redo size                        457
08/30/13_11_00_12_00                     redo size                        443
08/30/13_12_00_13_00                     redo size                        456
08/30/13_13_00_14_00                     redo size                        473
08/30/13_14_00_15_00                     redo size                        503
08/30/13_15_00_16_00                     redo size                        460
08/30/13_16_00_17_00                     redo size                        491
08/30/13_17_00_18_00                     redo size                        592
08/30/13_18_00_19_00                     redo size                        536
08/30/13_19_00_20_00                     redo size                        533
08/30/13_20_00_21_00                     redo size                        551
08/30/13_21_00_22_00                     redo size                        869
08/30/13_22_00_23_00                     redo size                       9895
08/30/13_23_00_00_00                     redo size                        543
08/31/13_00_00_01_00                     redo size                        432
08/31/13_01_00_02_00                     redo size                        460
08/31/13_02_00_03_00                     redo size                        445
08/31/13_03_00_04_00                     redo size                        444
08/31/13_04_00_05_00                     redo size                        509
08/31/13_05_00_06_00                     redo size                        520
08/31/13_06_00_07_00                     redo size                        566
08/31/13_07_00_08_00                     redo size                        508
08/31/13_08_00_09_00                     redo size                      14947
08/31/13_09_00_10_00                     redo size                        449
08/31/13_10_00_11_00                     redo size                       6322
08/31/13_11_00_12_00                     redo size                        444
08/31/13_12_00_13_00                     redo size                        463
08/31/13_13_00_14_00                     redo size                        478
08/31/13_14_00_15_00                     redo size                       1019
08/31/13_15_00_16_00                     redo size                        461
08/31/13_16_00_17_00                     redo size                        481
08/31/13_17_00_18_00                     redo size                        440
08/31/13_18_00_19_00                     redo size                       1851
08/31/13_19_00_20_00                     redo size                        562
08/31/13_20_00_21_00                     redo size                        442
08/31/13_21_00_22_00                     redo size                        423
08/31/13_22_00_23_00                     redo size                       3649
08/31/13_23_00_00_00                     redo size                        573
09/01/13_00_00_01_00                     redo size                        500
09/01/13_01_00_02_00                     redo size                        462
09/01/13_02_00_03_00                     redo size                        447
09/01/13_03_00_04_00                     redo size                        464
09/01/13_04_00_05_00                     redo size                        473
09/01/13_05_00_06_00                     redo size                       1058
09/01/13_06_00_07_00                     redo size                       1929
09/01/13_07_00_08_00                     redo size                        470
09/01/13_08_00_09_00                     redo size                      15616
09/01/13_09_00_10_00                     redo size                        496
09/01/13_10_00_11_00                     redo size                        524
09/01/13_11_00_12_00                     redo size                        576
09/01/13_12_00_13_00                     redo size                        517
09/01/13_13_00_14_00                     redo size                        533
09/01/13_14_00_15_00                     redo size                        516
09/01/13_15_00_16_00                     redo size                        436
09/01/13_16_00_17_00                     redo size                        518
09/01/13_17_00_18_00                     redo size                        450
09/01/13_18_00_19_00                     redo size                        476
09/01/13_19_00_20_00                     redo size                        473
09/01/13_20_00_21_00                     redo size                        510
09/01/13_21_00_22_00                     redo size                        469
09/01/13_22_00_23_00                     redo size                        465
09/01/13_23_00_00_00                     redo size                        548
09/02/13_00_00_01_00                     redo size                        531
09/02/13_01_00_02_00                     redo size                        564
09/02/13_02_00_03_00                     redo size                        538
09/02/13_03_00_04_00                     redo size                        398
09/02/13_04_00_05_00                     redo size                        532
09/02/13_05_00_06_00                     redo size                        564
09/02/13_06_00_07_00                     redo size                        458
09/02/13_07_00_08_00                     redo size                        457
09/02/13_08_00_09_00                     redo size                      15899
09/02/13_09_00_10_00                     redo size                        507
09/02/13_10_00_11_00                     redo size                        427
09/02/13_11_00_12_00                     redo size                        428
09/02/13_12_00_13_00                     redo size                        541
09/02/13_13_00_14_00                     redo size                        540
09/02/13_14_00_15_00                     redo size                        515
09/02/13_15_00_16_00                     redo size                        499
09/02/13_16_00_17_00                     redo size                        526
09/02/13_17_00_18_00                     redo size                        571
09/02/13_18_00_19_00                     redo size                        542
09/02/13_19_00_20_00                     redo size                        492
09/02/13_20_00_21_00                     redo size                        449
09/02/13_21_00_22_00                     redo size                        678
09/02/13_22_00_23_00                     redo size                       9696
09/02/13_23_00_00_00                     redo size                        476
09/03/13_00_00_01_00                     redo size                        464
09/03/13_01_00_02_00                     redo size                        493
09/03/13_02_00_03_00                     redo size                        565
09/03/13_03_00_04_00                     redo size                        595
09/03/13_04_00_05_00                     redo size                        510
09/03/13_05_00_06_00                     redo size                        581
09/03/13_06_00_07_00                     redo size                        550
09/03/13_07_00_08_00                     redo size                        559
09/03/13_08_00_09_00                     redo size                      16381
09/03/13_09_00_10_00                     redo size                        487
09/03/13_10_00_11_00                     redo size                        425
09/03/13_11_00_12_00                     redo size                        443
09/03/13_12_00_13_00                     redo size                        458
09/03/13_13_00_14_00                     redo size                        530
09/03/13_14_00_15_00                     redo size                        574
09/03/13_15_00_16_00                     redo size                        478
09/03/13_16_00_17_00                     redo size                        550
09/03/13_17_00_18_00                     redo size                        588
09/03/13_18_00_19_00                     redo size                        593
09/03/13_19_00_20_00                     redo size                        567
09/03/13_20_00_21_00                     redo size                        473
09/03/13_21_00_22_00                     redo size                        457
09/03/13_22_00_23_00                     redo size                        520
09/03/13_23_00_00_00                     redo size                        516
09/04/13_00_00_01_00                     redo size                        493
09/04/13_01_00_02_00                     redo size                        484
09/04/13_02_00_03_00                     redo size                        581
09/04/13_03_00_04_00                     redo size                        570
09/04/13_04_00_05_00                     redo size                        529
09/04/13_05_00_06_00                     redo size                        562
09/04/13_06_00_07_00                     redo size                        566
09/04/13_07_00_08_00                     redo size                        529
09/04/13_08_00_09_00                     redo size                      15998
09/04/13_09_00_10_00                     redo size                        549
09/04/13_10_00_11_00                     redo size                        548
09/04/13_11_00_12_00                     redo size                        497
09/04/13_12_00_13_00                     redo size                        587
09/04/13_13_00_14_00                     redo size                        436
09/04/13_15_00_16_00                     redo size                        584
09/04/13_16_00_17_00                     redo size                        461
09/04/13_17_00_18_00                     redo size                        512
09/04/13_18_00_19_00                     redo size                        406
09/04/13_19_00_20_00                     redo size                        383
09/04/13_20_00_21_00                     redo size                        396
09/04/13_21_00_22_00                     redo size                       1057
09/04/13_22_00_23_00                     redo size                       9559
09/04/13_23_00_00_00                     redo size                        485
09/05/13_00_00_01_00                     redo size                        434
09/05/13_01_00_02_00                     redo size                        419
09/05/13_02_00_03_00                     redo size                        429
09/05/13_03_00_04_00                     redo size                        392
09/05/13_04_00_05_00                     redo size                        442
09/05/13_05_00_06_00                     redo size                        498
09/05/13_06_00_07_00                     redo size                        501
09/05/13_07_00_08_00                     redo size                        439
09/05/13_08_00_09_00                     redo size                      16155
09/05/13_09_00_10_00                     redo size                        593
09/05/13_10_00_11_00                     redo size                        494
09/05/13_12_00_13_00                     redo size                        562
09/05/13_13_00_14_00                     redo size                        512
09/05/13_15_00_16_00                     redo size                        443
09/05/13_16_00_17_00                     redo size                        408
09/05/13_17_00_18_00                     redo size                        405
09/05/13_18_00_19_00                     redo size                        383
09/05/13_19_00_20_00                     redo size                        404
09/05/13_20_00_21_00                     redo size                        412
09/05/13_21_00_22_00                     redo size                        581
09/05/13_22_00_23_00                     redo size                      10250
09/05/13_23_00_00_00                     redo size                        537
09/06/13_00_00_01_00                     redo size                        414
09/06/13_01_00_02_00                     redo size                        419
09/06/13_02_00_03_00                     redo size                        409
09/06/13_03_00_04_00                     redo size                        409
09/06/13_04_00_05_00                     redo size                        417
09/06/13_05_00_06_00                     redo size                        473
09/06/13_06_00_07_00                     redo size                        499
09/06/13_07_00_08_00                     redo size                        451
09/06/13_08_00_09_00                     redo size                        392
09/06/13_09_00_10_00                     redo size                        434
09/06/13_10_00_11_00                     redo size                        432
09/06/13_11_00_12_00                     redo size                        483
09/06/13_12_00_13_00                     redo size                        389
09/06/13_13_00_14_00                     redo size                        445
09/06/13_14_00_15_00                     redo size                        481