Contention for Space in the Redo Log Buffer

[anuragmin]

I ran this query on the database and got a result of 77.44 %. I know that it should be less than 1%. what do I need to fix this problem.

/***Detecting Contention for Space in the Redo Log Buffer**/


select round(greatest(
2 (sum(decode(ln.name, 'redo copy', misses,0))
3 / greatest(sum(decode(ln.name, 'redo copy', gets,0)),1)),
4 (sum(decode(ln.name, 'redo allocation', misses,0))
5 / greatest(sum(decode(ln.name, 'redo allocation', gets,0)),1)),
6 (sum(decode(ln.name, 'redo copy', immediate_misses,0))
7 / greatest(sum(decode(ln.name, 'redo copy', immediate_gets,0))
8 + sum(decode(ln.name, 'redo copy', immediate_misses,0)),1)),
9 (sum(decode(ln.name, 'redo allocation', immediate_misses,0))
10 / greatest(sum(decode(ln.name, 'redo allocation', immediate_gets,0))
11 + sum(decode(ln.name, 'redo allocation', immediate_misses,0)),1)))
12 * 100,2)"Percentage "
13 from v$latch l, v$latchname ln
14 where l.latch# = ln.latch#;


Percentage -----------
77.44

[mbaliga]

Hi,

Increase the redo copy latches based on the number of CPUs you have. Also look at the redo space requests Vs redo writes & logfile sync time and tune the log buffer size.

Baliga

[sreddy]

See the size of current redologs is enough. If not try creating/adding couple of more logs or resizing current logs