Re: buffer cache and Rollback

On Jul 29, 9:10 am, "Richard Foote" <richard.fo..._at_bigpond.nospam.com> wrote:
> Comments embedded.
>
> <plb..._at_gmail.com> wrote in message
>
> news:1185712439.456536.54960_at_l70g2000hse.googlegroups.com...
>
>
>
>
>
> > On Jul 27, 8:32 pm, "Richard Foote" <richard.fo..._at_bigpond.nospam.com>
> > wrote:
> >> <plb..._at_gmail.com> wrote in message
>
> >>news:1185578894.204112.202140_at_q75g2000hsh.googlegroups.com...
>
> >> > Question:
>
> >> > During updating any table, it updates the block in the buffercache and
> >> > not the actual datafile. In a scenerio where the user executes the
> >> > update and don't commit, Does the select query of other users will be
> >> > retreived from the rollback segment(data file) or from the same
> >> > datablock which is cached in the buffer cache.How does it work. During
> >> > any updates does it copy the old image to the rollback
> >> > segment(datafile), if so any update it requires disk access... please
> >> > clarify...
>
> >> > Thanks
>
> >> Basically and simplistically, a query that reads data that has been
> >> updated
> >> but not committed by another session will read the "dirty" block,
> >> determine
> >> from the header and locking info that the row is in a dirty state, go to
> >> the
> >> specific RBS specified in the transaction slot of the updating
> >> transaction,
> >> find the corresponding undo (previous state of changed data), reconstruct
> >> a
> >> new read consistent block to the point of time prior to the update
> >> occurring, see if the block is now at a point of time prior to the select
> >> statement starting (if not repeat the process again and peel off another
> >> layer of the onion by reconstructing read consistent images until the
> >> block
> >> is at a state as at the start of the transaction) and retrieve the
> >> corresponding consistent data. This consistency check is always performed
> >> during a read by comparing the relevant header info in a block to the
> >> point
> >> of a query commencing.
>
> >> A rollback/undo block is just another block, if it's in the buffer cache
> >> great read it from there, if not then read it from disk. Note that the
> >> first
> >> rollback/undo block at least is likely to be in cache as it's from a
> >> current
> >> transaction but if it's a long transaction either in terms of the number
> >> of
> >> updated blocks or time it takes to issue the commit, then even this might
> >> not be the case.
>
> >> Hope it makes sense.
>
> >> Cheers
>
> >> Richard
>
> > Thanks Richard for your response.
> > If I understand correctly, the buffer cache works like below:
> > 1) User1 does a select query for row R1 which is in Block B1.
> > 2) Now B1 is in Buffer cache, any further select query for row R1 will
> > be served from Buffer cache.
> > 3) Now User1 executes update R1.... and not commiting, so In buffer
> > cache oracle will copy the old image of B1 to B1-old and will have a
> > pointer in B1 to point to B1-old (No I/O Activity).
>
> Note that B1-old doesn't exist as such. The changed data (and just the
> changed data plus some overhead details) are actually stored in the rollback
> segment associated with this transaction. The "pointer" from B1 which is
> part of the transaction information stored in the header of B1 points to
> this rollback block. It's B1 and the rollback data that together may be used
> to create a consistent image of B1 to a prior point of time for read
> consistency purposes.
>
> > 4) Now User2 executes select query for R1, which will be served from
> > B1-old(No I/O Activity).
>
> Is served by creating a new consistent image of B1 (called B1-old if you
> wish) which restores the changed data stored in the rollback block and makes
> B1-old look as it did prior to the update statement.
>
> > 5) If there are no space in the buffer cache than B1 will be moved to
> > Datafile and B1-old will be moved to Rollback segment.
>
> No. B1 will eventually be written to disk as it eventually ages. The
> rollback block will eventually be written to disk if it ages. B1-old will
> not be written to disk as it's only a consistent image and will simply be
> over-written once it ages.
>
> > Could you please correct me if any of the above statement is wrong..
>
> Did my best.
>
> Cheers
>
> Richard- Hide quoted text -
>
> - Show quoted text -

Thanks for the response and correction.
In your statement u have mentioned "The changed data (and just the
> changed data plus some overhead details) are actually stored in the rollback
> segment associated with this transaction."

I hope that the old image of the changed block are stored in the rollback segment .. correct me if I am wrong..

So Any update it require actual disk access for updating Rollback segment with old data.. right?..

Thanks Received on Sun Jul 29 2007 - 12:00:29 CDT