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

 

 

   
  Oracle Tips by Burleson

The Problem of Duplicate RAM caches

As hardware evolved though the 1990’s, independent components of database systems started to employ their own RAM caching tools (Figure 1-7).

As this figure demonstrates, the Oracle database is not the only component to utilize RAM caching.  The disk array employs a RAM cache, the servers have a Journal File System(JFS) RAM cache, and the front-end web server also serves to cache Oracle data.

This concept is important because many enterprises may inadvertently double-cache Oracle data.  Even more problematic is the “fake” statistics reported by Oracle when multiple-level caches are employed:

Fake physical I/O times – If a disk array with a built-in RAM cache is being used, the disk I/O subsystem may acknowledge a physical write to the database even though the data has not yet been written to the physical disk spindle.  This can skew timing of disk read/write speed.

Wasted Oracle Data Buffer RAM – In systems that employ web servers such as Apache, the front-end may cache frequently-used data.  Consequently, Oracle resources may be wasted by caching data blocks that are already cached on the web server tier.

Now it is time to take a look at the best way to use SSD in an Oracle environment.  Examining the relationship between Physical Disk I/O (PIO) and Oracle Logical I/O(LIO) is a good place to start.

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 or 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 to 100 times faster than physical disk I/O.

Oracle has internal data protection mechanisms at work that cause RAM data block access to be far slower due to internal locks and latch serialization mechanisms.  This overhead is required by Oracle to maintain read consistency and data concurrency. So, if Oracle logical I/O is expensive, can this expense be avoided when data is read directly from disk?  The answer can be found in determining the most appropriate placement for SSD in an Oracle environment.

With 144 gigabyte super-large 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 first read the disk data block and then transfer it into the Oracle RAM buffer before the data is passed to the requesting program (Figure 1-8).

Since logical I/O expense is going to happen regardless of whether or not physical I/O is performed, there is valuable insight to be gained into the proper placement for SSD in an Oracle environment:

Finding the Baselines

It is critical to remember that Oracle databases are always changing. A database examined at 10:00 AM may be completely different from the same database examined at 3:00 PM.  When the performance of Oracle disk I/O is examined over time, signatures appear when the I/O information is aggregated by hours-of-the-day and day-of-the-week (Figure 1-9). 

Most Oracle professionals will use Oracle STATSPACK or Oracle10g AWR information to gather these baselines.  Once the repeating I/O trends have been identified, a broad-brush approach to the application of SSD can be used. Thus, placing the fast I/O devices where they will do the most good.

Capturing I/O information at the file level can provide insight into the best data files to place on super-fast SSD.  The reads.sql script extracts the physical read information from the Oracle 10g dba_hist_filestatxs view:

-- *************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

break on begin_interval_time skip 2 

column phyrds  format 999,999,999
column begin_interval_time format a25 

select
   begin_interval_time,
   filename,
   phyrds
from
   dba_hist_filestatxs
  natural join
   dba_hist_snapshot
;

The example that follows shows a running total of physical reads by datafile.  Note that the snapshots are collected every half-hour.  Starting from this script, a where clause criteria could easily be added to create a unique time-series exception report.

SQL> @reads 

BEGIN_INTERVAL_TIME       FILENAME                                  PHYRDS
------------------------- ---------------------------------------- -------
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF    164,700
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF    26,082
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF    472,008
                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF       1,794
                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA      2,123
                                                                         

24-FEB-04 11.30.18.296 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF    167,809
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF    26,248
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF    476,616
                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF       1,795
                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA      2,244
                                                                         

25-FEB-04 12.01.06.562 AM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF    169,940
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF    26,946
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF    483,550
                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF       1,799
                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA      2,248

Of course, with a little tweaking to the reads.sql script, reports on physical writes, read time, write time, single block reads, and a host of other neat metrics from the dba_hist_filestatxsview could be generated.

Next the existing research on SSD will be reviewed and supplemented by what other Oracle experts say about using SSD with Oracle.


The above book excerpt is from:

Oracle Solid State Disk Tuning

High Performance Oracle tuning with RAM disk

ISBN 0-9744486-5-6  

Donald K. Burleson & Mike Ault

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

  
 

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. 

Hit Counter