Oracle Database - Physical Read

Card Puncher Data Processing

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.

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 ?





Discover More
Card Puncher Data Processing
Oracle Database - How to detect read from the temporary tablespace ?

How to detect read from the temporary tablespace ? Step followed : disable automatic PGA management by the server set the hash area size. and play with the sort area size In this example, the...
Card Puncher Data Processing
Oracle Database - Physical I/O

A physical read, I/O 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 logical io....
Card Puncher Data Processing
Oracle Database - Read

single block reads = sequential reads in Oracle Term
Card Puncher Data Processing
Oracle Database - Session Statistics (V )

The second part of the autotrace output are a part of the session statistics. V recursive_calls : Number of SQL...



Share this page:
Follow us:
Task Runner