RE: DBWR - How Many is Too Many?
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: david.barbour1@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 27 Feb 2008 14:03:06 -0500
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
==============================================================================
Other related posts:
