This discussion is archived
3 Replies Latest reply: Jan 2, 2013 8:59 AM by Aman.... RSS

Need clarification on when the oracle will write the data tod disk blocks

913867 Newbie
Currently Being Moderated
Hi,

I have confused on oracle redo log files. As i am learning the redo buffers are flushed for every 3 sec or when it is one - third full or whenever a commit command placed. As of i know whenever a commit performed the LGWR writes the buffers to the redo log file and DBWR writes the data from buffers to the Disk blocks.

When the redo buffers are flused for every 3 sec, is it means the DBWR writes the data to diskblocks?

For example, My system allocates a space for redo log buffer as 2 MB, and i want to perform an update on all rows of a table which is actually size 20 GB, and i would like to perform the commit after my transaction on all rows completed. In this case how the Oracle behaves. How the DBwr writes the data from data buffers to disk. As the point mentioned for every 3 sec it will write the data to disk blocks for every redo buffer flush in 3 sec or when one third of redo buffers will full.

Kindly clarify on this with examples.

Thanks a lot.

Regards,
Mani.
  • 1. Re: Need clarification on when the oracle will write the data tod disk blocks
    P.Forstmann Guru
    Currently Being Moderated
    Please read following discussion where this question has already been asked and answered:
    https://forums.oracle.com/forums/thread.jspa?messageID=7427559.

    Detailed answer is in message that contains "Suppose you do an update of a very large table, and it floods the buffer cache with lots of dirty buffers."

    Edited by: P. Forstmann on 2 janv. 2013 08:57

    Edited by: P. Forstmann on 2 janv. 2013 09:00
  • 2. Re: Need clarification on when the oracle will write the data tod disk blocks
    rp0428 Guru
    Currently Being Moderated
    >
    As of i know whenever a commit performed the LGWR writes the buffers to the redo log file and DBWR writes the data from buffers to the Disk blocks.
    >
    No - you can't know that since it isn't true. DBWR doesn't have to write data to disk just because a commit occurs. It is LGWR that has to write data to disk BEFORE it signals that the commit has been completed. Note - this action can be modified by use of the COMMIT_WAIT parameter; see the Database doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams032.htm
    >
    COMMIT_WRITE is an advanced parameter used to control how redo for transaction commits is written to the redo logs. The IMMEDIATE and BATCH options control how redo is batched by Log Writer. The WAIT and NOWAIT options control when the redo for a commit is flushed to the redo logs.
    >
    As for this question
    >
    When the redo buffers are flused for every 3 sec, is it means the DBWR writes the data to diskblocks?
    >
    No - LGWR does NOT signal DBWR to write data to disk. DBWR, on the other hand, may signal LGWR to write data to disk if it needs to free up buffers in the buffer cache.

    Uncommitted blocks can exist in the buffer cache even after LGWR has signaled that a commit has occured and uncommitted blocks can also exist in the datafiles as long as DBWR has first written them to the log files.
  • 3. Re: Need clarification on when the oracle will write the data tod disk blocks
    Aman.... Oracle ACE
    Currently Being Moderated
    910864 wrote:
    Hi,

    I have confused on oracle redo log files. As i am learning the redo buffers are flushed for every 3 sec or when it is one - third full or whenever a commit command placed. As of i know whenever a commit performed the LGWR writes the buffers to the redo log file and DBWR writes the data from buffers to the Disk blocks.
    And how do you know this and who said that it's correct? It's a wrong understanding completely. There is no write done to the data files by the DBWR when there is a commit issued for the simple reason that as long as the data is protected in the redo logs (and from there to the archive logs) , any gaps between the data file and redo log file can be bridged-if needed. And there is a lot of difference in the writing units of both the processes as well. DBWR writes in the terms of oracle data blocks whereas LGWR writes in the terms of o/s blocks which is far smaller than the logical oracle block.
    >
    When the redo buffers are flused for every 3 sec, is it means the DBWR writes the data to diskblocks?
    The answers of Pierre, Rp and mine are the same , no .
    >
    For example, My system allocates a space for redo log buffer as 2 MB, and i want to perform an update on all rows of a table which is actually size 20 GB, and i would like to perform the commit after my transaction on all rows completed. In this case how the Oracle behaves. How the DBwr writes the data from data buffers to disk. As the point mentioned for every 3 sec it will write the data to disk blocks for every redo buffer flush in 3 sec or when one third of redo buffers will full.

    Kindly clarify on this with examples.
    Forget that example at the moment and don't get deep into the things. It's important that you understand and remember the basic definitions of these processes and the memory areas first and for that Concepts guide is an excellent place to start from.

    Aman....
    >
    Regards,
    Mani.

Legend

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