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 Events
~~~~~~~~~~~~~~~~~~                                         % Total
Event                              Waits      Time (s)      Ela Time
--------------------------- ------------   -----------      --------
db file sequential read            2,598         7,146         48.54
db file scattered read            25,519         3,246         22.04
library cache load lock              673         1,363          9.26
CPU time                           1,154          7.83          6.21
log file parallel write           19,157           837          5.68
 

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 Events
~~~~~~~~~~~~~~~~~                                           % Total
Event                                     Waits    Time (s) Ela Time
---------------------------------- ------------ ----------- --------
CPU time                                  4,851       4,042    55.76
db file sequential read                   1,968       1,997    27.55
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:

• All Oracle databases are I/O-bound:  Untrue.  Databases with a reasonable data buffer cache size and a small working set will usually be constrained by CPU or network latency.

• The Data Buffer Hit Ratio (DBHR) will yield caching efficiency:  Untrue, 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 request.

• Only faster disk can remove I/O bottlenecks:  Untrue.  This is a common myth.  There are other non-RAM approaches to reducing disk I/O for Oracle databases:

• Adjusting optimizer_mode:  Oracle will generate widely differing SQL execution plans depending on the optimizer mode.

• Re-analyze SQL Optimizer statistics:  Using better quality CBO statistics with dbms_stats and adding column histograms can make a huge difference in disk activity.

• Adjusting Oracle  parameters: Resetting the optimizer_index_cost_adj  and optimizer_index_caching parameters can affect physical reads.

• Improve clustering_factor for index range scans: Manually resequence table rows to improve clustering_factor , sometimes using single-table clusters, can reduce disk I/O.

• Use Materialized Views: 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.
 

See 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.

---

APEX support:

For APEX development support just call to get an Oracle Certified professional for all APEX development projects.