原文連結

V$FILESTAT

This view displays the number of physical reads and writes done and the total number of single-block and multiblock I/Os done at file level. As of Oracle Database 10g Release 2 (10.2), this view also includes reads done by RMAN processes for backup operations.

Column	Datatype	Description
FILE#	NUMBER	Number of the file
PHYRDS	NUMBER	Number of physical reads done
PHYWRTS	NUMBER	Number of times DBWR is required to write
PHYBLKRD	NUMBER	Number of physical blocks read
PHYBLKWRT	NUMBER	Number of blocks written to disk, which may be the same as PHYWRTS if all writes are single blocks
SINGLEBLKRDS	NUMBER	Number of single block reads
READTIM	NUMBER	Time (in hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter istrue; 0 if false
WRITETIM	NUMBER	Time (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter istrue; 0 if false
SINGLEBLKRDTIM	NUMBER	Cumulative single block read time (in hundredths of a second)
AVGIOTIM	NUMBER	Average time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is true; 0 if false
LSTIOTIM	NUMBER	Time (in hundredths of a second) spent doing the last I/O, if the TIMED_STATISTICSparameter is true; 0 if false
MINIOTIM	NUMBER	Minimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICSparameter is true; 0 if false
MAXIORTM	NUMBER	Maximum time (in hundredths of a second) spent doing a single read, if theTIMED_STATISTICS parameter is true; 0 if false
MAXIOWTM	NUMBER	Maximum time (in hundredths of a second) spent doing a single write, if theTIMED_STATISTICS parameter is true; 0 if false

本檢視記錄各檔案物理I/O資訊。如果瓶頸與I/O相關，可用於分析發生的活動I/O事件。V$FILESTAT顯示出資料庫I/O的下列資訊(不包括日誌檔案)：

物理讀寫數
塊讀寫數
I/O讀寫總耗時

以上數值自例項啟動即開始記錄。如果獲取了兩個快照，那麼二者之間的差異即是這一時間段內活動I/O統計。

V$FILESTAT中的常用列：

FILE#：檔案序號；
PHYRDS：已完成的物理讀次數；
PHYBLKRD：塊讀取數；
PHYWRTS：DBWR完成的物理寫次數；
PHYBLKWRT：寫入磁碟的塊數；

V$FILESTAT注意項：
因為multiblock讀呼叫，物理讀數和資料塊讀數有可能不同；
因為程式直寫，物理寫和資料塊寫也可能不一致；
Sum(physical blocks read) 近似於v$sysstat中的physical reads；
Sum(physical blocks written) 近似於v$sysstat中的physical writes；
資料讀(由快取讀比直讀好)由服務程式處理。從buffer cache寫只能由DBWR進行，直寫由服務程式處理。

select df.tablespace_name name,
       df.file_name       "file",
       f.phyrds           pyr,
       f.phyblkrd         pbr,
       f.phywrts          pyw,
       f.phyblkwrt        pbw
from v$filestat f, dba_data_files df where f.file# = df.file_id
order by df.tablespace_name;

SQL> select sum(PHYBLKWRT),sum( PHYWRTS ) from v$filestat;

SUM(PHYBLKWRT) SUM(PHYWRTS)
-------------- ------------
82020105 76972981

SQL> select name,value from v$sysstat where name like '%writes%' ;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical writes                                                   100817824
physical writes non checkpoint                                     78362383
DBWR transaction table writes                                        140236
DBWR undo block writes                                             21725210
DBWR cross instance writes                                                8
DBWR fusion writes                                                   239173
remote instance undo block writes                                         8
remote instance undo header writes                                        0
redo synch writes                                                 149037740
physical writes direct                                             24863310
physical writes direct (lob)                                        2003213
redo writes                                                       147532422

12 rows selected.

v$filestat 中：
sum(PHYBLKWRT),sum( PHYWRTS) 分別表示 DBWR 的寫的 block 數和寫的次數，而 v$sysstat 中表示所有的寫的 blocks 數，包括 server porcess的 direct writes 。當然事實上，我們查詢下表也可以看出 v$filestat 中不包含臨時表空間

Oracle V$FILESTAT

原文連結

V$FILESTAT

相關文章