[20180227]显示每小时产生的redo.txt

[20180227]显示每小时产生的redo.txt

--//参照链接https://orainternals.wordpress.com/2013/06/12/dude-where-is-my-redo/,修改一点点实现:
--//原来显示按天,修改成小时.

REM  You need Diagnostic Pack licence to execute this query!
REM  Author: Riyaj Shamsudeen
col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
rem set colsep '|'

WITH redo_sz
     AS (SELECT sysst.snap_id
               ,sysst.instance_number
               ,begin_interval_time
               ,end_interval_time
               ,startup_time
               ,  VALUE
                - LAG
                  (
                     VALUE
                  )
                  OVER
                  (
                     PARTITION BY startup_time, sysst.instance_number
                     ORDER BY
                        begin_interval_time
                       ,startup_time
                       ,sysst.instance_number
                  )
                   stat_value
               ,    EXTRACT
                    (
                       DAY FROM (end_interval_time - begin_interval_time)
                    )
                  * 24
                  * 60
                  * 60
                +   EXTRACT
                    (
                       HOUR FROM (end_interval_time - begin_interval_time)
                    )
                  * 60
                  * 60
                +   EXTRACT
                    (
                       MINUTE FROM (end_interval_time - begin_interval_time)
                    )
                  * 60
                + EXTRACT
                  (
                     SECOND FROM (end_interval_time - begin_interval_time)
                  )
                   DELTA
           FROM sys.wrh$_sysstat sysst, DBA_HIST_SNAPSHOT snaps
          WHERE     (sysst.dbid, sysst.stat_id) IN (SELECT dbid, stat_id
                                                      FROM sys.wrh$_stat_name
                                                     WHERE stat_name =
                                                              'redo size')
                AND snaps.snap_id = sysst.snap_id
                AND snaps.dbid = sysst.dbid
                AND sysst.instance_number = snaps.instance_number
                AND begin_interval_time > SYSDATE - 90)
  SELECT instance_number
        ,TO_DATE (TO_CHAR (begin_interval_time, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24')
            dt
        ,round(SUM (stat_value)/1024/1024,3) redoM
    FROM redo_sz
GROUP BY instance_number
        ,TO_DATE
         (
            TO_CHAR (begin_interval_time, 'YYYY-MM-DD HH24')
           ,'YYYY-MM-DD HH24'
         )
ORDER BY instance_number, 2;

--//脚本有一个小毛病,就是如果数据库关闭,最后1个小时范围内没有记录.因为没有建立awr报表,看来关闭数据库顺手执行
--//dbms_workload_repository.create_snapshot();也是一个习惯.
--//通过归档定位,我以前写的:
$ cat d_arc.sql
SELECT TO_CHAR (first_time, 'YYYY-MM-DD WW') AS "DATE"
        ,TO_CHAR (first_time, 'DAY') week
        ,ROUND (SUM ( (blocks + 1) * block_size) / 1024 / 1024, 0) AS "SIZE_MB"
        ,COUNT (*) AS "NUMBER_OF_SWITCHES_PER_DAY"
    FROM v$archived_log
   WHERE dest_id = 1 and first_time>=trunc(sysdate)-20
GROUP BY TO_CHAR (first_time, 'YYYY-MM-DD WW'), TO_CHAR (first_time, 'DAY')
ORDER BY TO_CHAR (first_time, 'YYYY-MM-DD WW') DESC;

--//这个也是有问题,如果日志文件很大,可能一天都没有切换,仅仅作为估计.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2151330/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2151330/