Oracle Database - Physical Read

About

The physical read statistic from the autotrace statistics is a measure of how much real I/O, or physical i/o, your query performed.

A physical read of table or index data places the block into the buffer cache. Then we perform a logical IO to retrieve the block. Hence, most physical read are immediately followed by a buffer read.

There are two major types of common physical I/Os :

• reading the data in from data files. Doing I/O to the data files to retrieve index and table data. These operations will be followed immediately by a buffer read to the cache.
• direct read from the temporary tablespace. This in response to a sort area or hash area not being large enough to support the entire sort/hash in memory. The database is forced to swap out some of the data to the temporary tablespace and read it back later. These physical reads bypass the buffer cache and will not incur a logical I/O.

Articles Related

How to reduce the Physical read ?

Run the query twice

Normally, the number of physical read must go down for most queries after your run the query once.

If you run a small query (a query that performs hundred of buffer (consistent_get) and you repeatedly observe physical I/O being performed, that could be an indication of your buffer cache is too small and indicate direct reads from the temporary tablespace. There isn't sufficient space to cache even the results of your small query with hundred of logical I/Os.

Example how to detect read from the temporary tablespace ?

Oracle Database - How to detect read from the temporary tablespace ?