| |
 |
|
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
% Total
Event
Waits Ela Time
--------------------------- ------------ -----------
db
file sequential read
2,598 48.54
db file scattered read
25,519 22.04
library cache load lock
673
9.26
CPU time
44
7.83
log file parallel write
19,157
5.65
See my notes on
disk I/O tuning for further details on Oracle disk
constrained databases.
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.
The dba_hist_sqlstat
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.
awr_sqlstat_deltas.sql
col c1 heading 'Begin|Interval|time'
format a8
col c2 heading 'SQL|ID'
format a13
col c3 heading 'Exec|Delta'
format 9,999
col c4 heading 'Buffer|Gets|Delta'
format 9,999
col c5 heading 'Disk|Reads|Delta'
format 9,999
col c6 heading 'IO Wait|Delta'
format 9,999
col c7 heading 'Application|Wait|Delta'
format 9,999
col c8 heading 'Concurrency|Wait|Delta'
format 9,999
break on c1
select
to_char(s.begin_interval_time,'mm-dd hh24') c1,
sql.sql_id c2,
sql.executions_delta c3,
sql.buffer_gets_delta c4,
sql.disk_reads_delta c5,
sql.iowait_delta c6,
sql.apwait_delta c7,
sql.ccwait_delta c8
from
see code depot for full scripts
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
order by
c1,
c2
;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
|
|
|
