||Oracle Tips by Burleson
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
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.
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
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
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
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
-- 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 email@example.com
begin_interval_time skip 2
column begin_interval_time format a25
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.
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
24-FEB-04 18.104.22.1686 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
25-FEB-04 12.01.06.562 AM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
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
The above book excerpt is from:
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
Donald K. Burleson & Mike Ault