What is Logical I/O and Physical I/O in Oracle Database



Last updated on August 16th, 2015 at 07:56 am

Many times we get confused with Logical I/O and Physical I/O in Oracle Database.
I am trying to clear all the doubt about what is Logical I/O and Physical I/O in Oracle Database
What is Logical I/O

  • It is the data block accessed from Buffer Cache
  • Its statistics in captured in db block get, consistents gets, bufer is pinned count, session logical read
  • LIO may have incurred a PIO in order to get into the cache in the first place
  • Db block gets is the state of the block as it exists
  • consistents gets  is the blocks read from the buffer cache in a consistent state
  • This stats is clearly visible  as db block gets and consistent gets in Autotrace executed
  • We can find this for statement from cr+cu in raw trace data
  • This stats is clearly visible as query and current in tkprof output

What is Physical I/O

  • it is data block requested from Physical disks or Operation system
  • It could be from Operation system cache
  • Data blocks must be placed into memory in order to be accessed to
    satisfy the acquisition of rows for a query’s result set
  • Its statistics in captured in physical reads statistics
  • This stats is clearly visible as physical reads in Autotrace executed
  • We can find this for statement from pr in raw trace dat
  • This stats is clearly visible as disks in tkprof output

V$SYSSTAT

Use the following query (or similar) to get the information from this
dictionary view:

select name, value from v$sysstat where name in (‘consistent
gets’,’db block gets’,’physical reads’);
Which is better logical I/O and physical I/O  in terms of performance
 A physical I/O is not good for performance of queries . Whenever a physical I/O takes place, Oracle tries to read the data block from the disk which will be slow. The goal hence is to avoid physical I/O as far as possible.
A Logical I/O is considered better for performance (when compared to Physical I/O) because the reads happen from the memory as the data block is pre-fetched from the disk. So now Oracle does not  need to go to disk to fetch blocks for your query results. But it is important to note that excess of Logical reads (I/O) is also not good or recommended. There are many reasons for that
1) a logical read might have resulted in a physical read to fetch the data block into the buffer cache.
2) every time a block is read from the cache, a lock or latch is acquired on the cache and hence higher logical reads could mean that there are high chances of buffer cache contention.
So our goal should be to access least number of logical I/O for queries inorder to improve its performance

Leave a Reply