Oracle Database - Physical Read
Table of Contents
1 - About
There are two major types of common physical I/Os :
- 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.
2 - Articles Related
3 - How to reduce the Physical read ?
3.1 - 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.