说明: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.
v$...stat的视图一般是从instance启动累计到当前的统计
关键字段:
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 |
以hr.employees为例:
1 查询该表的file号:
SQL> select t.file_id,t.file_name from dba_data_files t where t.tablespace_name = 'EXAMPLE';
FILE_ID FILE_NAME
---------- ----------------------------
5 D:/ORACLE/PRODUCT/10.2.0/ORADATA/HIS3/EXAMPLE01.DBF
2 对应在v$filestat中的状态
3. 查询语句
SQL> select * from hr.jobs;
已选择19行。
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=19 Bytes=62
7)
1 0 TABLE ACCESS (FULL) OF 'JOBS' (TABLE) (Cost=3 Card=19 Byte
s=627)
Statistics
----------------------------------------------------------
366 recursive calls
0 db block gets
76 consistent gets
6 physical reads
0 redo size
1434 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
19 rows processed
4 运行查询语句后该文件在v$filestat的状态
5 查看参数
SQL> show parameter db_block_size;
NAME TYPE VALUE
---------------------------- ----------- -------
db_block_size integer 8192
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
---------------------------- ---------- ----------
db_file_multiblock_read_count integer 16
说明:
PHYRDS表示物理读的次数, 3818 – 3816 = 2
PHYBLKRD表示读取的块数 4456 – 4450 = 6 即该sql statistics中的6 physical reads
注意参数db_file_multiblock_read_count的影响
其他参考:
本视图记录各文件物理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进行,直写由服务进程处理。
V$FILESTAT中的连接列
Column View Joined Column(s)
----------- --------------------- ------------
FILE# DBA_DATA_FILES FILE_ID
FILE# V$DATAFILE FILE#
网友资料:
http://www.51testing.com/?uid-138711-action-viewspace-itemid-155863
http://www.itpub.net/viewthread.php?tid=817206&highlight=v%24filestat