1 2 Previous Next 18 Replies Latest reply: Sep 3, 2008 8:50 PM by NitinJoshi RSS

    consistent gets and db block gets

    Anand...
      Hi...


      I wanted to know the difference between consistent gets and db block gets in v$sess_io.I have read that consistent gets is the blocks in consistent mode..so here what does consistent mode means????





      Thanks in Advance,
      Anand
        • 1. Re: consistent gets and db block gets
          247514
          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
          • 2. Re: consistent gets and db block gets
            Charles Hooper
            Anand,
            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.

            yingkuan,
            Is it possible that you have described consistent changes, rather than consistent gets?:
            http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/stats002.htm

            Charles Hooper
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: consistent gets and db block gets
              601585
              Yingkuan's comment is conceptually correct.

              But in practice, Oracle does consistent read even on commited blocks for select operation.
              For uncommitted blocks, Oracle should do more consistent reads due to rollback.

              You can easily prove the concepts through v$sesstat view.

              Dion Cho
              • 4. Re: consistent gets and db block gets
                26741
                a db block get is just a regular database block read
                isn't correct. A 'db block get' is a CURRENT get. That is why it is listed seperately from 'consistent gets' (although Oracle doesn't call it 'current gets').
                • 5. Re: consistent gets and db block gets
                  247514
                  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).
                  • 6. Re: consistent gets and db block gets
                    Aman....
                    Anand,
                    Read this thread,
                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514
                    Aman....
                    • 7. Re: consistent gets and db block gets
                      NitinJoshi
                      Hi Aman,
                      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"?

                      Regards!
                      • 8. Re: consistent gets and db block gets
                        Aman....
                        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.
                        HTH
                        Aman....
                        • 9. Re: consistent gets and db block gets
                          26741
                          "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 !
                          • 10. Re: consistent gets and db block gets
                            mbobak
                            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,

                            -Mark

                            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
                            • 11. Re: consistent gets and db block gets
                              NitinJoshi
                              Hi,
                              Thanks for explanation.
                              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.
                              so this means Consistenet GETS & Consistenet READS are same?

                              Regards!
                              • 12. Re: consistent gets and db block gets
                                Aman....
                                Hemant,
                                +. In the process of doing CURRENT gets, it may well do 'consistent gets' as well !+
                                Can you be more elaborative on this? Why would a current get require to go for a consistent get or rollback of the data?
                                Aman....
                                • 13. Re: consistent gets and db block gets
                                  Aman....
                                  Oralad,
                                  Consistent get is the request for the read consistent image and read is the access of that image. Both refer to that point where the block was consistent.
                                  Aman....
                                  • 14. Re: consistent gets and db block gets
                                    26741
                                    Read Tom Kyte's answers at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514
                                    1 2 Previous Next