Thanks. I was having a bout of insomnia last night, so I'm just
glad it came out sounding coherent! ;-)
Mark,
That
is perhaps the most concise and easy to understand
explanation of current mode vs. consistent mode that I
have
yet seen.
Thanks for posting it.
Jared
|
| "Bobak, Mark"
<Mark.Bobak@il.proquest.com>
Sent by: ml-errors@fatcity.com
12/17/2003 11:24 PM
Please respond to ORACLE-L
|
To:
Multiple recipients of list ORACLE-L
<ORACLE-L@fatcity.com>
cc:
Subject: RE: db block gets /consistent
gets |
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-----
Sent:
Thu 12/18/2003 1:39 AM
To:
Multiple recipients of list ORACLE-L
Cc:
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@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@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).
--
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 - 12:04:30 CST
