Find disk constrained Oracle instance
Oracle Database Tips by Donald BurlesonJuly 4, 2013
Disk Constrained Databases
In a disk-bound database, the majority of the wait time
is spent accessing data blocks. This can be db file
sequential read waits, which is usually index access, and db
file scattered read waits, which is usually full-table scans
as evidenced by the following AWR report section:
Top 5 Timed Events
Waits Ela Time
--------------------------- ------------ -----------
file sequential read
db file scattered read
library cache load lock
log file parallel write
See my notes on
disk I/O tuning for further details on Oracle disk
I/O bound databases can be tuned down by any number of
holistic techniques including large data buffers with 64-bit
Oracle, changing RAID levels on the disk and using high
speed solid-state RAM-disk.
table is a great way to find disk bottlenecks. This view is
very similar to the v$sql view, but it contains important
SQL metrics for each snapshot. These include important delta
(change) information on disk reads and buffer gets, as well
as time-series delta information on application, I/O and
concurrency wait times. The following is a script that
queries this view for this performance information.
col c1 heading 'Begin|Interval|time'
col c2 heading 'SQL|ID'
col c3 heading 'Exec|Delta'
col c4 heading 'Buffer|Gets|Delta'
col c5 heading 'Disk|Reads|Delta'
col c6 heading 'IO Wait|Delta'
col c7 heading 'Application|Wait|Delta'
col c8 heading 'Concurrency|Wait|Delta'
break on c1
to_char(s.begin_interval_time,'mm-dd hh24') c1,
see code depot for full scripts
s.snap_id = sql.snap_id
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.