Re: DBWR Tuning

"Denis Do" <nospam.denisdo_at_yahoo.com> wrote in message news:slrncrqf56.3vo.nospam.denisdo_at_denisdo.news.google.com...
> ... skipped about number of DBWriters ...
> Niall,
>
> I want to explain why I would do this.
> 1st, obviously he has at least 2 CPUS - and not only one
> HDD, I assume (see original post).
> In such situation it is better to have 2 or even more
> DBWriters - that will help you distribute your load between
> CPUs and IO between drives, also consider the fact that the
> system is OLTP, so you need scan LRU list for quick blocks
> fast.

I'm going to go back to the quotation from the documentation.

<quote>
The DB_WRITER_PROCESSES initialization parameter lets you configure multiple database writer processes (from DBW0 to DBW9 and from DBWa to DBWj). Configuring multiple DBWR processes distributes the work required to identify buffers to be written, and it also distributes the I/O load over these processes. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).

Based upon the number of CPUs and the number of processor groups, Oracle either selects an appropriate default setting for DB_WRITER_PROCESSES or adjusts a user-specified setting.

</quote>

The quotation suggests that by the time you have got to 8 cpus it is probably worth adding extra processes. 8 is considerably more than 2. Certainly in general you do not need one DBWR per processor. It also pays to consider what might be the factors that lead DBWR to be a bottleneck. One factor will be the speed of the disk system, if it turns out the disks are slow adding more processes to write to different disks might be a useful countermeasure; buying or configuring a more effective disk io system is probably also worth looking at. A second factor is how long the lru list actually is, and how quickly blocks are travelling through it. Excessively sizing the buffer cache - or doing repeated pointless io are good candidates for these conditions.

> About wait times - yes, you are right - but in this
> situation having one "excessive" DBWriter will let us chance
> to "catch up" with dirty blocks quicker, if need arises.

and if the need doesn't arise?

> Can you please highlight your points why you do not set
> up second (or even third) writer in this scenario?

I guess there are two aspects

1. is mostly about focussing work on where the users cares about it. If I don't have writes to disk slow enough for anyone to care, then doing them faster is pretty much pointless
2. there is the old medical adage 'first do no harm', adding extra processor overhead to a system, that may already be processor overloaded would be somewhat foolish. Adding extra processes, based on some paper specifications for the hardware is dangerously close to what Gaja Vaidyanatha calls Compulsive Tuning Disorder.

Cheers

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com