File location matters on
busy Oracle disks
In this blog entry
we see a great test that demonstrates that the placement of data
files on different parts of the disk impact the access speed:
So the total number of I/Os that we
can do really depends on where the data is located on the disk.
According to maxtor the average seek time for this disk is around
9.3 ms. It means that the full seek time will be around 16-18 ms.
That seems to fit. Subtract the average outside time from the
average inside/out (16.4 - 0.5) and we get 15.9 millisecond as may
be 90 percent of the full seek time.
So even for the simple ATA drives,
it is important where the data is stored.
The idea is to
place frequently-referenced “hot” data files in the middle cylinders
of the disk to minimize the “seek delay” associated with the
movement of the disk read-write heads. Back in the 1980’s you could
deliberately load hot files on the inner and outer edges of a disk,
and the read-write head movement would make the disk shake like an
out-of-balance washing machine:
Until such time that
solid state disk is cheap enough to fully cache large databases, the
Oracle DBA will still be concerned about their most critical
performance area, the disk I/O sub-system. The main points of this
article include the following:
If you are not
using RAID, the old-fashion file placement rules still apply, and
you must manually place Oracle data files across you disk spindles
to relieve I/O contention.
Using a RAID 10
approach (striping and mirroring) distributes data blocks across all
of your disk spindles, making “hot” disks a random and transient
RAID 5 is not
recommended for high-update Oracle systems. The performance penalty
from the parity checking will clobber Oracle performance.
Oracle10g continue to refine the ability to support very large RAM
data buffers with the v$db_cache_advice utility and Oracle10g
Automatic Memory Management (AMM).
disk is getting cheaper, and may soon replace traditional disk
devices. Many Oracle customers are using solid state disk for high
I/O data files such as TEMP, UNDO, and REDO files.
If disk is not
your bottleneck, improving disk access speed will not help. Be sure
to check your top-5 STATSPACK wait events to ensure that disk I/O is
your bottleneck before undergoing expensive changes to your disk I/O