Re: db writer working data sets

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 2 Jun 2009 22:35:00 -0700
Message-ID: <a9c093440906022235p4ce46ad3gb631c9eddd8ea83b_at_mail.gmail.com>

Unless "free buffer waits" is in your top 5 wait events in AWR/Statspack, you dont need more db writers. In almost all cases, the default value for db_writer_processes is sufficient. http://download-west.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm#sthref759

Tuning "buffer busy waits" is different. You need to find out what what type of buffer is being waited for and triage why. http://download-west.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm#sthref657

As a data point, the recent TPC-C that Oracle published http://tpc.org/tpcc/results/tpcc_result_detail.asp?id=109052101 used a db server that has 72GB RAM, multiple block caches (2k/8k/16k) and 1 CPU (quad core Intel 5520 "Nehalem") and did over 230k tpmC using a single db writer.

On Tue, Jun 2, 2009 at 2:50 PM, Taral Desai <taral.desai_at_gmail.com> wrote:
> Reason behind this we are using 12 dbwriter process for we have 12gb of sga
> and 10gb of pga. Some time we are getting buffer busy waits. So, need to
> find that we are using multiple buffer pools. So, if it is from there then i
> can suggest management to use only default block size and not multiple. I
> already know that multiple buffer pool have overhead
> of managing one more memory structure so need to investigate and learn from
> you.
> My idea is to reduce this and then start adding processes if necessary.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 03 2009 - 00:35:00 CDT