Question: I see that Oracle has a
v$iostat_consumer_group view that allows me to see I/O
timings. Are these the same number is an AWR report?
When is it appropriate to use
Answer: Oracle does not run in a
vacuum, and Oracle has introduced many tools like
to monitor the external environment.
An Oracle consumer group is a named entities, which are groups of
users or sessions combined together, based on their processing and
We do not recommend using consumer groups
except in special cases. Consumer groups will limit the
available resources of end-users, and when consumer group limits are
reached, the end-user session is terminated. It is never a
good idea to terminate an end user.
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.
- 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. See
Monitoring I/O with v$iostat_file.
- 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).
Monitoring I/O for Oracle background processes with
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.