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 


 

 

 


 

 

 
 

 

Oracle Metric consistent gets

Oracle Tips by Burleson Consulting

 

The consistent gets Oracle metric is the number of times a consistent read (a logical RAM buffer I/O) was requested to get data from a data block. Part of Oracle tuning is to increase logical I/O by reducing the expensive disk I/O (physical reads), but high consistent gets presents it's own tuning challenges, especially when we see super high CPU consumption (i.e. the "top 5 timed events" in an AWR report).

Tuning Consistent Gets

Many shops with super-high consistent gets have high CPU consumption and this is quickly fixed by adding more CPU's to the server. Note that Oracle expert Kevin Closson sees "buffer chains latch" thrashing (latch overhead) as a major contributor to high CPU consumption on highly-buffered Oracle databases (e.g. 64-bit Oracle with a 50 gig db_cache_size): 

" The closer a system gets to processor saturation, the more troublesome latch gets become--presuming the chain is hot.

While cache buffers chains latch thrashing may seem like a nebulous place to put blame for high processor utilization, trust me, it isn't.". 

Types of Consistent Gets

Not all buffer touches are created equal, and Oracle has several types of "consistent gets", the term used by Oracle to describe an Oracle I/O that is done exclusively from the buffer cache.  Oracle AWR and STATSPACK reports mention several types of consistent gets, all undocumented:

  • consistent gets

  • consistent gets from cache

  • consistent gets - examination

  • consistent gets direct

Some Oracle experts claim that these undocumented underlying mechanism can be revealed and that these consistent gets metrics may tell us about data clustering  Mladen Gogala, author of "Easy Oracle PHP" makes these observations about consistent gets:

"The [consistent gets] overhead is the time spent by Oracle to maintain its own structures + the time spent by OS to maintain its own structures. So, what exactly happens during a consistent get in the situation described? As I don't have access to the source code, I cannot tell precisely, with 100% of certainty, but based on my experience, the process goes something like this:

1) Oracle calculates the hash value of the block and searches the SGA hash table for the place where the block is located.

2) Oracle checks the SCN of the block and compares it with the SCN of the current transaction. Here, I'll assume that this check will be OK and that no read consistent version needs to be constructed.

3) If the instance is a part of RAC, check the directory and see whether any other instance has modified the block. It will require communication with the GES process using the IPC primitives (MSG system calls). MSG system calls are frequently implemented using device driver which brings us to the OS overhead (context switch, scheduling)

4) If everything is OK, the block is paged in the address space of the requesting process. For this step I am not exactly sure when does it happen, but it has to happen at some point. Logically, it would look as the last step, but my logic may be flawed. Here, of course, I assume a soft fault. Hard fault would mean that a part of SGA was swapped out.

All of this is an overhead of a consistent get and it is the simplest case. How much is it in terms of microseconds, depends on many factors, but the overhead exists and is strictly larger then zero. If your SQL does a gazillion of consistent gets, it will waste significant CPU power and time to perform that."

For more insights on consistent gets, we see expert Kevin Closson who has a great description of the internal mechanisms within consistent gets.  Kevin goes on to describe the internals of a consistent get:

"The routine is kcbget() (or one of his special purpose cousins). It doesn't really "search" a hash *table* if you will.  A hash table would be more of a "perfect hash" structure and to implement that, every possible hash value has to be known when the table is set up. That would mean knowing every possible database block address.

Instead, it hashes to a bucket that has similar hashed dbas chained off off it in a linked list. So it is more of a scan of the linked list looking for the right dba and right version of it.

The particulars of the structures under a get are not as important as remembering that before walking that chain, the process has to obtain the latch on the chain. "

Consistent gets - examination

Mike Ault notes that "consistent gets - examinations" are related to buffer management overhead and data access overhead such as index reads and undo writes:

"consistent gets – examination is from reading something like undo blocks…

Other examples of "consistent gets – examination" are: reading the root block of an index, reading an undo block while creating a consistent read data block, reading a block in a single table hash cluster - unless it is found to have the ‘collision flag’ set."

Steve Karam, OCM notes about "consistent gets - examination":

"Consistent gets - examination are a different kind of consistent get  that only requires a single latch, saving CPU.  The most common use of a consistent get - examination is to read undo blocks for consistent read purposes, but they also do it for the first part of an index read and in certain cases for hash clusters.

So if you're doing a query on a couple tables that are mostly cached, but one of them has uncommitted DML against it at the time, you'll do consistent gets for the standard data in the cache, and the query will do consistent gets - examination to read the undo blocks and create read consistent blocks; this doesn't necessarily save CPU unfortunately, because while the consistent gets - examination only acquire one latch,  creating the read consistent data block also takes a latch.

However, I think that when you use single table hash clusters (or the new 10g Sorted Hash Clusters I mentioned once that automatically sort by a key so they don't need order by) you can get a performance gain, because reads from the blocks of a hash cluster are usually consistent get - examination, therefore they only need one latch instead of two. "

Interpreting consistent gets in reports

Here is a STATSPACK (pr AWR) report  we see displays for "consistent gets" and "consistent gets - examinations": 


Statistic                         Total              per Second     per Trans
--------------------------------- ------------------ -------------- -----------
consistent gets                           35,024,284        9,718.2      3,703.9

consistent gets - examination             12,148,672        3,370.9      1,284.8

An Oracle FAQ's forum had a problem in which a user had trouble, "when we run set autotrace on or similar execution statistics."  The problem was resolved in part with this advices: "consistent gets is the blocks in consistent mode (sometimes reconstructed using information from Undo logs). So this reconstruction from UNDO (rollback segments)  takes more resources (reads actually), which will end up as high consistent gets."

Consistent gets and many other Oracle performance metrics are discussed in my book "Oracle Tuning" by Rampant TechPress. 

 
 
 
Get Complete Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Third Edition" has been updated with over 800 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle performance and you can buy it for 40% off directly from the publisher.
 


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

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.