Question: I see that Oracle has a
v$iostat_function view that allows me to see I/O timings. Are
these the same number is an AWR report? When is it appropriate
to use v$iostat_function?
does not run in a vacuum, and Oracle has introduced many tools to
monitor the external environment.
Remember, no amount of tuning is going to help a database that has
an external bottleneck, such as I/O enqueues.
- The dbms_stats.gather_system_stats to sample the
external environment for CPU and disk latency. See
using dbms_stats.gather_system_stats for external monitoring.
- Oracle has internal instrumentation mechanisms capture disk
I/O metrics automatically for all tablespaces and files and
stored them in the v$ structures, the dba_hist tables and the
Internally Oracle knows the individual I/O latency for all
tablespaces and datafiles. As we see these iostat details in all AWR
and STATSPACK reports.
Oracle has introduced three iostat
views to display I/O details:
- v$iostat_consumer_group: If Oracle
Database Resource Manager is enabled this view captures I/O
statistics for consumer groups. See
monitoring I/O against consumer groups.
- v$iostat_file: This v$iostat_file
view displays I/O statistics of database files that are or have
been accessed. The small_sync_read_latency column
displays the latency for single block synchronous reads (in
milliseconds), which translates directly to the amount of time
that clients need to wait before moving onto the next operation.
The normal range of I/O speeds range from .002 ms for solid
state disks (RAM disk) to 5 ms for a disk array, up to 15 ms for
a standard platter disk without any RAM caching.
- v$iostat_function: This
v$iostat_function view captures I/O statistics for database
background processes. Oracle has named the sum of the
background processes as functions (ARCH, LGWR, DBWR, &c).
Here are the writer processes within Oracle that are collected by
Buffer Cache Reads
Quest expert Guy Harrison has this great SQL script to
display The wait time for all background processes that perform
system I/O (DBWR, ARCH, etc.)
col function_name format a25
heading "File Type"
col reads format 99,999,999
writes format 99,999,999 heading "Writes"
col number_of_waits format 99,999,999
col wait_time_sec format 999,999,999 heading "Wait Time|Sec"
col avg_wait_ms format 999.99
heading "Avg|Wait ms"
set lines 80
set pages 10000
set echo on
small_read_reqs + large_read_reqs reads,
case when number_of_waits > 0
round(wait_time / number_of_waits, 2)
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.