Differences – consistent gets , physical reads and db block gets





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.

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

"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."

"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. "
 what is the difference between DB Block Gets, physical reads and Consistent Gets?

– Physical Reads are the number of actual reads from the I/O subsystem.
– Consistent Gets and DB Block gets together are the number of block reads from Block Buffer Cache.

 what ARE Consistent Gets and DB Block Gets and what is the difference?
 consistent gets from cache – Number of times a consistent read was requested for a block from the buffer cache.
 db block gets from cache – Number of times a CURRENT block was requested from the buffer cache.

consistent gets is the blocks in consistent mode (sometimes reconstructed using information from RBS). So this reconstruction from RBS takes more resources (reads actually), which will end up as high consistent gets.
db block gets is the blocks in current mode (whatever it is NOW).”

Block in current mode is  Oracle internally getting data where it does not have to bother checking for reconstructing the data from rollback information. Oracle can just get whatever is currently correct.
In summary.

Consistent Gets – a normal reading of a block from the buffer cache. A check will be made if the data needs reconstructing from rollback info to give you a consistent view but most of the time it won’t.
DB Block Gets – Internal processing. Don’t worry about them unless you are interested in Oracle Internals and have a lot of time to spend on it.
Physical Reads – Where Oracle has to get a block from the IO subsystem
From a performance perspective:-
Reducing Physical IO {if there is any} is very often a very good way to make a statement run a lot faster.
Reducing Consistent Gets is often a good way to make a statement run faster.
Reducing DB Block Gets is probably beyond what you can do (except as a side effect of changes to the execution plan). It won’t make a lot of difference.

Author: admin