Learning Dynamic Performance Table 9-V $ filestat

Learning Dynamic Performance Table

Article 9-- V $ filestat

This view records the physical I/O information of each file. If the bottleneck is related to I/O, it can be used to analyze the active I/O events. V $ filestat displays the following information about database I/O (excluding log files ):

L number of physical reads and writes

L block read/write count

L I/O total read/write time

The preceding values start from instance startup. If two snapshots are obtained, the difference between the two is the active I/O statistics during this period.

V $ filestatCommon columns in:

 

L file #: file number;

L phyrds: number of completed physical reads;

L phyblkrd: Number of read blocks;

L phywrts: Number of physical writes completed by dbwr;

L phyblkwrt: number of data blocks written to the disk;

V $ filestatNote:

L because multiblock reading is called, physical reading and data block reading may be different;

L physical write and data block write may be inconsistent because of the process direct write;

L sum (physical blocks read) is similar to physical reads in V $ sysstat;

L sum (physical blocks written) is similar to physical writes in V $ sysstat;

L Data Reading (better than direct read by cache) is processed by the service process. Writing from the buffer cache can only be performed by dbwr, and direct writing is handled by the service process.

V $ filestatConnection column in

Column view joined column (s)

-------------------------------------------------------------

File # dba_data_files file_id

File # V $ datafile file #

Example:

1. Obtain the physical reads and writes of data files and data block reads and writes:

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

Orderby DF. tablespace_name;

Note: although the Read and Write times recorded by Oracle are very accurate, if the database runs on a Unix File System (UFS), it may not be able to perform real disk read and write operations, for example, the number of reads may not be the actual disk read, but the UFS cache. However, the read/write count of bare devices should be accurate.

2.