Datafile Metrics
Oracle10g provides several database metrics for datafile access
monitoring. There are two
v$ views that report
datafile metrics. The
v$filemetric_history
view shows file metrics for samples that occurred on ten minute
intervals during the preceding hour. The
v$filemetric view provides the datafile metrics for
the most recent sample.
For a complete description of the v$ views, get the "free
11g poster" sponsored by Quest Software. The following metrics are available for
review:
§
Average file read and write times
§
Current numbers of physical read and write operations
§
Current numbers of physical blocks reads and writes
The following
avg_io_time.sql query
reports datafiles that experience a high current read/write I/O time
that exceeds the average I/O time computed:
SELECT
to_char(m.begin_time,'hh24:mi') "start time",
to_char(m.end_time,'hh24:mi') "end time",
f.file_name,
s.lstiotim "last i/o time",
m.average_read_time + m.average_write_time "average i/o time"
FROM
v$filemetric m,
dba_data_files f,
v$filestat s
WHERE
m.file_id = f.file_id
AND s.file# = f.file_id
AND s.lstiotim > (m.average_read_time + m.average_write_time);
The following is a result listing where the average I/O time and the
last I/O time are shown:
start end t FILE_NAME
Last I/O Time Average I/O Time
----- -----
---------------------------------------- ---- ----------------
12:36 12:46 D:\ORACLE\ORADATA\DBDABR\SYSTEM01.DBF
6 3
12:36 12:46 D:\ORACLE\ORADATA\DBDABR\UNDOTBS01.DBF
3 2
12:36 12:46 D:\ORACLE\ORADATA\DBDABR\SYSAUX01.DBF
12 5
12:36 12:46 D:\ORACLE\ORADATA\DBDABR\USERS01.DBF
4 7
Oracle captures snapshots of
v$filemetric_history views in the corresponding AWR table,
wrh$_filemetric_history.
MMON does not write all datafile metric history to AWR repository.
It only writes snapshots of history that existed at the moment of
the snapshots. Therefore, the DBA cannot reconstruct a sequential,
all-inclusive file I/O access history using AWR views such as
dba_hist_filemetric_history.
SEE CODE DEPOT FOR FULL SCRIPTS
and include the URL for the page.
