Re: DBWR - How Many is Too Many?

Thanks Robyn. We also archive extensively. The data in the system represents two years. Unfortunately - or fortunately as the case may be - business is growing, and we're expanding both in volume and in the functionality we're using within SAP (implementing SD in several of our divisions).

I have seen the reference you cited below, among a lot of other stuff I've reviewed over the past several days. I don't think it's the database. But the systems folks insist it's the database (sigh) since they're not seeing any paging. My response is that there is no reason for kernel processes to be using more CPU than user processes.

On 2/27/08, Robyn <robyn.sands_at_gmail.com> wrote:
>
> 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_at_gmail.com>
> 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_at_credit-suisse.com> 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_at_credit-suisse.com
> > > www.credit-suisse.com
> > >
> > >
> > > ------------------------------
> > > *From:* oracle-l-bounce_at_freelists.org [mailto:
> > > oracle-l-bounce_at_freelists.org] *On Behalf Of *David Barbour
> > > *Sent:* Wednesday, February 27, 2008 1:53 PM
> > > *To:* oracle-l_at_freelists.org
> > > *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
>

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