On busy Oracle databases on a single disk spindle, the disk can shake like an out-of-balance washing machine as competing tasks enqueue for data service. There are several ways to minimize disk I/O for Oracle on Linux:
- Large data buffers - The 64-bit Linux allows for super-large data buffers. The new solid state disks provide up to 100,000 I/Os per second, six times faster than traditional disk devices.
- Multiple blocksizes - I/O segregation with multiple blocksizes (i.e. indexes on a 32k blocksize) provides additional I/O manageability. This is especially important if you are doing full-scans in Linux with multi-block reads.
- Linux Direct I/O - Always make sure that you are using direct I/O. Linux systems support direct I/O on a per-filehandle basis (which is much more flexible) with the O_DIRECT parameter. See Kernel Asynchronous I/O (AIO) Support for Linux.
Linux datafile I/O management for Oracle
Understanding the Linux I/O calls (Completely Fair Queuing (CFQ), Deadline I/O scheduling, NOOP I/O and Anticipatory I/O). This guy has a write-up on Linux kernel I/O for large Oracle systems in Linux. Also full-scan access speed is aggravated by Oracle willy-nilly block placement in Automated Storage Management (ASM) and using bitmap freelists (Automated Segment Storage Management).The problem with most large Linux Oracle databases is that the super-large disk devices have introduced seek-time latency, as the read-write heads traverse between the cylinders. See
This author also notes this seek latency issue in Linux and suggests how changing I/O drivers may be an option for very large Oracle Linux databases:
"When Oracle is performing a full table scan using parallel query it is continually issuing read requests of around 1Mb (for example) for a large set of blocks that are contiguous. Hence there ought to be little or no latency due to disk head movement.
When another parallel query slave, possibly for the very same query as the first, is also trying to retrieve a large set of contiguous data the danger is that the disk head will continually be flicking around between the two processes, incurring latency each time it does so.
The most efficient scheduling method would therefore appear to me to be one that allows the second process to wait while satisfying more requests from the first process, thus reducing the disk head movement and increasing the rate of blocks read from disk."
Seek time (read-write head movement remains the largest component of Linux I/O latency. The Oracle professional can work-around this issue by intelligently placing high I/O data files in the middle absolute track number to minimize read-write head movement, allocating "hot" data files near the middle absolute track of the disk spindle:
Finding disk I/O bottlenecks in Linux
The majority of the wait time in most large Linux Oracle databases is spent accessing data blocks. You can also run STATSPACK I/O queries to see Linux disk I/O details.
Top 5 Timed Events% Total
Event Waits Time (s) Ela Time
--------------------------- ------------ ----------- --------
db file sequential read 2,598 7,146 48.54
db file scattered read 25,519 3,246 22.04
library cache load lock 673 1,363 9.26
CPU time 44 1,154 7.83
log file parallel write 19,157 837 5.68
Far and away, the easiest way to spot hidden Linux I/O bottlenecks is with Ion, where the sources of the Linux disk I/O contention become immediately apparent. Ion is the most useful because it tracks workload-related I/O bottlenecks that are often too transient to see with scripts:
The Ion tool is amazing at spotting hidden I/O trends on Linux databases. I rarely recommend GUI tools, but Ion is one exception because it removes the tedium of running dozens of scripts to locate Linux disk I/O contention.
