Oracle Database Tips by Donald Burleson

Improving I/O speed is not a silver bullet

Please understand that 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 the CPU or the network then speeding-up the I/O sub-system will not result in any appreciable performance gains.  To learn more about database resource bottlenecks, display the top-5 timed events from STATSPACK.

Here is a typical OLTP database where I/O delay is the main source of wait time.  Note that I/O comprises more than 70% of total elapsed time.

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 as evidenced by the report shown below.

In this example, notice that CPU time is the primary source of database delay so improving the speed of the I/O with SSD may not have an appreciable effect on overall Oracle performance.

Be careful not to focus solely on minimizing physical disk I/O.  For databases with sub-optimal SQL statements it is not uncommon to see poor performance combined with a very high data buffer cache hit ratio and very little disk I/O.  For these databases, the root cause of the performance problem is excessive logical I/O, whereby the sub-optimal SQL re-reads data blocks over-and-over from the RAM data buffers. 

Here are several myths of Oracle physical I/O:

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 will show caching efficiency - Untrue, except in cases of a super-small cache.  The Data Buffer Hit ratio  only measures the propensity that a data block will be in the buffer on the second I/O request.

Only faster disks can remove I/O bottlenecks - 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 - Re-setting the optimizer_index_cost_adj  and optimizer_index_caching  parameters can affect physical reads

Improve clustering_factor for index range scans - Manually re-sequence 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.

The next issue to be covered is duplicated RAM caching in large Oracle systems.

This is an excerpt from the book Oracle Solid State Disk Tuning.  You can get it for more than 30% by buying it directly from the publisher and get immediate access to working code examples.

---

HTML-DB support:

For HTML-DB development support just call to get an Oracle Certified professional for all HTML-DB development projects.