Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle disk I/O on Linux Tips

Oracle Database Tips by Donald Burleson


With Linux becoming the most popular OS for Oracle, many professionals have questions about how to manage disk I/O for Linux Oracle databases. 
I've devoted over a hundred pages in my book "Oracle Tuning: The Definitive Reference" to Linux disk I/O management, but we still have the issue that super-large disks will impose enqueues because the mechanical device can only relocate to a single cylinder at a time. 

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. 

David Aldridge 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:

Oracle has another approach in the Oracle 11gr2 "ASM intelligent file placement" feature. 

In intelligent file placement the data file is broken down into "hot" and "cold" disk platter areas, leveraging on the fact that the outermost sectors of a disk contain more space per revolution than the inner "cold" sectors.




Using this ASM feature you can direct Oracle to place a datafile (or ASM template) onto the "hot" area of the disk, the outermost cylinders.  See ASM intelligent file placement Tips.

Oracle 12c automatic data optimization

In a manual system, popular data ages-out and become less popular and read-only.  In this approach to data lifecycle management, aged-out popular data is automatically compressed and move to a lower-tier tertiary storage (disk or tape).

 

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.

References on Linux I/O for Oracle

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational