This discussion is archived
11 Replies Latest reply: Feb 4, 2013 9:58 PM by 951290 RSS

why to read undo data into bufffer cache during dml operation

951290 Newbie
Currently Being Moderated
hi gurus,
i am a beginner in oracle dba . I have read sql processing steps in oracle , in which i noticed one thing that i got confused ie, during dml operation oracle server process reads undo blocks along with data blocks into
database buffer cache. Whats the purpose of loading undo image into buffer cache?? as it is already in undo tablespace. Also please guide me what happens to undo blocks after commit.
any help appreciated .

Thanks in advance

Mahi
  • 1. Re: why to read undo data into bufffer cache during dml operation
    Jairam Explorer
    Currently Being Moderated
    Don't duplicate the thread., You already asked the same question in the below thread.,

    is undo blocks also got loaded into buffer cache
  • 2. Re: why to read undo data into bufffer cache during dml operation
    951290 Newbie
    Currently Being Moderated
    sorry,,,


    please clear me instead of giving any other links.. as i read a lot of links related to this .
    even i read your article but still i m confused..



    thanks
  • 3. Re: why to read undo data into bufffer cache during dml operation
    Jairam Explorer
    Currently Being Moderated
    Hi,

    After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible. (Based on undo retention parameter).,

    What is unclear for you in this statement?
  • 4. Re: why to read undo data into bufffer cache during dml operation
    951290 Newbie
    Currently Being Moderated
    thanks for d guidance,

    k let me ask some more,


    let i fired a dml like this


    update emp set name='MAHI' where empid='1234';

    here the server process will reads the blocks containing empid '1234' and put int into buffer cache if it is already not there. right?

    at the same time server process keeps a copy into UNDO tablespace also . right?

    so when the server process will load this undo image from undo tablespace to buffer cache?

    only when a request for consistent reads will happen?

    or at the same time server process will read the data blocks?


    thanks
  • 5. Re: why to read undo data into bufffer cache during dml operation
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    Why do we have buffer cache? So that frequently accessed data is stored in physical memory and oracle processes don't need to do physical read every time to get som edata foe read/write. Likewise, when you change a data usin a DML (suppose update), new value and old value both are stored in buffer cache (again the reason is same, we don't want to do physical read all the time).
    In summary, oracle stores data (table data and undo data) in memory so that physical read is not done. This data is written to the datafile when checkpoint occurs (table data in tables and undo data in undo tablespace).

    Salman
  • 6. Re: why to read undo data into bufffer cache during dml operation
    Jairam Explorer
    Currently Being Moderated
    Hi,

    Answers for your Questions,


    "here the server process will reads the blocks containing empid '1234' and put int into buffer cache if it is already not there. right?"

    Yes.,


    "at the same time server process keeps a copy into UNDO tablespace also . right?"

    No.,Keeps the data in memory and after commit if it will become undo segments then place the segment in undo tablespace based on the checkpoint.



    "so when the server process will load this undo image from undo tablespace to buffer cache?"

    When the undo data is not available in memory.,



    "only when a request for consistent reads will happen?"

    Yes


    "or at the same time server process will read the data blocks?"

    No
  • 7. Re: why to read undo data into bufffer cache during dml operation
    951290 Newbie
    Currently Being Moderated
    Hi thanks for the guidance

    So before commit any request for old image will leads the server process to read the blocks from datafile
    and to mark it as 'undo' and after getting commit based on the retention period the server process will writes the undo information into undo tablespace.
    and after undo retention period the same will be aged out.

    right ??

    thank
  • 8. Re: why to read undo data into bufffer cache during dml operation
    951290 Newbie
    Currently Being Moderated
    hi gurus

    please somebody clear my doubts,

    So before commit any request for old image will leads the server process to read the blocks from datafile
    and to mark it as 'undo' and after getting commit based on the retention period the server process will writes the undo information into undo tablespace.
    and after undo retention period the same will be aged out.

    right ??

    thanks in advance
  • 9. Re: why to read undo data into bufffer cache during dml operation
    rp0428 Guru
    Currently Being Moderated
    >
    So before commit any request for old image will leads the server process to read the blocks from datafile
    >
    No - Oracle will get the read-consistent image of a block that is needed for a particular query. That block might already be in the buffer cache, it might be in an undo segment or it might get it from the datafile.

    It will get it from whereever it finds it.
  • 10. Re: why to read undo data into bufffer cache during dml operation
    Aman.... Oracle ACE
    Currently Being Moderated
    948287 wrote:
    hi gurus

    please somebody clear my doubts,

    So before commit any request for old image will leads the server process to read the blocks from datafile
    Why do you believe that the request would always go to the datafile? The access would be first checked from the buffer cache only and if the required Undo Blocks are not found there than only the access would go to the Undo tablespace .
    and to mark it as 'undo' and after getting commit based on the retention period the server process will writes the undo information into undo tablespace.
    Nope, seems you are getting confused. The marking of the Undo block is done right at the time of the transaction. So saying at commit , it happens is wrong. And also saying that after the commit, the Undo information gets written to the Undo tablespace is also wrong. It's written by DBWR on it's own as it's a data file. At commit, LGWR writes not the DBWR.
    and after undo retention period the same will be aged out.
    right ??
    Do you mena to say that once the retention period is over, the undo data would be marked for being over-written? If yes than right !

    Aman....
  • 11. Re: why to read undo data into bufffer cache during dml operation
    951290 Newbie
    Currently Being Moderated
    Hi Aman thanks for the guidance,

    but your reply confused me more , see the earlier reply by Jairam for my question

    and my question was

    k let me ask some more,
    let i fired a dml like this
    update emp set name='MAHI' where empid='1234';

    here the server process will reads the blocks containing empid '1234' and put int into buffer cache if it is already not there. right?

    at the same time server process keeps a copy into UNDO tablespace also . right?

    so when the server process will load this undo image from undo tablespace to buffer cache?

    only when a request for consistent reads will happen?

    or at the same time server process will read the data blocks?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points