Why the dbwn would write uncommited data to the datafile ? 4

[pissallio]

In "ORACLE 9i DBA Fundermentals-1",page 1-33,the following description about checkpoint can be found :
"An event called a checkpoint occurs when the Oracle background process DBWn writes all the modified database buffers in the SGA,including both committed and uncommitted data,to the data files."

Why would DBWn write uncommitted data to data files ?

I think that only committed data should be written from data buffers to the data files,because executing "commit" by a user means that his confirmation to the changes to the data,which would make the data permanent in the datafiles and can ensure that every other users see the identical copy of the data.

 

[SantaMufasa]

Pissallio,

Why would DBWn write uncommitted data to data files ?

I think that only committed data should be written from data buffers to the data files,because executing "commit" by a user means that his confirmation to the changes to the data,which would make the data permanent in the datafiles and can ensure that every other users see the identical copy of the data.

The way you describe is one way to handle COMMIT (and that likely is the way that one or more other database management systems handle COMMIT), but that is not the way Oracle handles COMMIT. Oracle uses other mechanisms to deal with COMMIT to ensure that everyone sees the "proper", consistent, COMMITted data.

To understand how Oracle handles all this, let's review some other architectural priciples that you may have encountered in the DBA Fundamentals manual:

The first important concept to understand about Oracle is its architecture. The two main components of a "running" Oracle are:
1) The Instance and
2) The Database.

The main components of the Database are
1) The Control Files,
2) The Database Data Files and
3) The On-line Redo Log Files.

The Control Files: a textual map of the components and characteristics of the database. Amongst the information that the control file contain are:
a) the name of the database,
b) whether the database is in "ARCHIVELOG" mode
c) maximum numbers of log files, log members, data files, instances that can be managing the database simultantiously, and maximum amount of log history
d) log file groups and log file names
e) database data file names
f) database characters set

The Database Data Files: These are the disk files that provide storage space for your database's objects. A Database Data File supports one tablespace; a tablespace has one or more data files. These database data files logically contain extents. An extent simply is a collection of contiguous database blocks. The size of your database blocks is a multiple of your operating system's block size. Oracle labels the extents as either "free" or "used". If a block is "used", it means that it contains data for a database object known as a segment. Typical examples of segments are tables and indexes. If Oracle has not yet allocated an extent for use by a segment, then the extent (the physically contiguous collection of blocks) is called a "free extent". It is from this collection of "free extents" that Oracle allocates more space for tables and indexes, as needed.

The On-line Redo Log Files: On-line redo log files are the key to Oracle's success as a database. They are simply a sequential "diary" of all the events that occur to the database. It is the "stream of consciousness" that, if you were to "replay" it, from start to finish, could logically and physically rebuild your entire database, current up to the most recently committed (and even uncommitted) transaction.

So, Control Files, Database Data Files and On-line Redo Log Files make up an Oracle databas.

====================================================
Now, for an explanation of The Instance. The Oracle Instance is made up of:
1) The System Global Area (SGA) and
2) The Background Processes

The main components of The System Global Area (SGA) are:
1) The Shared Pool,
2) The Database Buffer Cache and
3) The Log Buffer.

The Shared Pool: A main purpose of the Shared Pool is that it is the area of memory that stores SQL statements for execution and re-use. It stores a text version and an interpreted, executable version of SQL statements.

The Database Buffer Cache: This major memory component is the location where Oracle
a) reads data blocks into memory from the Database Data Files,
b) performs updates, deletes, and inserts of rows of data that appear in (or logically disappear from) the data blocks, and
c) displays data from here to the screen and processes of database users everywhere.
The Database Writer (DBWR) process writes information from this memory structure out to the Database Data Files on an as-needed basis. (But this is a very important note: a COMMIT statement is not one of the triggering events that causes DBWR to write to disk. We’ll talk more about this, below.)

The Log Buffer: This vital memory component is the “scratch pad” where Oracle jots down all the changes that occur to the database, including whether or not transaction changes are worthy of saving (COMMITted) or not worthy of saving (ROLLedBACK). The Log Writer (LGWR) process writes information from this memory structure out to the On-line Redo Log Files on both a regular and irregular/as-needed basis. One of the events that triggers the LGWR to write to disk is a COMMIT statement.

