Printable View
Hi
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...)
Have Fun
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.
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.
Cheers,
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.
Nizar.
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.
well as I mentioned it depends on a few factors, one of our telco customer where I am right now, their call center application has over 6000 connections to oracle (OLTP) and we have setup 10 groups of 250MB redo logs each isolated in raw device. Tansdot if your database is small and you want a fast mean time recovery dont size your redo logs too big
Hi Pando,
I'm just curious, how often do you update your data in your DWH? Is it weekly or everyday?
If it's every day, then I just can't imagine the volume of your data coming to your DWH for you to set that much size of your redo logs.
ok, you just answer my question.
thanks.
it depends on DWH, there are few here, some loads data the first week of every month, some do it every week but weekends, some do daily incremental load etc etc. But all the load all nologging to generate as few redo as possible since all load can be reloaded if the process fails
forgive my ignorance pando...
but why use huge redo logs if your going to set your tables to nologging?
because even using nologging, the minimum redo is generated hell a lot because of the amount of data being loaded
aahh, ok I remember now. It is the rbs which is going to be bypass and not the redo logs.
It was really a damn question pando.
Thanks anyway.
Sizing of REDO logs also deterimes how close to a complete recovery you can perform. I.e. if you have a redo switch and is then archived every 15 minutes, then you can recover your db to 15 minutes prior to the db crash. Have redo logs that switch 3 hours... then that's when you can recover to.
If recovery isn't important via redologs, i.e NOARCHIVELOG mode, then having the redo files larger, rather than smaller is more preferred, I think anyway. I've seen REDO LOGS as large as 500 MB on insert/delete intensive databases, these systems are still switching every 15 minutes. The sizing of the redo logs is about Log Switching time, archiving time and recovery time.
Oh and as for NOLOGGING, we know it's a red herring, since it only minimises redo for DIRECT inserts into the tables/tablespaces. ALL NORMAL DML still generates redo.
Cheers,
[Edited by grjohnson on 10-23-2001 at 07:01 PM]
I agree, the size of the redo logs is dependent on the application plus the recovery required. At the end of the day if you require complete recovery (as little loss as possible) then a cluster running parallel server with mirrored disk sets is probably the option.
There was also a good script called logswitch.sql (from Tim Gorman (Evergreen Database Technologies, Inc.)) which displays not only how many switches per day, but a historial number. Gives you an idea of which days are the heaviest (if you didn't know that already)
Hey pando i am going to start another thread and hopefully people can describe what databases they are looking after, no names etc just size, type, worst problems etc.
Have Fun
There is also a free 60 day trial application called EZSQL which will perform the same functions by which running a query which gives you the number of log swtiches per hour over the last forntight and the numnber of changes recorded.
http://www.ezsql.net/download.htm
Cheers,
I have my redo logs set to 2048MB and I am still switching between 30 to 45 minutes. But, my load will double within a year, so my switching times should shrink.
That's some mega redo logs.
Point of interest:
If you expect your load to double, and currently you switch every 35 - 45 minutes. Is this a case of over proactive tuning... I mean, I is up to you of course, how often you want to switch (ahem, recover)... or would it be better to decrease the rollback size to optimal swtich to 15 minutes as per Oracle recommendation.
I'm not having a go at you in this regard, I have 250 MB redo logs that switch every 2 hours, but I also expect the database to grow dramatically.
It's just a thought, it is a proactive process or incorrect administration... hmmm.
Cheers,
I am in a different boat than your average DBA. I work on a development team and also support operations. Our database needs to be fully-automated, as possible, almost like its an embedded database. So, I have to trade-off certain things to ensure operations and overly-large redo logs is one of them.
Thank you for asking.