I am having very poor performance of redo log in my database.
I normally run long running batch processes on our database.
I am seeing the following:
redo log space requests ===1198
redo log space wait time ===68814
I have my log_buffer set to 5242880
we have only 2 mirrored redo log files of size 200MB.
Should I add more redo log files ? What size?
Any other suggestions appreciated.
With only the information provided...
Increase the no. of redo logs. (+2 perhaps)
The same size.
I would test making the log_buffer smaller too
perhaps 512K instead of 5M.
(Just maybe this will help...)
Here is what the doco (ver8 says)
redo log space requests:
The active log file is full and Oracle is waiting for disk space to be allocated for the redo log entries. Space is created by performing a log switch.
Small Log files in relation to the size of the SGA or the commit rate of the work load can cause problems. When the log switch occurs, Oracle must ensure that all committed dirty buffers are written to disk before switching to a new log file. If you have a large SGA full of dirty buffers and small redo log files, a log switch must wait for DBWR to write dirty buffers to disk before continuing.
Also examine the log file space and log file space switch wait events in V$SESSION_WAIT.
redo log space wait time:
The total elapsed time of waiting for redo log space request in 10s of milliseconds.
Performance... Push the envelope!
Hmmm, redo_buffer. You can have a bottle neck when writting to the REDO LOGS. Now, Oracle suggests that you probably won't get much of a boast in perfmorance in your REDO_BUFFER by increasing it > 1 MB. Even though.. I increased mine to 4 MB, and performce has been better. I'd look to see what device your REDO_LOGS are on, since writing to redo logs is performed sequentially, there is NO benefit is placing them on any drive that is stripped.
I also agree with roobaron what suggested, definatley add an additional 2 logs, make this change before fiddleing with ou LOG_BUFFER sizing.
OCP 8i, 9i DBA
Good size for redo logs is 4-10M.
LOG_BUFFER should be at most 1M in 8i. There are several reasons for that. You cannot have better performance if you increase LOG_BUFFER from 1M to 4M. (I assume grjohnson meant LOG_BUFFER not REDO_BUFFER).
2 redo log groups is the minimum, 3 is better, some DBA preffer to have 4 or 5.
I think your problem is more with the size of your redo log file than the buffer. Let me explain.
2 files of 200 M each. When one gets filed up, a logswitch/checkpoint occurs which cause DBWR to move all dirty buffers to the datafiles. In the meanwhile your other log file is getting filled up fast enough and it becomes full before the checkpoint on the first file is over. Naturally, when the second file fills up it has to wait to clear out and causes the log_buffer to fill up which in turn causes users to wait for space in the log buffer.
Check your alert log file for "Checkpoint not complete" messages. If they exist then reduce the size of your log files/ increase number and also analyze your LOG_CHECPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters.
I agree that the size is a problem. I have never heard before of a 200M redo log file.
I say reduce your redolog size to 10M,
make it at least 3 groups,
log_buffers to 1M,
log_checkpoint_interval greater than 10M or 99999999
log_timeout to 0.
And do not put your redologs to RAID5/or with stripping.
Separate redologs disk source from archive logs disk source.
I suggest follow the advise above. I personally set log_checkpoint_interval = 9999999 :-) not 99999999
200M log is not that big, I am just sitting on 4 DWH instance with redo log member of 250MB each and 5 groups
Itīs all depends on your db size, transactions & batch jobs etc
Well if you are going to use that much redo log size(200M),
just make sure you have more than enough number of groups to make sure always have available redo logs.
Anyway, your setup for redo logs size is for Data Warehousing/Datamart or DSS.
I don't know if tansdot has a DSS system or OLTP type.
Click Here to Expand Forum to Full Width