Metric consistent gets
Oracle Tips by Burleson Consulting
consistent gets Oracle metric
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
" 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:
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
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 -
"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
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. "
consistent gets in reports
Here is a STATSPACK (pr AWR)
report we see displays for "consistent gets" and
"consistent gets - examinations":
per Second per Trans
consistent gets 35,024,284
gets - examination 12,148,672
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
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.