8 Replies Latest reply: Nov 27, 2012 3:20 AM by Aman.... RSS

    multiversioning: limit of versions kept?

    946279
      Oracle uses undo information to reconstruct a block for a query when neccessary. then this reconstructed block is sitting in buffer cache so that another query that needs the same reconstruction just takes it from there; reconstruction is not repeated.

      Question: is there a limit that says what number of block versions per block can be kept in buffer cache at the same time?

      thanks
        • 1. Re: multiversioning: limit of versions kept?
          Mohamed Houri
          Oracle uses undo information to reconstruct a block for a query when neccessary. then this reconstructed block is sitting 
          in buffer cache so that another query that needs the same reconstruction just takes it from there; reconstruction is not repeated
          I imagine that you are speaking about reconstructing a block for a read consistency purpose. If so, where did you read that in this case the _reconstructed block
          is sitting in buffer cache_ ?

          I think that a read consistency needs a copy of the data block to be made in the buffer cache on wich(the copy) we will apply the necessary
          undo records to reconstruct the block image as it was when the query started. This re-worked copy of the block is not kept in memory as far as I know . It will be removed very rapidly

          Best regards

          Mohamed Houri
          www.hourim.wordpress.com
          • 2. Re: multiversioning: limit of versions kept?
            946279
            Tom Kyte, expert oracle architecture, second edition, page 258: "oracle has ability to store multiple versions of the same block in the buffer cache"
            • 3. Re: multiversioning: limit of versions kept?
              John Stegeman
              @Mohammed The reconstructed block is indeed saved in the buffer cache.

              @OP there is no limit, other than the size of your buffer cache.
              • 4. Re: multiversioning: limit of versions kept?
                946279
                thanks! is there a way "to see" those versioned blocks? for example to tell how many multiversioned blocks are there for emp table?

                Edited by: 943276 on 2012-11-27 09:31
                • 5. Re: multiversioning: limit of versions kept?
                  John Stegeman
                  What problem exactly are you trying to solve?

                  http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1069.htm#REFRN30029
                  • 6. Re: multiversioning: limit of versions kept?
                    946279
                    I'm not trying to solve anything. I'm trying to understand.
                    • 7. Re: multiversioning: limit of versions kept?
                      Mohamed Houri
                      Tom Kyte, expert oracle architecture, second edition, page 258: "oracle has ability to store multiple 
                      versions of the same block in the buffer cache" 
                      Ok. I read this pages and immediately said to my self. Yes!!! and this has been very obvious for me since several years.

                      But stopped a minute and said again : then what makes me thinking differently?

                      After a while, I remember having read the following phrase in Jonathan Lewis new excellent book "Oracle Core Essential Internals for DBAs and Developpers" page 21.

                      *"There is an important difference between read consistency and rolling back, of course. For read consistency we make a copy of the data block in memory and apply undo records to that block,* *and it's a copy of the block that we can discard very rapidly once we've finished with it; when rolling back we acquire......"*

                      I certainly not have clearly understood what is the exact meaning of this "*copy of the block*". Does this copy of the block is just used as a temporary area in which we reconstruct the final consitent image of the block and then copy (again) this reconstruced image into the block that will be kept in the buffer cache and discard the working copy of the block we initialy made ?

                      Best regards

                      Mohamed Houri
                      www.hourim.wordpress.com
                      • 8. Re: multiversioning: limit of versions kept?
                        Aman....
                        Does this copy of the block is just used as a temporary area in which we reconstruct the final consitent image of the block and then copy (again) this reconstruced image into the block that will be kept in the buffer cache and discard the working copy of the block we initialy made ?
                        I believe its not a temporary area but just a buffer(any) in the buffer cache , over which the Undo image is applied based on the SCN requested by the session asking for it and is labelled as the CR(Consistent read) block. These blocks are not checkpointed to the disk at the next checkpoint and that's may be the point behind saying that they are just discarded rather than being kept in the cache.

                        Just my 2 cents.

                        Regards
                        Aman....