ANBOB™

redo中记录所有数据库的变化，包括所有数据文件上的变化，但不包含控制文件和参数文件的变化。redo最初记录在online redo logfile中，如果是归档模式会在填充满后生成离线的归档日志文件。有固定块大小的block组成redo logfile, 大小是在创建时指定，默认如linux和solarios为512bytes, hpux ia中为1024 bytes, 每个redo logfile都有固定的标准的数据文件头，记录了dbname ,thread,Compatibility Version, time, SCN等， 然后就是有redo header和redo record组成的redo block逻辑结构，一个redo block可以包含多个redo record,一个redo record又有一个多个Change Vectors组成,具体内容可以dump logfile查看具体trace. 当然LGWR并不是写满所有的redo block，由于LGWR内部原理设计会在写入后跳过未满的block，导致部分空间浪费，可以参考redo wastage系统统计信息计算，通常较大的redo浪费是有过频繁的commit导致。

有时会发现归档量突然某一天突增了需要查询原因， 更有甚者把降低redo生产量做为优化的目标。这里记录一些分析归档生成和分析变化的思路。

显示每天的归档生成/switch 次数

-- log_switch.sql --
set linesize 200 pagesize 1000
column day format a3
column total format 9999
column h00 format 999
column h01 format 999
column h02 format 999
column h03 format 999
column h04 format 999
column h04 format 999
column h05 format 999
column h06 format 999
column h07 format 999
column h08 format 999
column h09 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column h24 format 999
break on report
compute max of "total" on report
compute max of "h01" on report
compute max of "h02" on report
compute max of "h03" on report
compute max of "h04" on report
compute max of "h05" on report
compute max of "h06" on report
compute max of "h07" on report
compute max of "h08" on report
compute max of "h09" on report
compute max of "h10" on report
compute max of "h11" on report
compute max of "h12" on report
compute max of "h13" on report
compute max of "h14" on report
compute max of "h15" on report
compute max of "h16" on report
compute max of "h17" on report
compute max of "h18" on report
compute max of "h19" on report
compute max of "h20" on report
compute max of "h21" on report
compute max of "h22" on report
compute max of "h23" on report
compute sum of NUM on report
compute sum of GB on report
compute sum of MB on report
compute sum of KB on report

REM Script to Report the Redo Log Switch History


alter session set nls_date_format='DD MON YYYY';
select thread#, trunc(first_time) as "date", to_char(first_time,'Dy') as "Day", count(1) as "total",
sum(decode(to_char(first_time,'HH24'),'00',1,0)) as "h00",
sum(decode(to_char(first_time,'HH24'),'01',1,0)) as "h01",
sum(decode(to_char(first_time,'HH24'),'02',1,0)) as "h02",
sum(decode(to_char(first_time,'HH24'),'03',1,0)) as "h03",
sum(decode(to_char(first_time,'HH24'),'04',1,0)) as "h04",
sum(decode(to_char(first_time,'HH24'),'05',1,0)) as "h05",
sum(decode(to_char(first_time,'HH24'),'06',1,0)) as "h06",
sum(decode(to_char(first_time,'HH24'),'07',1,0)) as "h07",
sum(decode(to_char(first_time,'HH24'),'08',1,0)) as "h08",
sum(decode(to_char(first_time,'HH24'),'09',1,0)) as "h09",
sum(decode(to_char(first_time,'HH24'),'10',1,0)) as "h10",
sum(decode(to_char(first_time,'HH24'),'11',1,0)) as "h11",
sum(decode(to_char(first_time,'HH24'),'12',1,0)) as "h12",
sum(decode(to_char(first_time,'HH24'),'13',1,0)) as "h13",
sum(decode(to_char(first_time,'HH24'),'14',1,0)) as "h14",
sum(decode(to_char(first_time,'HH24'),'15',1,0)) as "h15",
sum(decode(to_char(first_time,'HH24'),'16',1,0)) as "h16",
sum(decode(to_char(first_time,'HH24'),'17',1,0)) as "h17",
sum(decode(to_char(first_time,'HH24'),'18',1,0)) as "h18",
sum(decode(to_char(first_time,'HH24'),'19',1,0)) as "h19",
sum(decode(to_char(first_time,'HH24'),'20',1,0)) as "h20",
sum(decode(to_char(first_time,'HH24'),'21',1,0)) as "h21",
sum(decode(to_char(first_time,'HH24'),'22',1,0)) as "h22",
sum(decode(to_char(first_time,'HH24'),'23',1,0)) as "h23"
from
v$archived_log
where first_time > trunc(sysdate-10)
group by thread#, trunc(first_time), to_char(first_time, 'Dy') order by 2,1;

REM Script to calculate the archive log size generated per day for each Instances.

select THREAD#, trunc(first_time) as "DATE"
, count(1) num
, trunc(sum(blocks*block_size)/1024/1024/1024) as GB
, trunc(sum(blocks*block_size)/1024/1024) as MB
, sum(blocks*block_size)/1024 as KB
from v$archived_log
where first_time > trunc(sysdate-10)
group by thread#, trunc(first_time)
order by 2,1
;

redo突然增长变化的原因

• 业务量变化 DML增加或块变化增加
• 改变了列长度或数据填充长度
• 表类型变化，如TEMP
• 增加新表
• 启动了数据库附加日志
• 创建了新的索引
• 过度commit，产生的浪费
• redo group block size改变
• 改变了循环、批量修改SQL的方式

分析日志的一些方法

1, logminer（注：没有启用附件日志不会看到IOT）
2, v$sess_io 的block_changes 和v$session 关连
3, v$transaction 中undo 生成量USED_UBLK and USED_UREC
4, AWR中的(dba_hist_seg_stat) block change和gets/executions
5，DBA_TAB_MODIFICATIONS中的DML量

如何降低redo生成

1, nologging
– direct load (SQL*Loader)
– direct-load INSERT
– CREATE TABLE … AS SELECT
– CREATE INDEX
– ALTER TABLE … MOVE PARTITION
– ALTER TABLE … SPLIT PARTITION
– ALTER INDEX … SPLIT PARTITION
– ALTER INDEX … REBUILD
– ALTER INDEX … REBUILD PARTITION
– INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
2, 删除不需要的索引
3，大数据加载时删除索引，加载后再重建
4， 整理碎片，更小的表块，更小的redo
5, Recreate some tables as IOTs
6, Increase the cache of the sequences.
7, 使用全局临时表
8,减少MVIEW刷新频率
9, 减少不必要的更新
10，使用更小的列数据类型精度
11，少用char

相关MOS note

Document:832504.1 – Excessive Archives / Redo Logs Generation due to AWR / ASH – Troubleshooting
Document:167492.1 – How to Find Sessions Generating Lots of Redo
Document:300395.1 – How To Determine The Cause Of Lots Of Redo Generation Using LogMiner
Document:199298.1 – Diagnosing excessive redo generation
Document:69739.1 – How to Turn Archiving ON and OFF in Oracle RDBMS
Document: 188691.1 : How to Avoid Generation of Redolog Entries

打赏

微信扫一扫，打赏作者吧～