Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Logical I/O and Physical I/O in Oracle

Oracle Tips by Rohit Gupta
December 29,  2010

Tips from the trenches by Rohit Gupta

Being a DBA, most of us have been regularly talking about and dealing with Logical I/O and Physical I/O as part of performance tuning discussions and issues. I have asked questions like - What actually is a logical I/O? What is a physical I/O?  Which is better? etc. ? to many experienced DBAs and practitioners. This paper is a collection of the answers I got, my research and my own experience on these topics over last few years.  

What is a Physical I/O??

 Whenever you execute a query, Oracle has to go and fetch data to give you the result of the query execution. Here, data means the actual data in data blocks. Whenever a new data block is requested, it has to be fetched from the physical datafiles residing on the physical disks. This fetching of data blocks from the physical disk involves an I/O operation known as physical I/O. By virtue of this physical I/O, now the block has been fetched and read into the memory area called buffer cache. This is a default action.  

We know that a data block might be requested multiple times by multiple queries. So what happens when the same data block is requested again by the some other user?  

What is a Logical I/O??  

Once a physical I/O has taken place and the block has been read into the memory, the next request for the same data block won?t require the block to be fetched from the disk and hence avoiding a physical I/O. So now to return the results for the select query requesting the same data block, the block will be fetched from the memory and is called a Logical I/O.  

Whenever the quantum of Logical I/O is calculated, two kinds of reads are considered ? Consistent reads and Current reads. Jointly, these 2 statistics are known as Logical I/O performed by Oracle. Let us look at these separately to better understand the concept of Logical I/O.  

Consistent reads  

It is a well known fact that whenever a change is induced in a data block, the old data/entry is written to the UNDO/ROLLBACK segments. From the fundamentals of UNDO, we also know that this is to provide a read consistent view of the data block to other users trying to read the same data block.  

Consistent reads mean reading the block in a consistent mode w.r.t a ?point in time?. Here the phrase ?point in time? means the time when the query/statement began. A consistent read might or might not involve any UNDO data. UNDO data will be applied when it is necessary to roll back a data block to the required ?point in time? when the SQL statement was fired. If on reading the buffer cache, it is found that the data block is already in the required state, no UNDO data is required because the block is already consistent.  

I have referred to the phrase ?state of data block? several times. Let me pause here and explain what I actually mean. The ?state of data block? is basically determined by the SCN. When the SQL statement is issued the SCN number is noted. Then Oracle scans all data blocks (needed for the transaction) and checks which blocks have SCN higher than the locked SCN. For such data blocks, UNDO data is used to roll back to the locked SCN and hence provide a consistent read.  

Consistent reads and array size  

Consistent reads could also depend on and vary with the array size setting of SQLPLUS. The default value is 15. Array size is the number of rows fetched in a single read. The value of array size is an indicator of the number of network round trips made to fetch the required data from Oracle. A careful adjustment of array size value can improve performance by reducing the network round trips. A higher array size might be good for performance of queries (by reducing the network round trips and also the consistent reads) but too high value also uses more memory. However, array size is not a setting restricted to SQLPLUS; it can be set in many other applications requesting data from oracle database.  

Now since we know the dependency on array size, let us extend our discussion to understand a rule for estimating the amount of consistent gets.  

Say our table spans across ?N? number of data blocks (space usage can be calculated using show_space). Now in an ideal condition, the numbers of consistent gets to do a full table scan of this table should be about ?N? i.e every block read at least once. But here comes into picture the array size and number of rows in the table. Suppose the array size is ?A? and the number of rows in the table ?R?. Hence, the rule of thumb to estimate the consistent gets is R/A+N. This means that the block needs to be visited in consistent mode R/A times plus once for each block i.e N. The expression R/A means the number of trips needed to fetch all the rows because in 1 trip, only ?A? rows (array size) can be fetched.  

Current reads  

Current reads mean getting the data block(s) as it exists (i.e the ?as of now? copy). Unlike consistent gets, there is no concept of ?point in time? in case of current reads. Current read gets the data block in the current shape. The difference between current and consistent gets can be best understood by considering a DML statement as explained below.  

In case of DMLs ? There is at least one consistent read performed by Oracle (this is called the read phase of the DML statement) to check which rows need to be modified (to ensure that only that data is modified which was there when the DML was fired). This means that Oracle will try and get the state of data blocks as they were when the DML statement was fired and then perform the DML action on that data. And then there are subsequent current reads to actually get the current state of all those rows and modify them as per the consistent reads (point in time data) which happened earlier. More the number of rows to be altered more are the current reads.  

In case of Select statements ? There could be any number of current reads and consistent gets. This will depend on the number of blocks that have been modified and how many of them need to have UNDO data applied to create a consistent copy and provide the result of the Select statement. There will be some consistent reads performed while reading the segment header and if there are no modifications to any of the requested blocks, rest would be current reads. 

What they actually mean in terms of performance??

 As part of this whole discussion, it is also important to know what logical I/O and physical I/O mean in terms of performance. A physical I/O is not good for performance of queries ? well, that?s quite obvious. Whenever a physical I/O takes place, Oracle tries to read the data block from the disk which means that number of factors like disk speed etc. get involved and play a role in deciding the response time etc. Oracle has now to depend on the external hardware resource like hard disks, network etc. to get you the results. The goal hence is to avoid physical I/O as far as possible. But at the same time, it won?t be apt to generically comment that logical reads are always good. Though desired in many cases, excess of everything is bad and logical I/O is no exception to this rule.  

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 doesn?t 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. First reason is that a logical read might have resulted in a physical read to fetch the data block into the buffer cache. Second, 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.  

Place holders for these statistics  

Having talked about I/O, let us see where in the database these stats are available and how to interpret them.  

AUTOTRACE  

AUTOTRACE is a utility used to get the execution path and execution statistics of a SQL statement. Here are some important stats worth knowing:  

  • DB Blocks Reads ? Current Reads, i.e the state of the block as it exists
  • Consistent Reads ? blocks read from the buffer cache in a consistent state
  • Logical Reads ? DB Blocks Reads + Consistent Reads
  • Physical Reads ? blocks read from the disk  

TKPROF  

TKPROF stats are considered more accurate as compared to AUTOTRACE as it the part of the actual output of the transaction/query where as AUTOTRACE provides more of the speculative or estimated statistics. Here are the stats worth some interest relevant to our current discussion:  

  • DISK ? number of physical reads
  • QUERY ? number of buffers read in consistent mode
  • CURRENT ? number of buffers read in current mode  

Generating and reading AUTOTRACE and TKPROF reports is all together a different topic and hence we won?t invest time and words in discussing them here as part of this paper.  

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');

 

*******************************************************************************************

Contributed by:

 

Name: Rohit Gupta
Current title : Technology Architect
Current Organization : Infosys Ltd.
Overall DBA Experience : 11+ yrs
Certifications :OCP 9i, 10g,11g DBA, ITIL foundation, Oracle Certified Exadata Implementation Specialist

 

Blogs:

http://rohitguptaoracletips.blogspot.com/

http://rohitguptastandbydbtips.blogspot.com/

 

Key Skills: RMAN, DATAGUARD, RAC, Installations and Upgrades


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.