oracle consistent gets,What is Oracle consistent gets?

What is Oracle consistent gets?

http://www.dba-oracle.com/m_consistent_gets.htm

The

consistent getsOracle 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 "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

RBS). So this reconstruction from RBS takes more resources (reads

actually), which will end up as high consistent gets."

By eygle on 2009-03-12 11:47 |

Comments (6) |

Oracle摘 | 2227 |