This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 2, 2013 6:41 AM by Aman.... RSS

Doubt regarding Buffer Writer

sandy121 Newbie
Currently Being Moderated
Friends, as per docs , DBWR writes as below :-

"The database writer (DBWn) process periodically writes cold, dirty buffers to disk. DBWn writes buffers in the following circumstances:

A server process cannot find clean buffers for reading new blocks into the database buffer cache.

As buffers are dirtied, the number of free buffers decreases. If the number drops below an internal threshold, and if clean buffers are required, then server processes signal DBWn to write.

The database uses the LRU to determine which dirty buffers to write. When dirty buffers reach the cold end of the LRU, the database moves them off the LRU to a write queue. DBWn writes buffers in the queue to disk, using multiblock writes if possible. This mechanism prevents the end of the LRU from becoming clogged with dirty buffers and allows clean buffers to be found for reuse.

The database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin.

Tablespaces are changed to read-only status or taken offline.
"

Can anyone explain the 4th scenario i.e "Thee database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin. " under which DBWR writes into data files ?

Thanks in advance.
  • 1. Re: Doubt regarding Buffer Writer
    sb92075 Guru
    Currently Being Moderated
    918868 wrote:

    Can anyone explain the 4th scenario i.e "Thee database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin. " under which DBWR writes into data files ?
    Imagine a session launches an UPDATE to a table with MANY, Many, many rows and every row gets changed.

    The amount of changed data exceeds total RAM by more than a factor of two.
    What happens to all the changed data prior to COMMIT being issued?
  • 2. Re: Doubt regarding Buffer Writer
    sandy121 Newbie
    Currently Being Moderated
    So you mean to say under that scenario, DBWR writes all those dirty blocks to datafiles even they are not committed, please correct me if I am wrong in my understanding.
  • 3. Re: Doubt regarding Buffer Writer
    sb92075 Guru
    Currently Being Moderated
    918868 wrote:
    So you mean to say under that scenario, DBWR writes all those dirty blocks to datafiles even they are not committed, please correct me if I am wrong in my understanding.
    yes, DBWR writes "dirty block" to datafiles.
  • 4. Re: Doubt regarding Buffer Writer
    rp0428 Guru
    Currently Being Moderated
    >
    So you mean to say under that scenario, DBWR writes all those dirty blocks to datafiles even they are not committed, please correct me if I am wrong in my understanding.
    >
    But nothing ever gets written to the datafiles until it has been written to REDO.

    There is extensive coverage of DBWR in the performance tuning guide
    http://docs.oracle.com/cd/E14072_01/server.112/e10821/instance_tune.htm#PFGRF024
    >
    10.3.8 free buffer waits
    This wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make free buffers by writing out dirty buffers. A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk.

    10.3.8.1 Causes
    DBWR may not be keeping up with writing dirty buffers in the following situations:

    •The I/O system is slow.

    •There are resources it is waiting for, such as latches.

    •The buffer cache is so small that DBWR spends most of its time cleaning out buffers for server processes.

    •The buffer cache is so big that one DBWR process is not enough to free enough buffers in the cache to satisfy requests.
  • 5. Re: Doubt regarding Buffer Writer
    Catfive Lander Explorer
    Currently Being Moderated
    Datafiles contain a mix of committed and uncommitted data all the time the database is running.
    The only time datafiles contain only committed data is when a database has just been shutdown cleanly (because a shutdown only takes place either when all users have been allowed to complete a transaction for themselves by commit or rollback; or after all users have been kicked off the database and had their transactions forcibly rolled back).

    So yes, DBWR writes blocks to disk when it needs to, regardless of whether transactions affecting them have been committed or not.

    It is the undo that is generated when blocks are dirtied that lets Oracle take a dirty block off disk and roll it back to a state where other users may view its clean data. And it's the redo generated when blocks are dirtied and the undo is generated that allows Oracle to take a dirty block off disk after an instance crash and roll it back to its earlier clean state.
  • 6. Re: Doubt regarding Buffer Writer
    sandy121 Newbie
    Currently Being Moderated
    Final clarification, sorry I should have asked in last post itself, before writing those dirty blocks to data files, checkpoint occurs and New SCN s generated and that SCN is updated in datafiles header and control files, hence the database should advance the checkpoint to incorporate the new checkpoimt information ie checkpoint time stamp and new SCN number generated ?

    Edited by: 918868 on Jan 31, 2013 9:04 PM
  • 7. Re: Doubt regarding Buffer Writer
    rp0428 Guru
    Currently Being Moderated
    Basically, yes - but again: not until the data is written to UNDO.
  • 8. Re: Doubt regarding Buffer Writer
    sandy121 Newbie
    Currently Being Moderated
    Doubts not clear yet
  • 9. Re: Doubt regarding Buffer Writer
    rp0428 Guru
    Currently Being Moderated
    >
    Doubts not clear yet
    >
    Question not clear yet
  • 10. Re: Doubt regarding Buffer Writer
    Aman.... Oracle ACE
    Currently Being Moderated
    918868 wrote:
    Final clarification, sorry I should have asked in last post itself, before writing those dirty blocks to data files, checkpoint occurs and New SCN s generated and that SCN is updated in datafiles header and control files, hence the database should advance the checkpoint to incorporate the new checkpoimt information ie checkpoint time stamp and new SCN number generated ?

    Edited by: 918868 on Jan 31, 2013 9:04 PM
    I am not sure that your understanding is correct about how oracle db works. The flow is,

    1) Checkpoint occurs
    2) Dirty buffers are written to the data files.
    3) Checkpoint marker is updated in the data file headers and also in the control file.
    4) There is no SCN generated or updated. SCN is updated in the blocks when the commit happens for the transaction under going in them or for some other reasons.
    5) There is no rule that Oracle has to advance the checkpoint because of the SCN generation.

    Now,what's your doubt again?

    Aman....
  • 11. Re: Doubt regarding Buffer Writer
    sandy121 Newbie
    Currently Being Moderated
    As written already about one of the scenario's when DBWR writes as per documentation
    "The database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin."

    What does this mean ?
  • 12. Re: Doubt regarding Buffer Writer
    sb92075 Guru
    Currently Being Moderated
    918868 wrote:
    As written already about one of the scenario's when DBWR writes as per documentation
    "The database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin."

    What does this mean ?
    what will you do differently after you understand sentence above?
  • 13. Re: Doubt regarding Buffer Writer
    sandy121 Newbie
    Currently Being Moderated
    Why does database advance the checkpoint? Aman told in last post that Oracle did not advance the checkpoint due to SCN generation/updates so it is due to other reasons? What is the relation to this with buffer writes with the advance of checkpoint?

    Hope now my doubt is clear to all.
  • 14. Re: Doubt regarding Buffer Writer
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    918868 wrote:
    Why does database advance the checkpoint? Aman told in last post that Oracle did not advance the checkpoint due to SCN generation/updates so it is due to other reasons? What is the relation to this with buffer writes with the advance of checkpoint?
    There are many different types of "checkpoints" (see http://jonathanlewis.wordpress.com/2007/04/12/log-file-switch/ ) The two best known are the log file switch checkpoint, and the incremental checkpoint.

    The log file switch checkpoint (which I think is named the Thread Checkpoin) is the one that results in Oracle writing dirty data buffers to the data files if their first redo change vector is recorded in a given log file; this checkpoint results in an update to every data file header AFTER the checkpoint is complete and the redo log file can be overwritten.

    The incremental checkpoint is fired every three seconds by DBWR posting itself, and tells dbwr to recalculate a new target SCN and redo block address (based on things like the MTTR, fast_start_target, log_checkpoint_interval or log_checkpoint_timeout) and write to file and data blocks that were first changed prior to that SCN; this SCN is recorded only in the control file.

    The first checkpoint type allows Oracle to pick the right log file to start recovering from, the second tells it where to start in the file.

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

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