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，直接读<将数据从disk直接读到PGA>的过程是由服务进程来完成的)

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没找到相关的信息？？

ITPub博客

Oracle动态性能视图学习笔记(6)_v$filestat

最新文章