Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: db block gets /consistent gets

RE: db block gets /consistent gets

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 17 Dec 2003 23:24:29 -0800
Message-ID: <F001.005DA360.20031217232429@fatcity.com>


Syed,

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.

Well, that turned out longer than I planned. If you're still reading, I hope it helped!

-Mark

-----Original Message-----

From:	Sultan Syed [mailto:ssyed_at_fine.ae]
Sent:	Thu 12/18/2003 1:39 AM
To:	Multiple recipients of list ORACLE-L
Cc:	
Subject:	db block gets /consistent gets

Hi list,  

What is db block gets and consistent gets.? How can I reduce consistent gets ?
Ask Tom says each consistent gets is latch, how it could be? Thanks in advance  

Syed      

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Bobak, Mark
  INET: Mark.Bobak_at_il.proquest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Dec 18 2003 - 01:24:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US