[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

                +   EXTRACT

                       HOUR FROM (end_interval_time - begin_interval_time)

                       MINUTE FROM (end_interval_time - begin_interval_time)

                + 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;

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