Tuesday, June 3, 2008

OS Param Tuning for Oracle DB : FS and I/O

Operating system performance issues commonly involve

  1. process management
  2. memory management and
  3. scheduling.

 

If you have tuned the Oracle instance and still need to improve performance:

  1. verify your work or try to reduce system time.
  2. Ensure that there is
    • enough I/O bandwidth,
    • CPU power,
    • and swap space.

Do not expect,however, that further tuning of the operating system will have a significant effect on application performance.

Operating System Caches
Operating systems and device controllers provide data caches that do not directly
conflict with Oracle cache management. Nonetheless, these structures can consume
resources while offering little or no benefit to performance.

This is most noticeable on a UNIX system that has the database files in the UNIX file store : by default, all database I/O goes through the file system cache.

Direct I/O: On some UNIX systems, direct I/O is available to the filestore.

Direct I/O allows the database files to be accessed within the UNIX file system, bypassing the file system cache.

Direct I/O :

  1. Saves CPU resources and
  2. allows the file system cache to be dedicated to non-database activity, such as program texts and spool files.
This problem does not occur on Windows. All file requests by the database bypass the caches in the file system.


Although the operating system cache is often redundant because the Oracle buffer
cache buffers blocks, there are a number of cases where Oracle does not use the Oracle buffer cache.

In these cases, using direct I/O which bypasses the Unix or operating
system cache, or using raw devices which do not use the operating system cache, may yield worse performance than using operating system buffering. Some examples
include:
■ Reads or writes to the TEMPORARY tablespace
■ Data stored in NOCACHE LOBs
■ Parallel Query slaves reading data
You may want a mix with some files cached at the operating system level and others
not.

FILESYSTEMIO_OPTIONS Initialization Parameter :
You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or
disable asynchronous I/O or direct I/O on file system files.

This parameter is platform-specific and has a default value that is best for a particular platform. It can be dynamically changed to update the default setting.


FILESYTEMIO_OPTIONS can be set to one of the following values:
■ ASYNCH: enable asynchronous I/O on file system files, which has no timing
requirement for transmission.
■ DIRECTIO: enable direct I/O on file system files, which bypasses the buffer
cache.
■ SETALL: enable both asynchronous and direct I/O on file system files.
■ NONE: disable both asynchronous and direct I/O on file system files.

No comments:

Post a Comment