视图V$DATAFILE、V$FILESTAT 和V$DBA_DATA_FILES 提供了数据库中所有数据文件和磁盘的文件I/O
活动信息。理想情况下，物理的读和写应当平均分布。如果没有合理的配置系统，其综合性能就会受到影
响。下面程序清单中的脚本展示了实际的分布情况并可以很方便地判断出是否有不平衡的现象存在。第3
章对这个问题进行了详细的讨论；本节只是显示快捷式查询，以了解其大致情况。
select a.file#, a.name, a.status, a.bytes,
b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
下面两个程序清单中的查询提供了有关文件和数据分布问题的报告，并已改进其格式。第一个报告获
得数据文件I/O 的情况，第二个报告获得磁盘I/O 的情况。
Set TrimSpool On
Set Line 142
Set Pages 57
Set NewPage 0
Set FeedBack Off
Set Verify Off
Set Term On
TTitle Off
BTitle Off
Clear Breaks
Break On Tablespace_Name
Column TableSpace_Name For A12 Head "Tablespace"
Column Name For A45 Head "File Name"
Column Total For 999,999,990 Head "Total"
Column Phyrds For 999,999,990 Head "Physical|Reads "
Column Phywrts For 999,999,990 Head "Physical| Writes "
Column Phyblkrd For 999,999,990 Head "Physical |Block Reads"
Column Phyblkwrt For 999,999,990 Head "Physical |Block Writes"
Column Avg_Rd_Time For 90.9999999 Head "Average |Read Time|Per Block"
Column Avg_Wrt_Time For 90.9999999 Head "Average |Write Time|Per Block"
Column Instance New_Value _Instance NoPrint
Column Today New_Value _Date NoPrint
Select Global_Name Instance, To_Char(SysDate, 'FXDay, Month DD, YYYY HH:MI')
Today
From Global_Name;
TTitle On
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'Data File I/O' Skip 1 –
Center 'Instance Name: ' _Instance Skip 1
select C.TableSpace_Name, B.Name, A.Phyblkrd +
A.Phyblkwrt Total, A.Phyrds, A.Phywrts,
310
A.Phyblkrd, A.Phyblkwrt
From V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
where B.File# = A.File#
and B.File# = C.File_Id
order by TableSpace_Name, A.File#
/
select object_name, statistic_name, value
from v$segment_statistics
where value > 100000
order by value;
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------------
ORDERS space allocated 96551
ORDERS space allocated 134181
ORDERS logical reads 140976
ORDER_LINES db block changes 183600
下面的程序清单获得磁盘I/O:
Column TableSpace_Name For A12 Head "Tablespace"
Column Total For 9,999,999,990 Head "Total"
Column Phyrds For 9,999,999,990 Head "Physical|Reads "
Column Phywrts For 9,999,999,990 Head "Physical| Writes "
Column Phyblkrd For 9,999,999,990 Head "Physical |Block Reads"
Column Phyblkwrt For 9,999,999,990 Head "Physical |Block Writes"
Column Avg_Rd_Time For 9,999,990.9999 Head "Average|Read Time |Per Block"
Column Avg_Wrt_TimeFor 9,999,990.9999 Head "Average |Write Time|Per Block"
Clear Breaks
Break on Disk Skip 1
Compute Sum Of Total On Disk
Compute Sum Of Phyrds On Disk
Compute Sum Of Phywrts On Disk
Compute Sum Of Phyblkrd On Disk
Compute Sum Of Phyblkwrt On Disk
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'Disk I/O' Skip 1 -
Center 'Instance Name: ' _Instance Skip 2
select SubStr(B.Name, 1, 13) Disk, C.TableSpace_Name,
A.Phyblkrd + A.Phyblkwrt Total, A.Phyrds, A.Phywrts,
A.Phyblkrd, A.Phyblkwrt, ((A.ReadTim /
Decode(A.Phyrds,0,1,A.Phyblkrd))/100) Avg_Rd_Time,
((A.WriteTim / Decode(A.PhyWrts,0,1,A.PhyblkWrt)) /
100) Avg_Wrt_Time
from V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
where B.File# = A.File#
311
and B.File# = C.File_Id
order by Disk,C.Tablespace_Name, A.File#
/
Set FeedBack On
Set Verify On
Set Term On
Ttitle Off
Btitle Off
技巧：
视图V$DATAFILE、V$FILESTAT 和V$DBA_DATA_FILES 提供了数据库中所有数据文件和磁
盘的文件I/O 活动信息。确保数据文件和磁盘都处于合理的平衡中，以获得最佳的性能。