Re: Redo Log Problem

Scott W. Patterson wrote:
>
> I am running a script from the Oracle on NT website called CheckTune.
> The results of the queries are a follows below....
>
> Current Date and Time: 12/14/2000 12:18:40
> Database name: par_22
> Measurement Goal Value Action
> ------------------------------ ------ -----------
> -------------------------------------------------
> Library cache hit percent >=0.99 1.00 OK
> Dictionary cache hit percent >=0.90 0.93 OK
> Buffer cache hit percent >=0.90 0.96 OK
> Sort area hit percent >=0.90 1.00 OK
> Redo Log space requests =0
> 180 Increase the LOG_BUFFER in INIT.ORA
> "
> Examine Redo Log size
> "
> Examine number of Redo Log groups
> -- Current value: log_buffer = 10240000
> -- Current value: redo log size = 5242880
> -- Current value: # of redo log groups = 3
> Enqueue waits =0 0 OK
> Checkpoints not completed <=1 0 OK
> Rollback contention ratio <=0.01 0.00 OK
> Redo allocation latch miss (W) <=0.01 0.00 OK
> Redo allocation latch miss (I) <=0.01 0.00 OK
> Redo copy latch miss (WTW) <=0.01 0.90
> Increase LOG_SIMULTANEOUS_COPIES 2xCPU in INIT.ORA
> -- Current value: log_simultaneous_copies = 4
> -- Current value: log_small_entry_max_size = 0
> Redo copy latch miss (Immed) <=0.01 0.00 OK
> Freelist contention ratio <=0.01 0.00 OK
> Input truncated to 15 characters
>
> I am trying to understand what to do about the the Redo Log Space
> Requests problem ...
> I have increased the size of the log files, the log buffer, and the
> number of groups (3 groups, with two members each -size 5mg)
> I set the log buffer to twice the size of a redo log file - at least I
> think so..
> The log files are on a hardware - RAID 1 disk . I speculate that since
> all the log files are on this same disk is the reason I am getting this
> problem.
>
> Any suggestions????
>
> Thanks,
>
> Scott Patterson
> Patterson Advertising Reports

Its very rare to get any benefit from a log buffer greater than approx 512k, and as you get a lot larger than this, you can make things worse with "log file sync" waits.

The "'space requests' implies make buffer bigger" is typically an incorrect assertion - you will always get some space requests when a log file switches - I'd only look at increasing the log buffer if a) you are getting these outside the times when you are log switching b) your log files are optimal (ie size, disk layout etc )

A better statistic would be to look for "log buffer space" waits.

Having said that, all the normal recommendations apply - separating log files, making them large, raw etc etc

HTH
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"