Oracle Performance Tuning Tips

Database block size = File system buffer size

Although there is every reason not to use buffered I/O (more) if you are constrained to do so for political reasons, it is important to ensure that the database block size matches the file system buffer size exactly. Here's why.
  1. If the database block size is smaller than the file system buffer size, then DBWn has to perform partial block writes. The problem of partial block writes is explained in our tip on using raw log files (here). Because DBWn does its work in the background and foreground processes do not normally wait for it, this is a scalability issue rather than a performance issue. That is, partial block writes make DBWn work less efficiently and so foreground processes will begin to wait for its services at lower workloads than would otherwise be the case. Performance is not affected under light workloads, but scalability is constrained. (Foreground processes are seldom directly affected by partial block writes because their writes, normally to temporary segments, are typically large relative to the database block size).

  2. If the database block size is larger than the file system buffer size, then all single block reads and writes are split into a series of distinct physical I/O operations - one for each file system buffer addressed - and these I/O operations are performed one at a time. For writes, a full rotational latency is sustained between each pair of file system buffer writes. For reads, all except the initial file system buffer read can normally be satisfied cheaply from the disk track buffers. However, the distinct contiguous file system buffer reads trigger the file system read ahead mechanism, even for random reads, causing inappropriate read ahead.

The following table shows the most common file system buffer sizes and some of the operating systems that use them. If for some reason you are using buffered I/O, then you are strongly advised to match your database block size and file system buffer size in order to avoid both of the above problems. (Windows NT/2000 is not listed in this table because it is not possible to use buffered I/O on that platform. Oracle performs direct I/O automatically.)

File System Buffer SizeOperating Systems
4KAIX, Linux
8KSolaris, HP-UX, Tru64 Unix
16KReliant Unix

If your operating system is not listed in the table above, you should be able to work out the file system buffer size by studying the ratio of blks/s to r+w/s in the sar -d output for various buffered filesystems. If so, please click on the feedback icon below and send us an email so that we can add it to the list.

Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home