Oracle release 11.2 has introduced new iostat views that show the
resource usage against the physical disk spindles.
These are very important new AWR tables because it allows Oracle
to look outside of the database and get statistics directly on disk
service rimes.
v$iostat_consumer_group:
See
v$iostat consumer group
v$iostat_file: This has valuable file I/O
statistics. What is missing is the all-important disk enqueues
which would indicate disk contention for a device. However,
this could be inferred from read service time, provided that the two
AWR snapshots are less then 60 seconds apart and the file is not
striped across multiple disks:
|
file_no
|
File
identification number
|
|
filetype_id
|
Type of file
(for example, log file, data file, and so on)
|
|
filetype_name
|
Name of the
file, in the case of a data file or temp file. For all other
files, a corresponding string to be displayed (for example,
ARCHIVELOG).
|
|
small_read_megabytes
|
Number of
single block megabytes read
|
|
small_write_megabytes
|
Number of
single block megabytes written
|
|
large_read_megabytes
|
Number of
multiblock megabytes read
|
|
large_write_megabytes
|
Number of
multiblock megabytes written
|
|
small_read_reqs
|
Number of
single block read requests
|
|
small_write_reqs
|
Number of
single block write requests
|
|
small_sync_read_reqs
|
Number of
synchronous single block read requests
|
|
large_read_reqs
|
Number of
multiblock read requests
|
|
large_write_reqs
|
Number of
multiblock write requests
|
|
small_read_servicetime
|
Total service
time (in milliseconds) for single block read requests
|
|
small_write_servicetime
|
Total service
time (in milliseconds) for single block write requests
|
|
small_sync_read_latency
|
Latency for
single block synchronous reads (in milliseconds)
|
|
large_read_servicetime
|
Total service
time (in milliseconds) for multiblock read requests
|
|
large_write_servicetime
|
Total service
time (in milliseconds) for multiblock write requests
|
|
asynch_io
|
Indicates
whether asynchronous I/O is available for the file (ASYNC_ON) or not (ASYNC_OFF)
|
|
retries_on_error
|
Number of
read retries on error
|
v$iostat_function & v$iostat_function_detail:
This table marks what processes are writing to each
disk spindle. Interesting, but not too helpful in tuning:
RMAN, DBWR, LGWR, ARCH, XDB, Streams, AQ, Data
Pump, Recovery Buffer Cache Reads, Direct Reads, Direct Writes
,Smart Scan, Archive Manager
See
v$iostat
function
v$iofunctionmetrics: This table contains
avg wait_time, an indicator of a "busy" disk with read
enqueues that may cause a database to become I/O-bound.
See v$iofunctionmetrics tips
v$iostat_function tips
v$iostat_network: This view shows I/O wait
times on network traffic, similar to SQL*Net roundtrip metrics.
See v$iostat_network
We also see corresponding DBA_HIST AWR tables for iostat
information:
dba_hist_iostat_detail: See
dba_hist_iostat_detail tips
dba_hist_iostat_filetype: See
dba_hist_iostat_filetype
tips
dba_hist_iostat_filetype_name: See
dba_hist_iostat_filetype_name tips
dba_hist_iostat_function:
See
dba_hist_iostat_function tips
dba_hist_iostat_function_name: See
dba_hist_iostat_function_name tips
 |
|
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.
|
