Oracle Server Enterprise Edition Technical Forum Displayed below are the messages of the selected thread. Thread Status: Closed From: Deborah Bux 30-Sep-99 18:26 Subject: Async IO and Multiple DB Writers on Sun Solaris RDBMS Version: 8.0.4.3 Operating System and Version: Sun Solaris 2.6 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): RDBMS Product Version: Async IO and Multiple DB Writers on Sun Solaris Since we are running Oracle 8.0.4.3 on Solaris 2.6, we are by default using async IO. Could we benefit by also using multiple DBWRs or DBWR_IO_SLAVEs? What is the difference between using multiple DBWRs or DBWR_IO_SLAVEs? Is there a white paper on these issues? Thanks, Debbie -------------------------------------------------------------------------------- From: Mark Powell 30-Sep-99 21:56 Subject: Re : Async IO and Multiple DB Writers on Sun Solaris You should not need multiple DB writers with async I/O. I believe that it is technically possible to start more than one when you are using async, but I have read you should not do so. I did not know async was automatic on Solaris. I have a reference book, Oracle and UNIX Performance Tuning, by Ahmed Alomari of Oracle Corp. which is what I based the first paragraph on that lists init.ora parameters to use async (but it was written in ver 7 days)> -------------------------------------------------------------------------------- From: Ranko Mosic 01-Oct-99 14:45 Subject: Re : Async IO and Multiple DB Writers on Sun Solaris There is a bug for 805 and Solaris 2.6. Oracle background processes are using tons of CPU is asynch is enabled. Workaround is to disable asynch and turn multiple dbwriters on. Ranko. -------------------------------------------------------------------------------- From: Chris Daw 01-Oct-99 17:17 Subject: Re : Async IO and Multiple DB Writers on Sun Solaris Can we have someone from Oracle tell us: 1) Whether using multiple DBWRs with async I/O on Solaris is unncessary, harmful, or useful? 2) More about the putative bug associated with using async I/O on Solaris with Oracle 8.0.5. Is there any documentation on this bug? Is it fixed in a patch? -------------------------------------------------------------------------------- From: Oracle, Helen Schoone 01-Oct-99 19:03 Subject: Re : Async IO and Multiple DB Writers on Sun Solaris Hi. I am not aware of a white paper, but the new parameters: DB_WRITER_PROCESSES, DISK/TAPE_ASYNCH_IO, AND DBWR_IO_SLAVES are documented in the Solaris Administrators Reference Guide for 8.0.5. I believe the documentation is incorrect (old) in the 8.0.4 version. Asynchronous I/O allows processes to proceed with the next operation without having to wait after issueing a write and therefore improves system performance by minimizing idle time. Solaris supports async I/O for both raw and filesystem datafiles. I/O Slaves are specialized processes whose only function is to perform I/O. In Oracle8, the file I/O interface was reworked to provide non-blocking I/O calls for its clients. I/O slaves can be used to provide non-blocking I/O requests even if the platform does not support asynchronous I/O. In Oracle7, "I/O slaves" were implemented specifically for DBWR in the form of multiple DB_WRITERS. The need existed to make the feature more general. This prompted the design of Oracle8 I/O slaves, which can be used theoretically by any server process. However, access to I/O slaves is restricted to DBWR, LGWR, ARCH and processes performing backups. It is not recommended to utilize I/O slaves if you have enabled asynchronous I/O. This can cause DBWR to become a bottleneck. DB_WRITER_PROCESSES controls the number of 'DBWR' gatherer processes being used by an instance. DBWR operates in a batch-oriented mode: it gathers dirty buffers into a batch and then issues the writes for all buffers in the batch. The writes will use asynchronous I/O if possible. However, you cannot have multiple DB_WRITER_PROCESSES if using DBWR_IO_SLAVES. Setting DB_WRITER_PROCESSES parallelises both the gathering and writing of buffers thus parallelising more of the DBWR activity. Multiple DB_WRITER_PROCESSES should be able to produce higher throughput that one DBWR with multiple IO slaves. The bug regarding an increase in CPU when using async I/O when running Solaris 2.6 is a Solaris 2.6 async I/O bug (ref. Sun bug bug 4222164). Oracle, however, has worked around the Solaris issue in 8.1.5. I hope this helps. -------------------------------------------------------------------------------- From: Deborah Bux 04-Oct-99 22:22 Subject: Re : Re : Async IO and Multiple DB Writers on Sun Solaris Does the SUN Solaris 2.6 async I/O bug(4222164)also apply to Oracle version 8.04? Should database Async IO be turned off if we are running solaris 2.6? How can we tell if this bug is causing us problems with CPU usage? -------------------------------------------------------------------------------- From: S. Houston 05-Oct-99 17:42 Subject: Re : Re : Re : Async IO and Multiple DB Writers on Sun Solaris We were loading a large amount of data to a table with a number of indexes on it, and could see that DBWR was choking (we used Quest Software's Instance Monitor tool). I didn't know about the bug in Solaris 2.6 -- we are on 2.7, and Oracle 8.0.5.0 -- but I turned off Asynch I/O and set up 3 DBWR slaves (one per disk), and cut about 1/3 of the time off the load. Until I heard about the bug, I thought the improvement was due to the use of multiple DBWR I/O slaves. Now I'm not sure. But I can tell you that if you DON'T turn off Async I/O the DBWR's WILL cause a bottleneck. (To turn it off, set DISK_ASYNCH_IO = FALSE in the init.ora. It's set to TRUE by default). I don't know if this helps or not. The documentation isn't very clear on whether or not to use them, and how. I just experimented until I got something that worked. -------------------------------------------------------------------------------- From: Oracle, Helen Schoone 05-Oct-99 19:51 Subject: Re : Re : Re : Async IO and Multiple DB Writers on Sun Solaris Hi. It is my understanding that it would effect any version of Oracle running on 2.6. The bug is that the Solaris async library appears to use a lot more cpu under Solaris 2.6 when doing operations against files on filesystems than in 2.5.1. It was reported to Oracle that DBWR and/or LGWR consumes more CPU after upgrade to Solaris 2.6. If you are encountering an increase in CPU usage which is causing a performance impact, then you may consider turning off async I/O. --------------------------------------------------------------------------------