 |
|
Oracle sequential read disk I/O tuning
Oracle Tips by Burleson Consulting
Updated October 29, 2007
|
10gr2 Note: Starting in
Oracle 10g release2, Oracle recommends not setting the
db_file_multiblock_read_count parameter, allowing Oracle to
empirically determine the optimal setting. For more details,
see my notes on
10gR2 automatically tuned multi-block reads.
Also,
see my related important notes on
tuning full-scan I/O.
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.
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
log file parallel write
19,157 837 5.68
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. Below is a great script you can use to measure these
I/O costs on your database.
col c1 heading 'Average
Waits|forFull| Scan Read I/O' format 9999.999
col c2 heading 'Average
Waits|for Index|Read I/O' format 9999.999
col c3 heading 'Percent of|
I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of|
I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj'
format 999
select
a.average_wait
c1,
b.average_wait
c2,
a.total_waits /(a.total_waits
+ b.total_waits) c3,
b.total_waits /(a.total_waits
+ b.total_waits) c4,
(b.average_wait /
a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file
scattered read'
and
b.event = 'db file
sequential read';
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:
(avg-wait-for-db-file-sequential-read /
avg-wait-for-db-file-scattered-read) * 100
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.
Here is sample
output from a real system showing an empirical
showing the numbers. We always expect
scattered reads (full-table scans) to be far
faster than sequential reads (index probes)
because of Oracle sequential prefetch (db_file_multiblock_read_count):
- scattered read
(full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much
longer 86ms (c4)
-
starting setting for optimizer_index_cost_adj at
36:
C1 C2 C3 C4 C5
---------- ---------- ---------- ----------
----------
13,824 5,072
13 86
36
Here is another
variant, showing changes to
optimizer_index_cost_adj
(see important note below) wait components over
time:
set pages 80
set lines 130
col c1 heading 'Average Waits for|Full Scan Read
I/O' format 999999.999
col c2 heading 'Average Waits for|Index Read I/O'
format 999999.999
col c3 heading 'Percent of| I/O Waits|for
scattered|Full Scans' format
999.99
col c4 heading 'Percent of| I/O Waits|for
sequential|Index Scans' format
999.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj'
format 99999
select a.snap_id "Snap",
sum(a.time_waited_micro)/sum(a.total_waits)/10000
c1,
sum(b.time_waited_micro)/sum(b.total_waits)/10000
c2,
(sum(a.total_waits) / sum(a.total_waits +
b.total_waits)) * 100 c3,
(sum(b.total_waits) / sum(a.total_waits +
b.total_waits)) * 100 c4,
(sum(b.time_waited_micro)/sum(b.total_waits))
/
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100
c5
from
dba_hist_system_event a,
dba_hist_system_event b
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read'
group by a.snap_id
order by 1
/
Snap Full Scan Read I/O Index Read I/O
Full Scans Index Scans
---------- ------------------ -----------------
------------- --------------
5079 .936
.074 10.14 89.86
5080 .936
.074 10.14 89.86
5081 .936
.074 10.14 89.86
5082 .936
.074 10.14 89.86
5083 .936
.074 10.13 89.87
5084 .936
.074 10.13 89.87
5085 .936
.074 10.13 89.87
Important Note:
Prior to Oracle 10g, adjusting these optimizer parameters was the
only way to compensate for sample size issues with dbms_stats.
As of 10g, the use of dbms_stats.gather_system_stats and
improved sampling within dbms_stats had made adjustments to
these parameters far less important. Ceteris Parabus, always adjust
CBO statistics before adjusting optimizer parms. For more details
on optimizer parameters, see my latest book "Oracle
Tuning: The Definitive Reference".
and include the URL for the page.
