Data Management

Database Optimization: Measuring Oracle disk I/O speed

An effective Oracle database optimization plan requires the measurement of physical disk access and an assessment of relative costs. Valuable processing information for this assessment can be gleaned from the standard Oracle STATSPACK report.

Because all Oracle databases retrieve and store data, the relative cost of physical disk access is an important topic. In Oracle, we see two types of data block access:
  • db file sequential read—A single-block read (i.e., index fetch by ROWID)
  • db file scattered read—A multiblock read (a full-table scan, OPQ, sorting)

Physical disk speed is an important factor in weighing these costs. Faster disk access speeds can reduce the costs of a full-table scan vs. single block reads to a negligible level.

For example, the new solid state disks provide up to 100,000 I/Os per second, six times faster than traditional disk devices. In a solid-state disk environment, disk I/O is much faster and multiblock reads become far cheaper than with traditional disks.

The standard STATSPACK report can be generated when the database is processing a peak load, and you can get a super-detailed report of all elapsed-time metrics. The most important of these metrics is the STATSPACK top-five timed events. This report is critical because it shows the database events that constitute the bottleneck for the system. We can also see the same phenomenon where a system is disk I/O bound. In the STATSPACK report in Listing A, we see that the system is clearly constrained by disk I/O.

Here we see that reads and a write constitute the majority of the total database time. In this case, we would want to increase the RAM size of the db_cache_size to reduce disk I/O, tune the SQL to reduce disk I/O, or invest in a faster disk I/O subsystem.

The ideal optimizer settings depend on your environment and are heavily influenced by your system's costs for scattered disk reads vs. sequential disk reads. Listing B shows a great script you can use to measure these I/O costs on your database.

Scattered reads and full-table scans
Contrary to some opinions, full-table scans are not necessarily a detriment to performance, and they are often the fastest way to access the table rows. The CBO (cost-based optimizer) choice of performing a full-table scan depends on the settings for Oracle Parallel Query, the db_block_size, the clustering_factor, the estimated percentage of rows returned by the query (according to the CBO statistics), and many other factors.

Once Oracle has chosen a full-table scan, the speed of performing a full-table scan (SOFTS) depends on internal and external factors:
  • The number of CPUs on the system
  • The setting for Oracle Parallel Query (parallel hints, alter table)
  • Table partitioning
  • The speed of the disk I/O subsystem (e.g., hardware-cached I/O, solid-state disk RAM 3)

With all of these factors, it may be impossible to determine the exact best setting for the weight in optimizer_index_cost_adj. In the real world, the decision to invoke a full-table scan is heavily influenced by run-time factors such as:
  • The availability of free blocks in the data buffers
  • The amount of TEMP tablespace (if the FTS has an order by clause)
  • The current demands on the CPUs

Hence, it follows that the optimizer_index_cost_adj should change frequently, as the load changes on the server.

However, is it safe to assume that all of the SOFTS factors are reflected in the relative I/O speed of FTS vs. index access? If we make this assumption, we've measured the relative speed in v$system_event and have a foundation for creating a self-tuning parameter. To do this, we must accept the following assumptions:
  • No systems are alike, and good DBAs must adjust optimizer_index_cost_adj according to their configuration and data access patterns.
  • The SOFTS is measurable and is reflected in the wait times in v$system_event.
  • The overall amount of time spent performing full-table scans is equal to the percentage of db file sequential read waits as a percentage of total I/O waits from v$system_event (see Listing C).

Control disk I/O
Disk I/O-intensive systems are common to Oracle databases, and the time spent performing disk I/O often consumes the majority of the wait time. The job of the Oracle professional is to examine all SQL statements to ensure that they're performing the minimum amount of disk I/O and to know their relative costs of index access vs. full-table scan access.

Editor's Picks

Free Newsletters, In your Inbox