|
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.68
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
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
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
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
order by
c1,
c2
;
|