The Background Processes: These processes are similar to the “kitchen help” at a restaurant. They are the processes that do much of the work to “make your dining experience a pleasure.” [But it is your own Dedicated Server (your personal "waiter" or "waitress"), that actually delivers to you what you request and takes care of your personal needs.] The background processes include these software components:
a) The System Monitor (SMON),
b) The Process Monitor (PMON),
c) The Database Writer (DBWR),
d) The Log Writer (LGWR),
e) The Checkpoint process (CKPT)
f) The Recovery process (RECO)

So, The SGA and Background Processes make up an Oracle Instance.
====================================================

It took all the above background to get us to the point where we can now clearly understand what happens as a result of a COMMIT statement.

When a User performs an INSERT, UPDATE or DELETE statement, s/he is instructing her/his Dedicated Server Process (SP) to make physical changes to Database Blocks that reside in the Database Buffer Cache in the SGA. (For an UPDATE or DELETE, the SP must ensure that the affected data block(s) reside in the Database Buffer Cache. If the block(s) are not there, then the SP must copy the necessary block(s) from the Database Data File(s) on disk into the Database Buffer Cache in the SGA.)

Before an SP actually makes any changes, however, it must write a copy of the “pre-changed” data out to a Rollback Segment, in case the User changes her/his mind and wants to abandon the changes. The Rollback Segment also provides a location for other SPs that are serving other Users to obtain an “unchanged”, consistent copy of the database data until such time that the changing User decides to COMMIT (or ROLLBACK) the changes that s/he has made.

When an SP makes changes for a User, it writes the changes to both the data block that resides in memory in the Database Buffer Cache and also to the Log Buffer. [Remember that the Log Buffer is a “diary” of all changes to the database, whether we “keep” the changes (via COMMIT) or “discard” the changes (via ROLLBACK or via abnormal end (abend) to a User session or something else unexpected such as a power failure that brings the entire instance down.) ]

It is very important to point out that an SP might need to go to different locations to obtain “true” data (“true” meaning currently COMMITted, consistent data). The SP might need to access “true” data from either:
1) Table/Index blocks in a Database Data File (on disk)
2) Table/Index blocks that have already been copied into the Database Buffer Cache in memory in the SGA,
3) Rollback-Segment blocks still residing in the Database Buffer Cache in memory in the SGA,
4) Rollback-Segment blocks in a Database Data File (on disk).

This means that “true” database data (meaning COMMITted data) might reside in one of four different locations, two of which are on disk and two of which are in memory.

At this point, you are probably saying, “I know for a fact that when ‘COMMIT happens’, something must be written to disk if for no other reason than to protect against a sudden power failure that erases everything in memory, including COMMITted images.” You would be correct…something absolutely, positively is written to disk as a result of a COMMIT: it is the information (including the COMMIT verification) that resides in the Log Buffer. And the response, "Commit complete", does not return to your screen until Oracle can confirm that it has successfully written the contents of the Log Buffer to the current On-line Redo Log File.

So, it is very important to understand that as a result of this architecture, that:
1) COMMITted data might not be written to the Database Data Files for an extended period following the COMMIT
2) Un-COMMITted data might be written to the Database Data Files long before it is ever COMMITted.

The above apparent anomaly disappears when one realises that Oracle always writes COMMITted data to the On-line Redo Log File. It is the contents of the On-line Redo Log Files that Oracle uses in case there is an abnormal termination to an Oracle Instance or session.

I hope this all seems reasonable, because this is how Oracle works.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 15:26 (31Oct04) UTC (aka "GMT" and "Zulu"),
@ 08:26 (31Oct04) Mountain Time

 

[pissallio]

Hi,santa
It really helps. Thanks for your continuous help

 

[KenCunningham]

That, Santa, is a masterpiece.

 

[SantaMufasa]

Ken and Pissallio,

You are both kind and thoughtful. I'm glad you found it helpful/worthy. Thanks!

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 08:45 (01Nov04) UTC (aka "GMT" and "Zulu"),
@ 01:45 (01Nov04) Mountain Time

 

[dbomrrsm]

Santa

Very neat explanation - I am also new to Oracle and that puts things into perspective for me wondefully.

Nearly as good as

SET GWIM ON

Well done have a star.

[blue]DBomrrsm[/blue]