Re: db_writer_processes tuning

From: Milen Kulev <makulev_at_gmx.net>
Date: Mon, 09 Feb 2009 17:54:22 +0100
Message-ID: <49905FBE.8060401_at_gmx.net>

Hello Tim,
thank you giving the correct formula. It has really been changed in 10g ind 11G.

Yes "default" doesn't necessarily mean "optimal". If (I believe) I have to change  the DB_WRITER_PROCESSES I am closely inspecting the AWR or STATSPACK reports,
watching  "% of total DB Time" column for significant "db file parallel write", "free buffer waits" and "write complete waits" in the "Top 5 Timed Events" section.
Another useful sections in AWR reports are  "Background Wait Events " -> columns
"Total Wait time" and "Avg wait (ms)" .

 Especially interesting are AWR snapshots during peak/heavy load for OLTP, nightly batch jobs in DWH databases.

I like your example below showing that
a) there is no point in using absolute values when justifying whether there enough db writer processes or not 
b)wait events, related to DBWR processes,  should consume significant amount of time in order DBWR processes to be considered for tuning.

I good (and a little bit outdated information about db writer processes and cache buffer management can be found in ML 91062.1)

Best Regards. Milen


Tim Gorman wrote:

Milen,

In Oracle9iR2, the documentation states that DB_WRITER_PROCESSES simply defaulted to "1".  For 10gR2 and 11gR1, default values set according to the formula "least(20, greatest(1, cpu_count/8))".

Using the formula you cited, 16 CPUs works out to 3 DBWn processes (i.e. round((16+7)/8,0) = 3), just FYI...

However, the "optimal" value is not necessarily the "default" value, as "optimum" certainly varies by workload and type of application, wouldn't it?  That is, if you define "optimum" as setting the quantity of a resource to a value to minimize queueing on that resource?

Wait-event analysis can help here.  Looking at a STATSPACK or AWR report during high-volume workload periods, we can look for the significance of the "write complete waits" or "free buffer waits" events, not to mention any events named "db file % write".  While high waits for such events are not proof-positive of a need to increase the bandwidth of DBWn, it is something to consider. Of course, if there are *no* significant waits on any such events, in proportion to "DB Time", then there probably isn't anything to worry about.

There is an X$ table that summarizes activity to the different DBWn processes, but the name of it has slipped into the past and I can't recall it.  So, when attempting to judge whether there are sufficient DB_WRITER_PROCESSES for the workload, I tend to simply view how "busy" the existing processes have been using the UNIX "ps" command as follows:

$ ps -eaf | grep ora_dbw | grep -v grep
  oracle 20891     1   0   Jan 16 ?          71:07 ora_dbw4_prod
  oracle 20889     1   0   Jan 16 ?         106:04 ora_dbw3_prod
  oracle 20874     1   0   Jan 16 ?          91:05 ora_dbw0_prod
  oracle 20887     1   0   Jan 16 ?          86:55 ora_dbw2_prod
  oracle 20893     1   0   Jan 16 ?          86:14 ora_dbw5_prod
  oracle 20885     1   0   Jan 16 ?         103:33 ora_dbw1_prod

Over the course of the past 24 days that the instance has been running (i.e. 35,227 mins), we can see that this 32-CPU server has racked up a total of 545 minutes of CPU time over these six (6) DBWn processes, averaging about 90 mins per process.  Now, if you look at the total possible CPU capacity of this server (35,227 mins * 32 CPUs), you get something like 1,127,264 minutes of CPU available on this server over the time that the instance has been running, and clearly 545 minutes out of 1.1m minutes (i.e. 0.05%) is not a big deal.  On this server, both "write complete waits" and "free buffer waits" typically sum to less than 1% of total "DB Time" even during peak periods observed using AWR.

For this example, we have DB_WRITER_PROCESSES set a bit higher than the default, but I conclude that it is an "optimal" setting for ensuring that nothing is waiting (unduly) on DBWn functions.

For now.

Hope this helps!

P.S. if anyone remembers the name of the X$ table that summarizes I/O totals for each DBWn process, could you post it to the list?  Thanks!!

Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim_at_EvDBT.com
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt
  

Milen Kulev wrote:

Hello Vladimir,
as fas as know, according to Oracle the optimal count of DB writer processes is
"(CPU_COUNT+7)/8". For 16 CPUs that would give you 4 DB Writer processes.
I have used this setting (also with VxFS), and there were no problems.

HTH. MILEN


Vladimir Barac wrote:

Hi, listers

 

A bit broad question… Is there a way to find out (by querying V$ views) what would be the optimal value for db_writer_processes parameter? As changing this parameter requires restart of the database, I have to provide proper justification (ITIL process) for this change.

 

Oracle 10.2.0.2, SLES 10.1 64 bit, OCFS2, db_writer_processes=1, filesystemio_options=directio, db_cache_size currently at 16GB, 4 dual core cpu server, RAM 32GB

 

Regards,

Vladimir Barac
Database administrator | Alghanim Industries
P.O. Box 223, Safat 13003, Kuwait
Tel: +965 1881 111  / ext. 2960

 

______________________________________________________________________
This e-mail message and any attachments to it are for the sole use of the intended recipients and may contain confidential and privileged information. This e-mail message and any attachments are the property of Yusuf A. Alghanim & Sons w.l.l. or any of its subsidiaries or affiliates (“Alghanim Industries”). Any unauthorized review, use, disclosure, or distribution of this e-mail message or its attachments is prohibited. Any opinions expressed in this message are those of the author and do not necessarily reflect the opinion of Alghanim Industries. If you are not an intended recipient, please notify the sender by reply e-mail and destroy all copies of the original message and any attachments.
______________________________________________________________________

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 09 2009 - 10:54:22 CST