Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Tips

Why is Oracle Logical I/O so Slow?

Disk latency is generally measured in milliseconds while RAM access is expressed in nanoseconds.  In theory, RAM is four orders of magnitude, 10,000 times, faster than disk; however, this is not true when using Oracle.  In practice, logical I/O is seldom more than 1,000 times faster than disk I/O.  Most Oracle experts say that logical disk I/O is only 15 times to 100 times faster than a physical disk I/O.

 

Oracle has internal data protection mechanisms that cause a RAM data block access, a consistent get, to be far slower due to internal locks and latch serialization mechanisms.  This overhead is required in order to maintain read consistency and data concurrency.

 

If Oracle logical I/O is expensive, can this expense be avoided by reading directly from disk?  The answer to this question is important to the discussion about the most appropriate placement for SSD in an Oracle environment.

 

There are also issues associated with super large disks.  With 144 gigabyte disks becoming commonplace, I/O intensive databases will often see disk latency because many tasks are competing to read blocks on different parts of the super large disk. 

 

An Oracle physical read must read the disk data block and then transfer it into the Oracle RAM buffer before the data is passed to the requesting program as shown in Figure 14.10.

 

Figure 14.10 - Physical reads include logical I/O latency

 

So if the DBA accepts that LIO expense is going to happen regardless of whether or not a PIO is performed, valuable insight can be gained into the proper placement for SSD in an Oracle environment:

 

In the next section, information will be presented on the KEEP and RECYCLE data buffers and how objects are selected for inclusion.

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

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

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.