Measuring Disk I/O Speed
The relative cost of physical disk access is an important topic since all Oracle databases retrieve and store data.
A significant factor in weighing these costs is physical disk speed. Quicker disk access speeds can diminish the costs of a full table scan versus single block reads to a negligible level.
In a solid-state disk environment, disk I/O is far more rapid and multiblock reads become far cheaper compared to traditional disks. The new solid-state disks provide up to 100,000 I/Os per second, six times faster than traditional disk devices.
The standard STATSPACK report can be generated when the database is processing a peak load, and it is also possible to get a detailed report of all elapsed time metrics. The STATSPACK top-five timed event report is the most important of these metrics. The report is critical for it displays the database events that constitute the bottleneck for the system. The listing below from a STATSPACK report shows that the system is clearly constrained by disk I/O.
Top 5 Timed Events
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
log file parallel write 19,157 837 5.68
Reads and writes constitute the majority of the total database time as shown above. In such a case, the RAM size of the db_cache_size should be increased in order to reduce disk I/O, the SQL tuned to reduce disk I/O, or a faster disk I/O subsystem should be invested in.
Not only do the ideal optimizer settings rely on the environment, they are heavily swayed by the system's costs for scattered disk reads versus sequential disk reads. A great script to measure these I/O costs on the database is shown below.
While there are varied opinions regarding full-table scans, they are not necessarily a detriment to performance. Indeed they are often the quickest way to access the table rows. The CBO option of performing a full table scan depends on many factors, some being the settings for Oracle Parallel Query, the db_block_size, the clustering_factor; and the estimated percentage of rows returned by the query according to the CBO statistics.
Once Oracle selects a full-table scan, the speed of performing a full-table scan (SOFTS) rests with internal and external factors:
§ Table partitioning
§ The number of CPUs on the system
§ The setting for Oracle Parallel Query (parallel hints, alter table)
§ The speed of the disk I/O subsystem (e.g., hardware-cached I/O, solid-state disk RAM 3)
When factoring in all these elements, it may be impossible to decide the exact best setting for the weight in optimizer_index_cost_adj. In reality, the decision to petition a full-table scan is heavily influenced by run-time factors such as:
§ The present demands on the CPUs
§ The attainability of free blocks in the data buffers
§ The amount of TEMP tablespace, if the FTS has an order by clause
No two database systems are the same and good DBAs must adjust optimizer_index_cost_adj according to database configuration and data access patterns.
The encompassing amount of time 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.
The following section will introduce how to measure system I/O wait events in real-time.
Copyright © 1996 - 2014 by Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.