What is Logical I/O (read) and Physical I/O(read) in Oracle Database

Many times we get confused with Logical I/O and Physical I/O in Oracle Database. Logical I/O and Physical I/O is called Logical reads and Physical reads respectively

I am trying to clear all the doubt about what is Logical read in oracle and Physical I/O in Oracle Database

What is Logical I/O (LIO) in Oracle

• 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

SQL> create table TEST as select * from dba_objects where rownum < 100000; 

SQL> set autotrace traceonly SQL> select * from TEST where object_name = 'FND_USER'; 
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 1416 | 156 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| VIRTUAL_TEST_T | 8 | 1416 | 156 (2)| 00:00:02 |

Statistics
0 recursive calls
0 db block gets
15 consistent gets
3 physical reads
0 redo size
408 bytes sent via SQL*Net to client
355 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

How to Read the statistics
Logical Reads = Consistent Gets + DB Block Gets
Logical Reads = 15 + 0
Logical Reads = 15
Rows Processed = 1
That means we had to read 15 blocks to find 1 rows

What is Physical I/O(PIO)

• 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 file
• 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. This gives the stats from database start time.

select name, value from v$sysstat where name in ('consistent
gets','db block gets','physical reads');

If you want the delta, then this need to be calculated at two interval and then delta can be found out

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

• A logical read might have resulted in a physical read to fetch the data block into the buffer cache.
• 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 in-order to improve its performance

Queries for Logical Reads and Physical reads

If we want to find the logical reads and physical reads per sec at any point , we can use below queries

Logical Read

select * from V$SYSMETRIC where metric_name = 'Logical Reads Per Sec';

It returns two rows .The first row is the the last minute (ie 59.59 seconds) and the second row is the last 15 seconds (ie 15.15 seconds)

select * from V$SYSMETRIC_SUMMARY where metric_name = 'Logical Reads Per Sec';

It returns average, maximum, minimum for the values for the last hour
 
select * from V$SYSMETRIC_HISTORY where metric_name = 'Logical Reads Per Sec';
It returns for date for  the last hour for 60 second intervals and for the last 3 minutes the 15 second intervals

select * from DBA_HIST_SYSMETRIC_SUMMARY where metric_name = 'Logical Reads Per Sec';

Physical Reads

select * from V$SYSMETRIC where metric_name = 'Physical Reads Per Sec';
select * from V$SYSMETRIC_SUMMARY where metric_name = 'Physical Reads Per Sec';
select * from V$SYSMETRIC_HISTORY where metric_name = 'Physical Reads Per Sec';
select * from DBA_HIST_SYSMETRIC_SUMMARY where metric_name = 'Physical Reads Per Sec';

I hope you like this article on what is logical read in oracle, Physical reads

Also Reads
How to turn on trace in Oracle : SQL trace or 10046 event is being used to trace the session activity in Oracle. The output generated by SQL trace is formatted using trcsess, tkprof utility
Oracle Explain Plan : All about Explain Plan in Oracle, How to read oracle explain plan for performance related issue, how to find the explain plan for query in cursor
Oracle Performance tuning Glossary :Check out Oracle Performance tuning Glossary to learn about most common performance tuning terms like Optimizer, Autotrace, Cardinality
Oracle performance tuning Certification Quiz : Test your performance tuning knowledge using the MCQ given
https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/osm_instance_help/osm_instance_instance_disk_performance_io_ps.html