MyException - 我的异常网
当前位置:我的异常网» 数据库 » script:查看redo发生的历史信息

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

www.MyException.Cn  网友分享于:2013-09-10  浏览:0次
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

文章评论

做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
每天工作4小时的程序员
每天工作4小时的程序员
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
为什么程序员都是夜猫子
为什么程序员都是夜猫子
10个调试和排错的小建议
10个调试和排错的小建议
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
一个程序员的时间管理
一个程序员的时间管理
中美印日四国程序员比较
中美印日四国程序员比较
程序员和编码员之间的区别
程序员和编码员之间的区别
Java程序员必看电影
Java程序员必看电影
总结2014中国互联网十大段子
总结2014中国互联网十大段子
代码女神横空出世
代码女神横空出世
旅行,写作,编程
旅行,写作,编程
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
那些争议最大的编程观点
那些争议最大的编程观点
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
编程语言是女人
编程语言是女人
程序员应该关注的一些事儿
程序员应该关注的一些事儿
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
我是如何打败拖延症的
我是如何打败拖延症的
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
我的丈夫是个程序员
我的丈夫是个程序员
如何成为一名黑客
如何成为一名黑客
老程序员的下场
老程序员的下场
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
程序员都该阅读的书
程序员都该阅读的书
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
鲜为人知的编程真相
鲜为人知的编程真相
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有