M V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD')
ORDER BY 1 DESC;
如下案例所示,2018-03-26日有一个归档日志暴增的情况,我们可以横向、纵向对比分析,然后判定在17点到18点这段时间出现异常,这个时间段与往常对比,生成了大量的redo log。
这里分享一个非常不错的分析redo log 历史信息的SQL
-----------
REM Author: Riyaj Shamsudeen @OraInternals, LLC
REM www.orainternals.com
REM
REM Functionality: This script is to print redo size rates in a RAC claster
REM **************
REM
REM Source : AWR tables
REM
REM Exectution type: Execute from sqlplus or any other tool.
REM
REM Parameters: No parameters. Uses Last snapshot and the one prior snap
REM No implied or explicit warranty
REM
REM Please send me an email to rshamsud@orainternals.com, if you enhance this script :-)
REM This is a open Source code and it is free to use and modify.
REM Version 1.20
REM
------------------------------------------------------------------------------------------------
set colsep '|'
set lines 220
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
set pagesize 10000
with redo_data as (
SELECT instance_number,
to_date(to_char(redo_date,'DD-MON-YY-HH24:MI'), 'DD-MON-YY-HH24:MI') redo_dt,
trunc(redo_size/(1024 * 1024),2) redo_size_mb
FROM (
SELECT dbid, instance_number, redo_date, redo_size , startup_time FROM (
SELECT sysst.dbid,sysst.instance_number, begin_interval_time redo_date, startup_time,
VALUE -
lag (VALUE) OVER
( PARTITION BY sysst.dbid, sysst.instance_number, startup_time
ORDER BY begin_interval_time ,sysst.instance_number
) redo_size
FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE sysst.stat_id =
( SELECT 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 snaps.begin_interval_time> sysdate-30
ORDER BY snaps.snap_id )
)
)
select instance_number, redo_dt, redo_size_mb,
sum (redo_size_mb) over (partition by trunc(redo_dt)) total_daily,
trunc(sum (redo_size_mb) over (partition by trunc(redo_dt))/24,2) hourly_rate
from redo_Data
order by redo_dt, instance_number
/
分析到这个阶段,我们还只获取了那个时间段归档日志异常(归档日志暴增),那么要如何定位到相关的SQL语句呢?我们可以用下面SQL来定位:在这个时间段,哪些对象有大量数据块变化情况。如下所示,这两个对象(当然,对象有可能是表或索引,这个案例中,这两个对象其实是同一个表和其主键索引)有大量的数据块修改情况。基本上我们可以判断是涉及这个对象的DML语句生成了大量的redo log, 当然有可能有些场景会比较复杂,不是那么容易定位。
SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME,
DHSO.OBJECT_NAME,
SUM(DB_BLOCK_CHANGES_DELTA) BLOCK_CHANGED
FROM DBA_HIST_SEG_STAT DHSS,
DBA_HIST_SEG_STAT_OBJ DHSO,
DBA_HIST_SNAPSHOT DHS
WHERE DHS.SNAP_ID = DHSS.SNAP_ID
AND DHS.INSTANCE_NUMBER = DHSS.INSTANCE_NUMBER
AND DHSS.OBJ# = DHSO.OBJ#
AND DHSS.DATAOBJ# = DHSO.DATAOBJ#
AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2018-03-26 17:00',
'YYYY-MM-DD HH24:MI')
AND
TO_DATE('2018-03-26 18:00', 'YYYY-MM-DD HH24:MI')
GROUP BY TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'),
DHSO.OBJECT_NAME
HAVING SUM(DB_BLOCK_CHANGES_DELTA) > 0
ORDER BY SUM(DB_BLOCK_CHANGES_DELTA) DESC;
此时,我们可以生成这个时间段的AWR报告,那些产生大量redo log的SQL一般是来自TOP Gets、TOP Execution中某个DML SQL语句或一些DML SQL语句,结合上面SQL定位到的对象和下面相关SQL语句,基本上就可以判断就是下面这两个SQL产生了大量的redo log。(第一个SQL是调用包,包里面有对这个表做大量的DELETE、INSERT操作)
如果你此时还不能完全断定,也可以使用下面SQL来辅佐判断那些SQL生成了大量的redo log。 在这个案例中, 上面AWR报告中发现的SQL语