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.