Oracle Performance Tuning Tips
Use raw tempfiles
Oracle does not format every block of a tempfile when it is created.
Instead, Oracle just writes the file header block and space management bitmaps and then seeks to the end of the file to format the last block.
This is why tempfile creation is so quick by comparison with datafile creation.
Almost all of the blocks of the new tempfile are left unformatted.
Under most file systems this results in the creation of a sparse file.
No storage space is allocated to the unformatted blocks.
File system space is only allocated as required when data is actually written to the tempfile blocks.
If file system space is oversubscribed, it is possible for Oracle to get an ENOSPC error when attempting to write to a sparse tempfile.
If so, a fatal ORA-07248 error will be returned.
This risk can be eliminated by manually converting sparse tempfiles to dense files.
This can be done by shutting down all instances and
copying the tempfiles using an operating system file copy utility that does not preserve the holes in sparse files.
Alternatively, the risk can be eliminated by using raw tempfiles.
Raw tempfiles are preferable because of the following issues with I/O to file system based tempfiles.
Because each Oracle process writes to and reads from the tempfiles directly,
it is common for tempfiles to sustain relatively high write concurrency.
This makes tempfiles particularly prone to contention for file system read/write locks (inode locks).
Some operating system performance analysis tools (such as glance under HP-UX) identify inode lock contention explicitly.
Otherwise, the problem must be inferred from the particularly poor response times for concurrent writes,
frequently with only modest physical disk utilization.
Raw I/O eliminates file system read/write locks, so no such contention is possible.
Tempfile I/O operations are typically large.
Large I/O operations can be performed very efficiently using raw or direct I/O.
But much of this efficiency is lost if the tempfile I/O is buffered,
because large buffered I/O requests are fragmented and serviced as a series of distinct single-buffer physical I/O operations.
This is particularly inefficient for large writes, because a full rotational latency is sustained between each pair of component physical writes.
Oracle makes heavy use of asynchronous I/O against tempfiles.
Therefore it is important that an asynchronous I/O facility be available, and that it be efficient.
Raw tempfiles normally enable efficient kernelized asynchronous I/O,
whereas file system based tempfiles only support threaded asynchronous I/O, if that.
These performance problems cannot be addressed by using Quick I/O, as in the case of datafiles,
because Veritas do not support the use of Quick I/O for tempfiles.
Raw tempfiles can be used even in environments that do not have any facility to backup raw disk,
because tempfiles should not be backed up.