Re: How to improve Log Switch performance

Try increasing you DBWR processes, we ran into a similar situation where we were running into redo log contention problems and check point not complete errors. Increasing the number of dbwr writer processes helped greatly. We stopped using async_io because the performance of it had sucked. Also, recall what happens when you check point or switch logs, all dirty buffers in the cache are suppose to be written out to disk and all the file headers are updated(I read that in oracle docs somewhere but forget where) which means your DB may not go any further until the checkpoint finishes. Also, if I remember right, in setting the number of DBWR processes, it should not exceed 2*(number of disks) your database is on. So, if your db is on 8 disks, don't exceed 16.

HTH,
Pete's

"Reza" <reza_at_digital-dispatch.co.uk> wrote in message news:<1002881304.7119.0.nnrp-09.d4f07ab4_at_news.demon.co.uk>...
> Hi
> I have a 8.0.5.2.2 server on HP-UX 10.20 running a massively busy OLTP
> instance.
> I have adjusted the checkpoint intervals to happen only during log switches
> (every 20 minutes in Average load).
> Database has 69 datafile, 3 control files, and 4 DB_WRITER processes
> running.
> Every time a Log switch happens it exactly takes 3 minutes and users suffer
> from hangings for some of this period. The redo log files are on two
> independent physical volumes separate from data files. The wait events shoot
> up drastically during Log switch. I do not exactly know what could improve
> this situation. Would adding more DB Writers help, even though we have only
> 2 CPUs on the server.
> I know this is not that straight forward and might need much more detail
> information to analyze the situation, however any indication toward right
> direction that I can take, or other factors that I have to look into, would
> be a great help.
> Many thanks for any help, much appreciated.
> Regards
> Reza
Received on Fri Oct 12 2001 - 15:25:02 CDT