Oracle I/O Slave Waits dbwr parallel DML

Oracle I/O Slave Waits

Oracle Tips by Burleson Consulting

The Oracle "I/O slave wait" is an idle event can appear in a STATSPACK or AWR report when a database is doing heavy insert or update (e.g. DML) activity.  Oracle has several mechanisms (i.e. multiple DBWR processes) that allow for multiple factotum processes (slaves) to perform disk writing:

Oracle asynchronous I/O

Oracle parallelism (i.e. parallel DML)

Oracle Asynchronous I/O

Asynchronous I/O enables write intensive processes like Oracle's DBWn to make full use of the I/O bandwidth of the hardware, by queuing I/O requests to distinct devices in quick succession so that they can be processed largely in parallel. Asynchronous I/O also allows processes performing compute intensive operations like sorts to pre-fetch data from disk before it is required so that the I/O and computation can occur in parallel.

Note:  According to MOSC, the I/O slave wait is incorrectly displayed as "other" when it is indeed an idles event, indicating that the factotum I/O slaves areidle.

The performance of asynchronous I/O is depends much on if the kernelized asynchronous I/O or threaded asynchronous I/O is used.

For kernelized asynchronous I/O, the kernel allocates an asynchronous I/O request data structure and calls an entry point in the device driver to set up the asynchronous I/O request. The device driver then queues the physical I/O operation and returns control to calling process. When the physical I/O operation has completed, the hardware generates an interrupt to a CPU. My other notes on Oracle asynchronous I/O includes:

Oracle Direct I/O - bypass Disk IO buffer

Automated Storage Management

Tuning multi-block I/O in Oracle

Oracle I/O Reports

Oracle RAC - Clustered files systems & raw devices

ORA-27070: skgfdisp: async read/write failed

Creating Oracle I/O slaves

If you implement database writer I/O slaves by setting the DBWR_IO_SLAVES parameter, you configure a single (master) DBWR process that has slave processes that are subservient to it.  In addition, I/O slaves can be used to "simulate" asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently. Database I/O slaves provide non-blocking, asynchronous requests to simulate asynchronous I/O.

Chris Foot notes about I/O factotum processes and multiple db_writer_processes:

You can't activate both multiple DBWRs and I/O slaves. If both parameters are activated, DBWR_IO_SLAVES will take precedence.  To determine whether to use multiple DBWn processes or database slaves, follow these guidelines:

For write intensive applications that also have a large data buffer cache (100,000 and up), configure DB_WRITER_PROCESSES

For applications that are not write intensive and run on operating systems that support asynchronous I/O, configure DBWR_IO_SLAVES

If the operating system does not support asnychronous I/O, use DBWR_IO_SLAVES

If your server only has one CPU, use DBWR_IO_SLAVES, DBWR processes are CPU intensive

Oracle Parallel DML

Oracle parallel DML is very useful when you have an SMP server (lots of independent CPU processors, see your cpu_count) and multiple I/O channels on multiple disks.  If you can bypass disk I/O bottlenecks by spreading data across multiple disks and controllers, then parallel DML can greatly improve data loading speed using tools like SQL*Loader (sqlldr).  Oracle parallel DML does this by allocating multiple processes, each simultaneously performing DML (updates, inserts).  You can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL "APPEND" option.  Mark Bobak notes that if you submit parallel jobs to insert against the table at the same time, using the APPEND hint may cause serialization, removing the benefit of parallel jobstreams.

My notes on Oracle parallel DML includes:

Oracle DML statements

Oracle import speed performance

Oracle cpu_count parameter

For more details, see my new book "Oracle Tuning: The Definitive Reference".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.