RE: DBWR, Direct I/O and the Devil

Don,

Do you believe you have an I/O issue or are you just trying to "squeeze" more out of your I/O subsystem?

Having played with db_writer_processes, I haven't really seen much improvement or negative impact from increasing/decreasing them. But that is probably more due to the load (or lack thereof to notice any difference).

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor_at_ingrambarge.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Seiler
Sent: Wednesday, November 28, 2007 12:59 PM To: Oracle-L Freelists
Subject: DBWR, Direct I/O and the Devil

I'd like to lay bare some aspects of my instance and see what everyone thinks. I've recently been re-reading Kevin Closson's series on over-configuring DBWRs [0], and had some questions.

First of all, this is on Oracle RDBMS 10.2.0.2 Enterprise Edition on RHEL4. Both DB and OS are 64-bit. Processing is 4 dual-core 64-bit CPUs. Filesystem for all database files is Veritas (vxfs).

Second, based on a suggestion from a colleague, I set filesystemio_options=directio. We also mounted the vxfs drives with the convosync=direct option. However, disk_asynch_io is still true. Is there a conflict here?

Third, we have db_writer_processes=4. This was done a long time ago and hasn't been looked at since. I imagine it was done based on the "1 DBWR for every CPU" line of thinking that Kevin spotlights in his series. Our database is a hybrid of OLTP data and bulk-loaded data that is either direct-path sqlldr or INSERT/APPEND from external tables. Kevin mentioned that direct-path writes don't use the DBWR, so that this instance *might* do perfectly well with just 1 DBWR. I'm wondering if using directio is also a factor in determining the proper value of db_writer_processes.

Fourth, should I have even gone to directio in the first place? I'd like to know what people use to benchmark I/O throughput, similar to what Kevin does in his tests.

[0]

http://kevinclosson.wordpress.com/2007/08/10/learn-how-to-obliterate-pro cessor-caches-configure-lots-and-lots-of-dbwr-processes/

-- 
Don Seiler
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l




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