How DB_FILE_DIRECT_IO_COUNT Initialization Parameters Affect Oracle Direct Path Read Performance

The DB_FILE_DIRECT_IO_COUNT initialization parameter can impact the direct path read performance. It sets the maximum I/O buffer size for direct reads and writes operations. Up to Oracle8i Database, the default value on most platforms is 64 blocks. So if the DB_BLOCK_SIZE is 8K, the maximum I/O buffer size for direct reads and writes operations is 512K. This number is further subject to hardware limits.

The DB_FILE_DIRECT_IO_COUNT parameter is hidden in Oracle9i Database, and the value is expressed in bytes instead of blocks. The default value in Oracle9i Database is 1 MB. The actual direct I/O size depends on your hardware configuration and limits.

You can discover the actual direct read I/O size in three ways:

• Trace the Oracle session that performs direct reads operations using the trace event 10046 at level 8. The P3 parameter indicates the number of blocks read. Based on the following example, the direct path read I/O size is 64K since the block size is 8K. Alternatively, you can query the V$SESSION_ WAIT view for the P3 value of the direct path read event.

• Trace the Unix session that performs direct reads or writes operations using the operating system trace facility such as truss, tusc, trace, or strace. The snippet of the truss report from an Oracle9i Database reveals the direct I/O size is 65536 bytes or 64K:

• Enable the debug information for the session that performs direct I/O operations using the trace event 1 0357 at level 1. Example: alter session set events ‘10357 trace name context forever, level 1’. The snippet of the trace file is provided here:

In the preceding example, the trace file belongs to query slave #0 (P000). There are 32 I/O slots available for the direct read operation (slot_cnt=32). A slot is a unit of I/O, and each slot is 65536 bytes (slot_size=65536). Asynchronous I/O is enabled during the read operation (async=1). The query slave reads data file #4 (afn=4). The number of blocks read is 8 (cnt=8). Since the block size is 8K, this translates to 65536 bytes.

In this case, the direct I/O slot size prevents the process from achieving the full 1 MB, which is the default limit of the _DB_FILE_DIRECT_IO_COUNT parameter. The slot size can be modified by event 1 0351. The number of slots can be modified by event 10353.

CAUTION

The preceding information gives you a sense of the direct I/O throughput in your system. Don’t simply change the default slot size or the number of direct I/O slots. You need to know your hardware limits before doing so. Besides, you should focus on optimizing the application and SQL statements first.

Lastly, in Oracle8i Database, direct reads can also be enabled for serial scans with the _SERIAL_DIRECT_READ initialization parameter. Earlier releases may do the same by setting event 10355. Doing so will cause data from full scans to be read into the session’s PGA and not shared with other sessions. This can sharply increase memory usage.