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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield
Sent: Thursday, February 28, 2008 2:24 AM To: greg_at_structureddata.org
Cc: david.barbour1_at_gmail.com; oracle-l_at_freelists.org 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_at_structureddata.org> 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 




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