 |
|
Oracle Tips by Burleson |
The
Problem of Duplicitous 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:
– 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.
– 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
|
