This content has been marked as final. Show 18 replies
consistent gets is when you request a block that recently changed by transaction but haven't been committed. Or you started a transaction before another transaction commit a change to the block. To keep transaction consistency, Oracle need to construct or clone the block using rollback segment.
a db block get is just a regular database block read
wrong statement. check later posts.
Edited by: yingkuan on Sep 2, 2008 11:37 PM
A recent thread explained these statistics more clearly than I have seen in recent memory. Take a look at this thread, I believe that the original descriptive explanation was provided by Mark Bobak:
insert a row results in consistent get.
Is it possible that you have described consistent changes, rather than consistent gets?:
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Anand and others,
I think I got the db block get part quite incorrect.
Mark Bobak's original explanation is more clear,
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).
These are some excerpt from the link you provided.i've read the link. but i couldn't get better idea.
A consistent get is a block gotten in read consistent mode (point in time mode). It MAY or MAY NOT
involve reconstruction (rolling back).
Db Block Gets are CURRENT mode gets -- blocks read "as of right now".
isn't it confusing?
i select a data i'll see it if it is in cache or a physical read whatever... so this is almost as good as "data point in time" & "datat as of right now"...
consistenet READ may invlove UNDO. but what is the thing that differentiate "consistenet gets" and "db gets"?
No its not the same.Its much simple when see the block from the cache only but gets a bit complex when its checkpointed. When the blck is modified, the block's transaction header is changed to update the transaction SCN,SCN which denotes the time of the start of the transaction and also a bit stating that the transaction is active in this block is set. Now this is a dirty block. If you are the owner of the transaction and are chaning it, when you would come again and try to modify the same block for next update, you need this image that is "at the moment" available.
For example, you got a block with value 1.You changed it to 2.Now when you would ome again and change this value to 3, what you need 2 or 1?If you said 2 than that's the db block get,block gotten in the current mode!
In case you are a visitor, trying toread a block which has an active transaction going on, thanks to Oracle's Read consistancy mechanisms, you would read 1, a consistent image since the transaction started.
"consistent get" is not really " "data point in time" & "datat as of right now"... ".
A query involves consistent gets for the table blocks, whether they have been updated or not -- thus, whether rollback is really required or not.
I am sure that you know about queries starting before updates and therefore requiring rollback. What else requires a consistent get ?
Consider this scenario :
time t0 : Session 121 for user 'A' begins an UPDATE. The UPDATE updates 50 blocks and takes upto time t10 to run.
time t1 : Session 126 for user 'C' begins a query that spans 6 tables only one of which is updatd by Session 121.
time t3 : Session 124 for user 'B' reads a block. It finds that it has been updated but not commited by Session 120. This query has to "rollback" the block for a read consistent image -- even though this query started after the UPDATE began.
time t8 : Session 126 for user 'C' reads a block from the same table that session 121 is updating. This block has not been updated and probably will not be among the 50 blocks that Session 121 will update totally.
Will Session 126 for user 'C' need to make a 'consistent get' ? YES. Because at the time it began (t1) it did NOT know whether it (will* be able to get a read consistent image. Session 126's query might run for 300 time intervals. Within those 300 intervals, there might be 50 different sessions updating different blocks. But Session 126 doesn't know if the block it visits (will visit !) has been updated since time t1 until it actually reads the block. Therefore, it know that it must be prepared to rollback some of the block images. It may not need to rollback all the block images but it may need to rollback some images.
On the other hand a CURRENT get is one for a block a session is updating. Session 121 for user 'A' will be doing CURRENT gets of blocks. In the process of doing CURRENT gets, it may well do 'consistent gets' as well !
Here's the complete text of the answer I originally wrote nearly 5 years ago on the Oracle-L mailing list:
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!
Hope that helps,
PS The original question asked about latching as well, which explains the reason for the third paragraph.
Edited by: mbobak on Sep 2, 2008 11:07 PM
Thanks for explanation.
so this means Consistenet GETS & Consistenet READS are same?
In case you are a visitor, trying toread a block which has an active transaction going on, thanks to Oracle's Read >>consistancy mechanisms, you would read 1, a consistent image since the transaction started.