Oracle動態效能檢視學習筆記(6)_v$filestat

目錄

參考文件<>Chapter 24

##################################################################
1 Overview
##################################################################

This view keeps information on physical IO activity for each file. This is usefule in isolating where the IO activity is happening if the
bottleneck is IO related.
v$filestat shows the following information for database IO(but not for log file IO.
1) Number of physical reads and writes;
2) Number of blocks read and write
3) Total IO time for reads and writes

統計自動系統啟動以來資料檔案的IO活動情況，這裡統計的是真正寫到物理IO嗎？
沒有的。作業系統層面上還有Buffer Cache.這裡只是統計交給作業系統層面的IO活動情況。

##################################################################
2 Useful Columns for v$filestat
##################################################################
1) FILE#:     Number of the file
2) PHYRDS:    Number of physical reads done
3) PHYBLKRD:    Number of physical blocks read
4) PHYWRTS      Number of physical writes done
5) PHYBLKWRT    Number of physical blocks write
6) SINGLEBLKRDS Number of single block reads
7) READTIM      Time spent doing reads if the TIME_STATISTICS=true;(百分之一秒)
8) WRITETIM     Time spent doing writes if the TIME_STATISTICS=true;(百分之一秒)
9) SINGLEBLKRDTIM Cumulative single block read time

Note:
1) Physical reads and blocks read can be different because of multiblock read calls;
物理讀的次數與物理塊讀的次數是不一樣的，有時一次物理讀會讀幾個物理塊。

2) Physical writes and blocks written can differ because of direct writes by processes.
物理寫的次數與物理塊寫的次數不一樣。有時會Direct writes　直接寫，直接寫一次會寫多個物理塊

3) SUM(PHYBLKRD) should correlate closely with physical reads from v$sysstat
每個檔案的物理讀總數應與v$sysstat.physical_reads接近。

4) SUM(PHYBLKWRT) should correlate closely with physical writes from v$sysstat
每個檔案的物理寫總數應與v$sysstat.physical_writes接近。

5) Reads(into buffer cache as well as direct reads) are done by server processes.
(將資料讀入Cache，直接讀的過程是由服務程式來完成的)

Writes from buffer cache are handled only by the DBWR.
將資料從Cache中寫到Disk的過程只能由DBWR程式完成。

The direct writes are handleed by the server processes.
直接寫，將資料從PGA寫到Disk的過程由服務程式完成。

##################################################################
3 示例
##################################################################
1 Checking Oracle Datafile IO

select name, phyrds, phywrts from v$datafile df, v$filestat fs where df.file# = fs.file#;

注意：儘管oracle記錄的讀寫次數非常精確，但如果資料庫執行在Unix檔案系統(UFS)有可能不能表現真實的磁碟讀寫，例如，讀次數可能並非真實的磁碟讀，而是UFS快取。不過裸裝置的讀寫次數應該是比較精準的。

2 Finding the Files with Large Numbers of Multiblock Reads

SELECT t.tablespace_name
,SUM(a.phyrds-b.phyrds)/MAX(86400*(a.snap_date-b.snap_date)) "Rd/sec"
,SUM(a.phyblkrd-b.phyblkrd)/greatest(SUM(a.phyrds-b.phyrds),1) "Blk/rd"
,SUM(a.phywrts-b.phywrts)/MAX(86400*(a.snap_date-b.snap_date)) "Wr/sec"
,SUM(a.phyblkwrt-b.phyblkwrt)/greatest(SUM(a.phywrts-b.phywrts),1) "Blk/wr"
FROM snap_filestat a, snap_filestat b, dba_data_files t
WHERE a.file# = b.file#
AND a.snap_id = b.snap_id + 1
AND t.file_id = a.file#
GROUP BY t.tablespace_name
HAVING sum(a.phyblkrd-b.phyblkrd)/greatest(SUM(a.phyrds-b.phyrds),1) > 1.1
OR SUM(a.phyblkwrt-b.phyblkwrt)/greatest(SUM(a.phywrts-b.phywrts),1) > 1.1
ORDER BY 3 DESC, 5 DESC;

##################################################################
4 問題
##################################################################
1 這個檢視統計檔案的IO情況，能不能再進一步，統計資料塊的IO情況，如某個資料塊的IO次數？？

2 檢視snap_filestat沒找到相關的資訊？？

Oracle動態效能檢視學習筆記(6)_v$filestat

相關文章