Learning Dynamic Performance Table (9)--v$filestat

Learning Dynamic Performance Table

Nineth article --v$filestat 2007.6.5

This view records physical I/O information for each file. If the bottleneck is associated with I/O, you can use it to analyze the active I/O events that occur. V$FILESTAT Displays the following information for database I/O (not including log files):

• L Physical Read and write number
• L block read/write number
• L I/O reads and writes total time

The above values start recording from the instance start. If two snapshots are taken, the difference between the two is the active I/O statistics for this time period.

V$filestat common columns in:

• L file#: Document serial number;
• L Phyrds: The number of physical reads completed;
• L PHYBLKRD: block read number;
• L PHYWRTS:DBWR The number of physical writes completed;
• L PHYBLKWRT: Number of blocks written to disk;

V$filestat Note item:

• The physical readings and data block readings may differ because of the multiblock read call;
• Because the process is written directly, physical writing and data block writing may also be inconsistent;
• 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;
• The data read (read better by the cache) is handled by the service process. Write from buffer cache can only be performed by DBWR, which is processed by the service process.

V$filestat the connection column in

Column View Joined column (s)

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

file# Dba_data_files file_id

file# V$datafile file#

Example:

1. Obtain data file physical read-write and data block read and write information:

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;

Note: Although the read and write times for Oracle records are very accurate, if the database is running on a UNIX file system (UFS) It may not be possible to perform real disk reads and writes, for example, the read times may not be real disk read, but a UFS cache. However, the number of bare device read and write should be more accurate.

Learning Dynamic Performance Table (9)--v$filestat