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