Tuning Oracle Database 10g for ext3 file systems

리눅스의 경우 OS Buffer Cache 때문에 Oracle SGA의 Buffer Cache와 중복되게 사용된다.
결국 메모리가 비효율적으로 사용된다. 이러한 이유 때문에 오라클에서 옵션을 따로 만들었나보다.
아래 SQL 한번 날리구 재시작하면 적용되니 좋네..
실제 테스트해봐도 성능향상 확실하게 되고...


ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
 

Introduction:

by Joseph Salisbury and D. John Shakshober

Red Hat® Enterprise Linux® 4 enables Oracle® Database 10g file system applications to perform within 3% of raw I/O due to enhancements to the ext3 file system and through the support of both Direct and Asynchronous I/O. Since Red Hat Enterprise Linux 2.1, the use of Direct I/O (DIO) has been an important ext file system feature for large Oracle database applications where the database sizes greatly exceed the amount of physical memory available on the servers.

The Linux 2.6 kernel implemented in Red Hat Enterprise Linux 4 continues to support Direct I/O to ext file systems and also includes scalability changes for Asynchronous I/O (AIO), which allows the simultaneous use of AIO and DIO to ext3 files for database use. This combination allows ext3 files system databases to scale up to CPU counts previously only considered for raw I/O while providing the manageability of a file system to ease system administration task like copying or moving files, backup to tape, and file system and volume snap shots.

Traditional Oracle users setup raw devices and/or volume managers with raw devices for peak Oracle performance. Red Hat Enterprise Linux 4 supports this by using the raw command to single disks, md stripes, or LVM2 volumes. For example:

	raw /dev/raw/raw1 /dev/sda
	raw /dev/raw/raw2 /dev/md1
	raw /dev/raw/raw3 /dev/vol1
	

Oracle Database 9i and Oracle Database 10g support a variety of file systems under Linux to offer improved manageability, exposing the database files to the operating system for easy manipulation of files (such as moving, copying, backing up and restoring files for archival purposes, or when altering or expanding underlying storage). In doing so, most file systems implement a page or file cache which caches file I/O in the systems memory. This works very well for frequently accessed data for problem sizes that fit within the memory size of the systems.

To cache or not to cache—that is the question

Oracle, by design, uses memory for its database cache. Large shared memory segments are allocated at database startup for Oracle's Shared Global Area (SGA). Oracle Database Administrators (DBAs) adjust and tune the SGA for optimal performance of their databases. For frequently accessed database files, Oracle is already caching file I/O and handling data coherency within the database architecture. If the file system is also caching I/O memory, performance can actually suffer by "double caching" file I/O. For large databases, which greatly exceed the total memory of a system or for data access patterns with very little data re-use, performance can suffer.

Red Hat Enterprise Linux 2.1 and higher support the use of Direct I/O to the ext3 file system by opening the file with the O_DIRECT flag, a POSIX-compliant specification. Oracle can disable this "double caching" by providing init.ora options for opening the file with direct access to storage using the O_DIRECT flag upon opening the fil

	filesystemio_options="DirectIO"
	

Figure 1 shows the comparison of raw versus ext3 file system with and without the use of Direct I/O for a 40 GB OLTP database and for a 100 GB decision support query with Oracle Database 10g 10.0.1.3.

Figure 1. Oracle Database 10g performance on Red Hat Enterprise Linux 4 U2

In addition, Red Hat Enterprise Linux 2.1 and higher have supported for Asynchronous I/O (AIO) for systems with large I/O capabilities to optimize the concurrency of queuing multiple I/O requests to the storage device, allowing the application code to continue processing until the point where it simply must wait for the I/O requests to complete. Asynchronous I/O will guarantee integrity at I/O completion. This feature has been improving in Linux kernels between 2.4 and 2.6 kernels and has been supported for both raw and ext3 file systems.

Oracle Database 9i and Oracle Database 10g supports Asynchronous I/O to file systems using the following init.ora parameter:

	filesystemio_options="Asynch"
	

With Red Hat Enterprise Linux 4, ext3 now supports both Direct I/O and Asynchronous I/O simultaneous, giving it the full capabilities of raw, while maintaining file system semantics.

Oracle Database 9i and Oracle Database 10g support both Direct and Asynchronous I/O using:

	filesystemio_options="SetAll"
	

Figure 2 shows that an Oracle Database 10g R1 running OLTP transactions to ext3 file systems can gain 1.74x using Direct I/O and up to 2x gain combining with Asynchronous I/O over synchronous non-direct I/O. Note: Red Hat Enterprise Linux 4 vs Red Hat Enterprise Linux 3 gained performance from enhancements in ext3 block reservations.

Figure 2. Oracle Database 10g with OLTP performance gain on Red Hat Enterprise Linux 3 and 4

Summary

Red Hat Enterprise Linux 2.1 and higher support ext file systems for Oracle users. This includes support for performance features like Direct I/O which benefits database sizes which exceed system memory. Asynchronous I/O was designed initially for raw I/O to allow for application concurrency on servers with larger I/O subsystems with raid-based UltraSCSI controllers or Fiber Channel SANs. Enhancements to AIO in Red Hat Enterprise Linux 4 allow AIO to be used with DIO to an ext3 file system. Offering these capabilities within file systems allows Oracle Database 9i and Oracle Database 10g users to maintain 97% of raw I/O performance when implementing their databases on a standard ext3 file system.

About the authors

Joe Salisbury is a Performance Engineer for Red Hat in Westford MA. Joe has an expertise in Oracle and database performance. Prior to Red Hat, Joe was a Performance Engineer for Sun Microsystems, focusing on the publication of industry standard and Oracle Application benchmarks.

D. John Shakshober is a Consulting Engineer for Red Hat in Westford, MA focusing on kernel and benchmark performance. These measurements are part of a Red Hat study on how to deliver peak performance for real customer environments. John has an M.S. in Electrical Engineering from Cornell University and a B.S. in Computer Engineering from Rochester Institute of Technology.