consistent get,db block get

Link:http://h11h99.blog.51cto.com/230273/42860

Oracle accesses blocks in one of two modes, current or consistent.
A 'db block get' is a current mode get.  That is, it's the most up-to-date
copy of the data in that block, as it is right now, or currently.  There
can only be one current copy of a block in the buffer cache at any time.
Db block gets generally are used when DML changes data in the database.
In that case, row-level locks are implicitly taken on the updated rows.
There is also at least one well-known case where a select statement does
a db block get, and does not take a lock.  That is, when it does a full
table scan or fast full index scan, Oracle will read the segment header
in current mode (multiple times, the number varies based on Oracle version).
A 'consistent get' is when Oracle gets the data in a block which is consistent
with a given point in time, or SCN.  The consistent get is at the heart of
Oracle's read consistency mechanism.  When blocks are fetched in order to
satisfy a query result set, they are fetched in consistent mode.  If no
block in the buffer cache is consistent to the correct point in time, Oracle
will (attempt to) reconstruct that block using the information in the rollback
segments.  If it fails to do so, that's when a query errors out with the
much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".
As to latching, and how it relates, well, consider that the block buffers
are in the SGA, which is shared memory.  To avoid corruption, latches are
used to serialize access to many linked lists and data structures that point
to the buffers as well as the buffers themselves.  It is safe to say that
each consistent get introduces serialization to the system, and by tuning
SQL to use more efficient access paths, you can get the same answer to the
same query but do less consistent gets.  This not only consumes less CPU,
it also can significantly reduce latching which reduces serialization and
makes your system more scalable.

buffer_gets=db block gets + consistent gets = LOGIC IO(逻辑读次数）.

consistent gets : 通过不带for update的select 读的次数
db block gets : 通过update/delete/select for update读的次数.

consistent get : 在一致读模式下所读的快数，包括从回滚段读的快数。
db block gets ： 在当前读模式下所读的快数，比较少和特殊，例如数据字典数据获取，在DML中，更改或删除数据是要用到当前读模式。

consistent gets :consistent_gets是从回滚段中读到的前映（或叫读取一致性影象）, 看见的数据是查询开始的时间点的，所以若存在block在查询开始后发生了变化的情况，则必须产生 before image 然后读数据，这就是一致读的含义
查询就是表示 consistent gets (query mode)，因为查询要保证所获取的数据的时间点的一致性，所以叫一致读，即使是从当前 buffer 获得的数据，也叫 consistent gets ，这仅仅表达一种模式一种期望，并不表示真实的是从 当前buffer 获得 还是从回滚段获取数据产生的 bufore image 。

db block gets : current mode , 不管这个块上的数据是否可能存在 before image ,也就是说不管是否存在回滚中数据可以 回滚，只看见当前最新块的数据，即使别人正在更新，也看见别人更新状态的数据，比如dml的时候就不需要看见别人更改前的数据，而是看见正在更改的，当然同时，若操作相同数据则被lock住。也就是说一次查询中看见的数据可能不在同一个时间点上，比如一个大的dml，当dml 开始更新一个非常大的表后，这个表更新的过程中，有一个进程去把该表末尾的一个记录更新了，然后这个大更新抵达该记录的时候会被阻塞的，若该进程事物提交，则大更新会覆盖该事务的更新，也就是说，这个大更新所看见的数据是当前的，不具有时间点的一致性，所以叫 current mode，个人认为db block gets这个词用的不好, 容易让人误解. 如果改成inconsistent gets可能会更准确一些

consistent gets
db block gets + consistent gets = logical io (as opposed to physical io). consistent gets are current mode gets. This might entail a reconstruction of the block with the undo (rollback) mechanism.

Number of times a consistent read was requested for a block.

db block gets
db block gets + consistent gets = logical io (as opposed to physical io). db block gets are current mode gets, blocks that are read as they are (even if these are being modified by another session)

Number of times a CURRENT block was requested

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

The consistent gets from cache Oracle metric is the number of times a consistent read was requested for a block from the buffer cache.

set pages 999 set lines 92 ttitle 'Contents of Data Buffers' drop table t1; create table t1 as select    o.owner          owner,    o.object_name    object_name,    o.subobject_name subobject_name,    o.object_type    object_type,    count(distinct file# || block#)         num_blocks from    dba_objects  o,    v$bh         bh where    o.data_object_id  = bh.objd and    o.owner not in ('SYS','SYSTEM') and    bh.status != 'free' group by    o.owner,    o.object_name,    o.subobject_name,    o.object_type order by    count(distinct file# || block#) desc ;

 

from:

http://xgss2000.blog.163.com/blog/static/2328860820094110648909/