Oracle Temp File IO Statistics Test
Temp files are a special class of data files that are associated only with temporary tablespaces. Locally managed temporary tablespaces use temp files, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database.
If IOPS performed on the temp files take too much time, administrators must be able to quickly and accurately identify the exact temp file to which read/write operations are most latent and the type of I/O operation that was performed on that file (i.e., whether a multiblock read/write or a single block read/write) when latency peaked. This will enable administrators to determine the course of action that needs to be taken to ensure that the I/O latency does not aggravate. This insight is provided by the Oracle Temp File IO Statistics test. This test automatically discovers the temp files, and for each temp file reports the time taken to read and write single and multiple blocks of data in the file. This will point administrators to that temp file on which read/write operations take longer than normal. From this test, you can also infer when read/write latency is maximum – when reading a single block of data? Or when reading multiple blocks of data? When writing a single block of a data to the file? Or when writing multiple blocks of data to the file?
Target of the test : An Oracle 12c server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for every temp file on the Oracle server.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Multiblock read time: |
Indicates the time taken by this file to service multiblock I/O requests during the last measurement period.
|
Secs/Read |
Multiblock I/O read means reading multiple database blocks with a single operating system READ call. Typically, a database block is 8 KB. A single block read call results in one of these 8 KB blocks read from the datafile. Where a lot of data is to be read, it would be less efficient and more resource-intensive to read single blocks of data of 8KB each when the underlying I/O system is capable of reading say, 1 MB in one read. Oracle therefore issues a multiblock I/O and requests 1MB worth of block (128 8kb blocks) in one system READ call rather than 128 individual requests and therefore speeds up performance of the I/O requests. A very high value of this measure could indicate a bottleneck when processing multiblock read requests to a particular file. Compare the value of this measure across files to accurately identify that file from which multiple blocks of data were read from most slowly. |
Singleblock read time: |
Indicates the time taken for singleblock reads from this file during the last measurement period. |
Secs/read |
Typically, a database block is 8 KB. A single block read call results in one of these 8 KB blocks read from the datafile. Where a lot of data is to be read, it would be less efficient and more resource-intensive to read single blocks of data of 8KB each when the underlying I/O system is capable of reading say, 1 MB in one read. A very high value of this measure could indicate a bottleneck when processing single block read requests to a particular file. Compare the value of this measure across files to accurately identify that file from which a single block of data was read from most slowly. |
Multiblock write time: |
Indicates the time taken for multiblock writes into this file during the last measurement period.
|
Secs/write |
Multiblock I/O write means writing multiple database blocks to a file with a single operating system WRITE call. A very high value of this measure could indicate a bottleneck when processing multiblock write requests to a particular file. Compare the value of this measure across files to accurately identify that file to which multiple blocks of data were written most slowly. |
Singleblock write time: |
Indicates the time taken for singleblock writes to this file during the last measurement period. |
Secs/write |
In case of a singleblock write, a write call results in a single 8KB block being written into the file. If a lot of data is to be written to a file, single block writes can significantly increase I/O processing overheads and related resource costs. A very high value of this measure could indicate a bottleneck when processing single block write requests to a particular file. Compare the value of this measure across files to accurately identify that file to which a single block of data was written most slowly. |
Sync read latency: |
Indicates the average latency for singleblock synchronous reads for single request since last test cycle on this file. |
Msecs/request |
If there is a high latency for critical files, you may want to consider relocating these files to improve their service time. |
IO time since last measure: |
Indicates the time taken by IOPS on this file during the last measurement period. |
Secs |
A high value could indicate a processing bottleneck. Compare the value of this measure across files to identify that file, the reads and writes to which take the maximum time. |