When using Oracle with a normal file system implementation, such as VxFS, certain overheads are placed on database operation. For example:

- Oracle and the file system may both buffer data (if using buffered I/O) leading to duplication of effort and inefficient usage of system memory

- Locking is performed at the file system level which, unless using concurrent I/O, normally means the use of exclusive write locks meaning that there can only be a single writing thread accessing a given file at any time. When using Oracle with large datafiles this can be extremely inefficient

To overcome these overheads the Veritas ODM implementation can be used. This involves installing relevant ODM packages/binaries, mounting the ODM device file, and linking the provided library from the Veritas implementation in place of the stub library provided by Oracle in the instances home. Further details on this are available in relevant product documentation.

Once the Veritas ODM implementation is in use the following enhancements take place:

- All I/O to Oracle datafiles going via the ODM implementation will be performed as direct I/O. This prevents buffering at the file system level. Instead all buffering is performed only within Oracle itself

- The VxFS file system does not enforce normal locking mechanisms on Oracle data files. Instead it allows the use of concurrent readers/writers. As such locking is performed internally by Oracle which then becomes responsible for enforcing data consistency when performing I/O to datafiles. As Oracle is able to lock data in a far more granular fashion than the file system (i.e. Oracle can lock on a table or row level compared with an inode) it is expected that it can achieve greater concurrency of threads and as such increase I/O throughput.

Due to these enhancements it is generally accepted that use of ODM will inprove performance of Oracle database instances in most scenarios (note that there may be some specific Oracle work loads which do not benefit from ODM however this is beyond the scope of this document). Some customers have noticed, however, that when enabling ODM they see little benefit in performance, or even some drop in performance compared with using a normal VxFS file system implementation.

A common reason for this lack of benefit is due to limited buffer space provided by Oracle when ODM is in use. When using VxFS and buffered I/O a significant proportion of system RAM is used as a file system cache or buffer. The actual size of the buffer is auto tuned depending on the amount of physical RAM installed in a machine and the I/O profile. An upper limit to buffer cache size is set via the vx_bc_bufhwm kernel viariable. To view the current size of buffer cache the vxfsstat command can be used. For example:

# vxfsstat -b /VRTS_Software

11:10:41.635 Tue 30 Nov 2010 11:10:41 GMT -- absolute sample

buffer cache statistics
     1408 Kbyte current     372792 maximum
  2447957 lookups            98.49% hit rate
     3600 sec recycle age [not limited by maximum]
 

The above example shows that the buffer cache is currently 1408Kb in size and has a maximum size of 372792Kb given the current amount of physical RAM in the machine and setting of vx_bc_bufhwm. Note that the vxfsstat command requires use of a valid VxFS mount point however when examining the buffer cache this is arbitrary as the buffer cache is global and not file system specific.

As such, any applications using a VxFS file system on this machine, can potentially consume up to 372792Kb of file system buffer space. Buffer space improves performance as it allows data to be cached in memory. If an application needs to modify this data it simply modifies and dirties the corresponding page in buffer cache/memory. At some later point in time the modified or dirty page is then written out to disk. Due to this application writes (and reads if the data is already in the buffer cache) can be performed at memory access speeds rather than waiting for disk which is many times slower to respond.

Given the above it follows that if an application is provided with very little buffer space it can only buffer a very limited amount of data in memory. This means that most read or write operations will require data which is not already present in buffer cache. As such these operations will need to wait for data to be read in to buffer cache from disk before being modified. As disk access times are many times slower than memory the average performance of I/O operations decreases significantly causing a corresponding decrease in application performance.

When using Oracle with ODM all I/O to datafiles is performed as direct I/O and as such bypasses file system buffer caches. This means that all I/O operations will complete at disk access speed and as such will be slow. To offset this Oracle data is buffered in the Oracle system global area (SGA) which is used in place of a file system cache. For example required data will be read into the buffer within the SGA, pages in the SGA modified by Oracle through normal database operations, then at some time later, changes in the SGA will be coalesced and physically written to disk via direct I/O operations. As locking in the SGA is better aligned to Oracle operation than locking in file system buffer caches it is expected that buffering and locking in this way will give an improvement in performance over file system buffering/locking.

Once the above is understood it is cleat that the amount of buffer space provided by Oracle within the SGA becomes of great importance when using ODM. Providing insufficient buffer space will cause a larger than expected number of I/O operations to go to disk as data is read into the SGA. This will causing a corresponding drop in performance of the database instance. Infact, if the buffer cache in the SGA is made small enough it is expected that enabling of ODM will cause worse performance than if using buffered I/O to a normal file system implementation.

Buffer space provided by Oracle in the SGA is primarily governed by the db_cache_size parameter. This parameter is defined as follows:

DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).

As such for database instances using a single block size, DB_CACHE_SIZE governs the entire amount of cache provided within the SGA. For database instances using multiple block sizes, caches must be provided in the SGA for non default block sizes using DB_nK_CACHE_SIZE (where n is the alternate block size in kilobytes) parameters.

Note that specifying a DB_CACHE_SIZE of 0 allows Oracle to auto tune the cache size. For Oracle 10g, when auto tuned the cache size is commonly either 48 MB or (4MB * number of CPUs * granule size), whichever is greater.

The value of DB_CACHE_SIZE can be queried as follows:

SQL> show parameter db_cache_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0
 

And modified using the 'alter system' command:

SQL> alter system set db_cache_size=1G;
System altered.

Note that the parameter can also be modified by editing the instances pfile and restarting the instance.

Whilst it is difficult to advise on a suitable setting for db_cache_size for arbitrary Oracle installations using ODM it is commonly accepted that the default auto tuned value is insufficient for most database instances and workloads. As such it is sensible to incrementally increase db_cache_size benchmarking Oracle performance with each setting to find an optimal trade off between performance of the I/O subsystem, database, and usage of system resources.

Increase the size of db_cache_size parameter within Oracle to provide more buffer space within the SGA.

Applies To

Oracle database

Data files on VxFS file system

Veritas ODM library linked in to Oracle instance and use of library has been confirmed in the Oracle alert log