RE: DBWR - How Many is Too Many?

Combined with *not* having high free buffer waits, this all makes sense that
additional dbwriters won't help. From the OS level if you run ps, you'll
likely see that one dbwr is pretty busy, another may be a little busy, and
the last two nearly never get work. If they are all equally busy at the same
rate as (runnable jobs)/#CPUs then it is possible adding more could achieve
increasing the fraction of your machine's CPU devoted to scanning for dirty
buffers in the SGA cache and to writing buffers, but this would only
marginally increase write throughput if you are missing opportunities to
shove data at your disk farm that don't queue against your existing write
load. Since you are not apparently waiting for free buffers, it seems you
already have plenty. In the case where one is pretty busy and another a
little busy and two idle-ish, having the extra two only costs you their
memory footprint and possibly a tiny amount of extra context switching, so
you probably don't need to eliminate them. In the case where they are
equally busy, you might actually help yourself by reducing the number of
writers, because there is a chance you will use less of the available CPU
for wait i/o without actually reducing the disk throughput. If you start to
get free buffer waits, put them back. If they are equally loaded I
personally would probably go down to 1 just to see.

 

Now I think that (in combination with this very fine thread) answers your
headline question: You have too many DBWRs if they are costing you resources
you do not have in surplus without gaining you throughput. Unless I missed
something there is no evidence that you are short on memory, but you might
be wasting some CPU by creating an artificial competition that is not needed
to free buffers.

 

Moving on to speculations about solving your problem, you mentioned that
you've got 12 drives per LUN. Are they 12 whole drives per LUN, or in order
to give you the separate LUNs for various purposes did the SAN group carve
up pieces of disks into different LUNs that compete with each other, making
the new SAN, possibly with a smaller number of huge drive, look like your
old system's 73 GB pieces. Is the block size matched correctly to mostly
have to hit only one drive on a write? Is the write cache to the RAID5
(www.baarf.com <http://www.baarf.com/> ) being overrun now when in the past
it was not? Is there a write confirmation on the SAN turned on (or not
turned off) that you were living without before? Are the drives in a LUN
configured so they queue against each other within the array's controller
communications?

 

Finally, coming to the end of my off the top of my head list of things I'd
give a quick check, is the wait i/o on your system actually from the
dbwriters? From the database you've also got the log writer and the archiver
and you didn't mention any data guard issues, but you've probably also got
sys admins practicing backup and recovery using spare space on your disk
farm. It is a NEW SAN, right?

 

What exactly tanked regarding your performance? Is that actually related to
the high wait i/o that jumped out of the statistics at you?

 

I hope these musings help. Please discard the ones that are irrelevant to
your actual situation and forgive me if I missed an excluding fact in the
existing thread.

 

Regards,

 

mwf

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Niall Litchfield
Sent: Thursday, February 28, 2008 2:24 AM
To: greg@xxxxxxxxxxxxxxxxxx
Cc: david.barbour1@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: DBWR - How Many is Too Many?

 

Great points both,

I'd also look at the i/o figures in your statspack report, I can't remember
if 9i gives the histograms but it certainly will give you the average IO
times experienced by your database. Often times SAN people can't/don't
measure time but utilisation. 

Niall




On Wed, Feb 27, 2008 at 9:49 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:


If you are experiencing high wait IO (>40% is high), then what benefit is
there to potentially do more IO, by adding dbwrs? 



The other observation I would make is that if you changed the SAN, and not
the database, and it worked ok before, then in all likelihood it is not a
database problem.  No?



-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: