Oracle Performance Tuning Tips

Avoid buffered I/O

Buffered datafile I/O wastes copious memory because Oracle data blocks are cached both in the SGA and in the file system buffer cache with substantial overlap between the two sets of blocks that are cached.

The file system cache should be used to buffer non-Oracle I/O only. Using it to attempt to enhance the caching of Oracle data just wastes memory, and lots of it. Oracle can cache its own data much more effectively than the operating system can. Oracle has a sophisticated touch count based cache replacement algorithm that is sensitive to both the frequency and recency of data usage. Oracle also avoids caching data that is unlikely to be, or will never be reused, and DBAs can fine tune Oracle's caching decisions by allocating segments to the KEEP and RECYCLE buffer pools. There is also a big saving in CPU time and elapsed time (approximately 50%) when getting data from the Oracle buffer cache rather than the filesystem buffer cache.

By switching to raw or direct I/O and by giving Oracle the memory that would otherwise be used by the operating system to cache Oracle data, a much larger working set of data can be cached, and a much higher cache hit rate can be sustained with obvious performance benefits. And when physical I/O is required, the CPU cost of performing that I/O is reduced because the data is not first copied to file system buffers. Also, the file system cache is not flooded with Oracle data, so it can cache non-Oracle data more effectively. Switching to raw or direct I/O also eliminates the risk that intensive database I/O might trigger paging storms on systems with a paged buffer cache, and prevents performance problems due to a mismatch between the database block size and file system buffer size (more).

Buffered I/O can be avoided either by using raw I/O or by using direct I/O. Raw is preferable for performance critical databases for the several reasons. The following are the most important.

  1. Kernelized asynchronous I/O is only available against raw devices and Quick I/O files, and is significantly more efficient and scalable and makes better use of the physical I/O bandwidth than either threaded asynchronous I/O or I/O slave processes (more).

  2. File system read/write locks are generally used for direct I/O despite that they are not logically required. The only exception is direct I/O on Solaris UFS. Using direct I/O on other file systems allows unnecessary contention for these locks. This can degrade performance significantly, particularly in write intensive environments. This risk is not present with raw I/O.

Please note that switching to unbuffered I/O on systems that perform repeated full table scans of the same set of medium sized tables that would otherwise be fully cached by the operating system can cause a drop in performance unless the target tables are also allocated to the KEEP buffer pool.

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