[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;
--//这个也是有问题,如果日志文件很大,可能一天都没有切换,仅仅作为估计.
