查看redo等待
Select Wait_Class, Event, Total_Waits, Total_Timeouts, Time_Waited, Round(Time_Waited / 100 / 60, 1) Time_Wait_Minutes,Average_Wait
From V$system_Event
Where Lower(Event) Like '%log%' Or Lower(Event) Like '%redo%'
Order By 1;
查看redo竞争 redo log space requests接近0或者是否持续增长
select name,value from V$SYSSTAT where name IN ('redo buffer allocation retries', 'redo log space requests');
Redo latch 命中率
Select retries.value/entries.value "Redo Log Buffer Retry Ratio"
From V$sysstat retries, V$sysstat entries
Where retries.name = 'redo buffer allocation retries'
And entries.name = 'redo entries';
SELECT substr(ln.name, 1, 20) statistic, gets, misses, immediate_gets, immediate_misses
FROM V$LATCH l, V$LATCHNAME ln WHERE ln.name in ('redo allocation', 'redo copy') and ln.latch# = l.latch#;
当前session产生的redo数量
select n.name, t.value from v$mystat t join V$STATNAME n on t.statistic# = n.statistic# where n.name = 'redo size';