Re: DBWR - How Many is Too Many?

David,

I would suggest that you check SAP documentation and Oracle's AIX specific
documents for recommendations on db_writers and async_io before making
changes. Several things came to mind after reading your post and Paul's
question:

1.  I used multiple db writers for our SAP system due to issues with async
i/o on HP-UX.  You don't have these same issues on AIX and thus, multiple
db_writers may not be your best answer.

2.  Our SAP database is one third the size of the database you've described
(we archive a lot of data with SAP archive tools). We used mirrored drives
for all data files, and data files were spread over about 65 data file mount
points.  Writes on RAID-5 will be your biggest issue and SAP is write
intensive.

3.  Still suggest that you gather system stats to get some numbers on read
and cpu speeds.

Here's a link to Oracle's recommendations for 9i on AIX:

http://download.oracle.com/docs/html/A90347_02/appa_aix.htm#631246

Robyn


On Wed, Feb 27, 2008 at 2:24 PM, David Barbour <david.barbour1@xxxxxxxxx>
wrote:

> Yes.  Identical SAN - IBM DS4800.  Only 4 Oracle datafile filesystems.
> Size of the database (5.4TB) necessitated we move it to accomodate
> growth.  Split index and data files, took undo and temp and put them in
> seperate filesytems too.  I actually expected performance to improve.
>
> On 2/27/08, Baumgartel, Paul <paul.baumgartel@xxxxxxxxxxxxxxxxx> wrote:
> >
> >  One question:  were you on RAID 5 previously?
> >
> >
> > *Paul Baumgartel*
> > *CREDIT SUISSE*
> > Information Technology
> > Prime Services Databases Americas
> > One Madison Avenue
> > New York, NY 10010
> > USA
> > Phone 212.538.1143
> > paul.baumgartel@xxxxxxxxxxxxxxxxx
> > www.credit-suisse.com
> >
> >
> >  ------------------------------
> > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *David Barbour
> > *Sent:* Wednesday, February 27, 2008 1:53 PM
> > *To:* oracle-l@xxxxxxxxxxxxx
> > *Subject:* DBWR - How Many is Too Many?
> >
> > We recently moved our database to a new SAN.  Performance has just
> > tanked.  Here's the environment:
> > AIX5.3L
> > Oracle 9.2.0.7
> > SAN - IBM DS4800
> >
> > We've got 8 filesystems for Oracle data files.  Redo, Archive, Undo and
> > Temp are all on seperate disk/filesystems from the data files.
> >
> > All the Oracle datafiles are on RAID5 LUNs with 12 15K RPM 73 (68
> > usable) GB drives.  SAN Read and Write Caching are both enabled.
> >
> > A statspack (generally for any given interval - this was for a period of
> > "light" processing) shows me our biggest hit is:
> > Buffer wait Statistics for DB: PR1  Instance: PR1  Snaps: 12609 -12615
> > -> ordered by wait time desc, waits desc
> >
> >                                  Tot Wait    Avg
> > Class                    Waits   Time (s) Time (ms)
> > ------------------ ----------- ---------- ---------
> > data block             278,194     20,811        75
> >
> > sar is scary (just a small portion)
> >
> > AIX r3prdci1 3 5 00CE0B8A4C00    02/27/08
> >
> > System configuration: lcpu=8
> >
> > 00:00:00    %usr    %sys    %wio   %idle   physc
> > 02:15:01      19      19      42      19    4.00
> > 02:20:00      21      25      40      14    4.00
> > 02:25:00      19      18      43      20    4.00
> > 02:30:00      18      18      43      21    4.00
> > 02:35:00      20      24      40      16    4.00
> >
> > We're running JFS2 filesystems with CIO enabled, 128k element size on
> > the SAN and AIO Servers are set at minservers = 220 and maxservers = 440
> > We've got 32GB of RAM on the server and 4 CPUs (which are dual core for
> > all intents and purposes - they show up as eight).  We're running SAP which
> > has it's own memory requirements.  I've configured my SGA and PGA using
> > Automatic Memory Management and the SGA currently looks like:
> > SQL> show sga
> >
> > Total System Global Area 1.0739E+10 bytes
> > Fixed Size                   757152 bytes
> > Variable Size            8589934592 bytes
> > Database Buffers         2147483648 bytes
> > Redo Buffers                1323008 bytes
> >
> > filesystemio_options = setall
> >
> > I'm thinking the data block waits is the result of  too many modified
> > blocks in the buffer cache.  Solution would be to increase the number of
> > db_writer_processes, but we've already got 4.  Metalink, manuals, training
> > guides, Google, etc.  seem to suggest two answers.
> >
> > 1.  One db writer for each database disk - in our case that would be 8
> > 2.  CPUs/8 adjusted for multiples of CPU groups - in our case that would
> > be 4
> >
> > Any thoughts?
> >
> > ==============================================================================
> > Please access the attached hyperlink for an important electronic 
> > communications disclaimer:
> >
> > http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
> >
> > ==============================================================================
> >
> >
>


-- 
I may not have gone where I intended to go, but I think I have ended up
where I needed to be.
Douglas Adams

Other related posts: