| |
 |
|
Examine Oracle I/O statistics
Oracle Tips by Burleson Consulting
June 22, 2003
|
The first step in unraveling any I/O puzzles in your
database is to make a quick check of some of the global database I/O
metrics. A query such as the globiostats.sql script can be used
to get a bird's eye view of a database's I/O:
select
name,
value
from
sys.v_$sysstat
where
name in
('consistent changes',
'consistent gets',
'db block changes',
'db block gets',
'physical reads',
'physical writes',
'sorts (disk)',
'user commits',
'user rollbacks'
)
order by
1;
The script queries the sys.v_$sysstat
view and output from the query might look like the following:
NAME VALUE
------------------------------
consistent changes 1
consistent gets 70983
db block changes 243
db block gets 612
physical reads 11591
physical writes 52
sorts (disk) 0
user commits 26
user rollbacks 1
Although there are some database experts who do not
believe the buffer cache hit ratio is of much value anymore (and there
are valid reasons for assuming such a stance), you can still perform a
cursory check to get an idea of overall disk I/O activity by using the
buffratio.sql script:
The full listing of the buffratio.sql
script can be obtained from the online Code Depot.
see code depot for full script
This script also queries the sys.v_$sysstat
view and some quick things to look for in the statistics include:
-
Increasing numbers of physical reads and a low hit ratio may
indicate insufficient settings for db_block_buffers or
db_cache_size (Oracle9i). The hit ratio reading in particular
should be observed over a decent time period to see if the ratio is
representative of the database’s ‘personality’, so keep in mind that
readings below the normal ‘rule of thumb’ (90%) can be OK.
-
High
volumes of disk sorts could be indicative of either a setting for
sort_area_size (Oracle8i and below) that is too low or
unnecessary sort activities. Seeing large numbers of physical writes
in a read-only database may also be indicative of excessive sorting.
-
Large
numbers of user rollbacks can be undesirable, since it indicates that
user transactions are not completing for one reason or another.
You should also do a cursory, global check of the
system-level wait events to get an idea of the I/O bottlenecks that may
be occurring. A script like the syswaits.sql script can be used
to perform such a check:
select
event,
total_waits,
round(100 * (total_waits / sum_waits),2) pct_tot_waits,
time_wait_sec,
round(100 * (time_wait_sec / sum_secs),2) pct_secs_waits,
total_timeouts,
avg_wait_sec
from
(select
event,
total_waits,
round((time_waited / 100),2) time_wait_sec,
total_timeouts,
round((average_wait / 100),2) avg_wait_sec
from
sys.v_$system_event
see code depot for full
script
where
event not in
('lock element cleanup
',
'pmon timer
',
'rdbms ipc message
',
'smon timer
',
'SQL*Net message from client
',
'SQL*Net break/reset to client
',
'SQL*Net message to client
',
'SQL*Net more data to client
',
'dispatcher timer
',
'Null event
',
'parallel query dequeue wait
',
'parallel query idle wait - Slaves',
'pipe get
',
'PL/SQL lock timer
',
'slave wait
'
'virtual circuit status,
'WMON goes to sleep') and
event not like 'DFS%' and
event not like 'KXFX%'),
(select
sum(total_waits) sum_waits,
sum(round((time_waited / 100),2)) sum_secs
from
sys.v_$system_event
where
event not in
('lock element cleanup
',
'pmon timer
',
'rdbms ipc message
',
'smon timer
',
'SQL*Net message from client
',
'SQL*Net break/reset to client
',
'SQL*Net message to client
',
'SQL*Net more data to client
',
'dispatcher timer
',
'Null event
',
'parallel query dequeue wait
',
'parallel query idle wait - Slaves
',
'pipe get
',
'PL/SQL lock timer
',
'slave wait
',
'virtual circuit status
',
'WMON goes to sleep') and
event not like 'DFS%' and
event not like 'KXFX%')
order by
2 desc;
The script queries the
sys.v_$system_event view and here are a few quick things
to note about the output from the waits SQL script:
-
Numerous waits for the db file scattered read event may
indicate a problem with table scans
-
Many
waits for the latch free event could indicate excessive
amounts of logical I/O activity
-
High
wait times for the enqueue event pinpoints a problem with lock
contention
Once you have a feel for the I/O numbers at a global level, you can
begin to work your way further down into what is really going on under
the covers.
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
