Re: Estimating the impact of reducing Redo log size.

MAK wrote:

> Estimating the impact of reducing Redo log size.
>
> Currently I have online redo log file size of 2GB. I intend to reduce
> this size to 1GB if performance impact is minimal. I have looked at
> statspack report for peak redo generation interval(1 hour).
>
> I observed that only 12 second wait for 'log file switch completion'
> for 6 log swithes in 1hour interval. Additionaly I looked at
> statistics like 'redo size' , 'redo log space request' , 'redo log
> space wait time' and all these telling me that if I reduce redo
> logfile size to 1GB, wait time would be 24 seconds for 1 hour
> interval, which look pretty light to me.
>
> Am I missing anything here.. Do I need to look at something else?

First place I would look is in your alert log. How frequently do you currently get 'Thread 1 advanced to Log Sequence XXX' messages?

If it's less frequently than once an hour or so, then a reduction in size is in order. If it's more frequently than three times an hour, then an increase in size is in order.

Waits on the redo system are extremely difficult to diagnose in this regard, because they can be caused by practically anything. If DBWR stalls, or can't keep up, then checkpoints take for ever, and a log switch into a log that hasn't been completely checkpointed therefore can't take place, so redo backs up in the log buffer and you get redo log space requests. Same thing if ARCH is too slow to archive.

I'd suggest that waits are a good diagnostic for the log buffer and the health of your I/O subsystems generally, but not so hot for suggesting a redo log size. Not when the decision is to be made between 1 and 2GB at least.

Since you have a statspack report, I'd be beaming it up to www.oraperf.com and seeing what suggestions you get there, anyway.

Regards
HJR
>
> Event Waits Timeouts Time (s) (ms)
>
> log file switch completion 65 5 12 182
>
>
>
> Statistics Value.
>
> redo size 13,437,787,536
>
> redo log space requests 80
>
> redo log space wait time 1,246
Received on Fri Oct 08 2004 - 00:50:58 CDT