Oracle Database Tips by Donald Burleson
Improving I/O Speed is Not a Silver Bullet
SSD and RAM buffer caching are only important to
I/O-intensive Oracle databases. If an Oracle database is constrained
by other environmental factors such as CPU or network, speeding up the
I/O subsystem will not result in any appreciable performance gains.
To learn about databases resource bottlenecks, the DBA need only
display the top-5 timed events from STATSPACK.
The following is a typical OLTP database where I/O
delay is the main source of wait time. I/O comprises more than 70% of
total elapsed time.
Top 5 Timed
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time
(s) Ela Time
db file sequential read 2,598 7,146
db file scattered read 25,519 3,246
library cache load lock 673 1,363
CPU time 1,154
log file parallel write 19,157
Again, it is critical to note that additional RAM
resources may not have any appreciable effect on databases that are
not I/O intensive. For example, some scientific Oracle databases only
read a small set of experimental results and spend the majority of
database time performing computations. The output of such a database
is shown below:
Top 5 Timed
~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
---------------------------------- ------------ ----------- --------
CPU time 4,851 4,042 55.76
db file sequential read 1,968 1,997
log file sync 299,097 369 5.08
db file scattered read 53,031 330 4.55
log file parallel write 302,680 190 2.62
In this example, CPU time is the primary source of
database delay, and improving the speed of the I/O with SSD may not
have an appreciable effect on overall Oracle performance.
One must be cognizant that it is foolish to focus
solely on minimizing physical disk I/O. For databases with
sub-optimal SQL statements, poor performance is commonly combined with
a high data buffer cache hit ratio
and little disk I/O. For these databases, the root cause of the
performance problem is excessive logical I/O, whereby the sub-optimal
SQL rereads data blocks over and over from the RAM data buffers.
There are several myths of Oracle physical I/O
that must be exposed at this point:
Untrue. Databases with a
reasonable data buffer cache size and a small working set will
usually be constrained by CPU or network latency.
except in cases of a super small cache. The DBHR only measures the
propensity that a data block will be in the buffer on the second I/O
Untrue. This is a common myth.
There are other non-RAM approaches to reducing disk I/O for Oracle
will generate widely differing SQL execution plans depending on the
Using better quality CBO statistics
with dbms_stats and adding
column histograms can make a huge difference in disk activity.
parameters can affect physical reads.
resequence table rows to improve
sometimes using single-table clusters, can reduce disk I/O.
Systems with batch only updates may greatly benefit
from Materialized Views to
pre-join tables. Of course, the overhead of refresh commit is too
great for high update systems.
With that information on the historical issues
about how Oracle uses RAM, the next step is to look at the issues of
duplicated RAM caching in large Oracle systems.
code depot for complete scripts
This is an excerpt from the book
Oracle RAC & Tuning with Solid State Disk.
You can get it for more than 30% by buying it directly from the
publisher and get immediate access to working code examples.
Market Survey of SSD vendors for
There are many vendors who offer rack-mount solid-state disk that
work with Oracle databases, and the competitive market ensures that
product offerings will continuously improve while prices fall.
SearchStorage notes that SSD is will soon replace platter disks and that
hundreds of SSD vendors may enter the market:
"The number of vendors in this category could rise to several
hundred in the next 3 years as enterprise users become more familiar
with the benefits of this type of storage."
As of January 2015, many of the major hardware vendors (including Sun and
EMC) are replacing slow disks with RAM-based disks, and
Sun announced that all
of their large servers will offer SSD.
Here are the major SSD vendors for Oracle databases
(vendors are listed alphabetically):
2008 rack mount SSD Performance Statistics
SearchStorage has done a comprehensive survey of rack mount SSD
vendors, and lists these SSD rack mount vendors, with this showing the
fastest rack-mount SSD devices:
||performance metrics and notes
sustained external throughput, 400,000 random IOPS
1,00MB/s write with ×4 PCIe, 3 microseconds latency
Solid Access Technologies
sustained read or write per port (full duplex is 719MB/s), with
8 x 4Gbps FC ports aggregated throughput is approx 2,000MB/s,
R/W transfer rate, 35,000 IOPS
Choosing the right SSD for Oracle
When evaluating SSD for Oracle databases you need
to consider performance (throughput and response time), reliability (Mean Time Between failures) and
TCO (total cost of ownership). Most SSD vendors will provide a
test RAM disk array for benchmark testing so that you can choose the
vendor who offers the best price/performance ratio.
Burleson Consulting does not partner with any SSD vendors and we
provide independent advice in this constantly-changing market. BC
was one of the earliest adopters of SSD for Oracle and we have been
deploying SSD on Oracle database since 2005 and we have experienced SSD
experts to help any Oracle shop evaluate whether SSD
is right for your application. BC experts can also help you choose
the SSD that is best for your database. Just
call 800-766-1884 or e-mail.:
SSD support details.
vs. Flash SSD
the talk about the Oracle “flash cache”, it is important to note that there
are two types of SSD, and only DRAM SSD is suitable for Oracle database
storage. The flash type SSD suffers from serious shortcomings, namely
a degradation of access speed over time. At first, Flash SSD is 5
times faster than a platter disk, but after some usage the average read time
becomes far slower than a hard drive. For Oracle, only rack-mounted
DRAM SSD is acceptable for good performance:
Avg. Read speed
Avg. write speed
||For APEX development support just call to get an
Oracle Certified professional for all APEX development