Create Table Creating Dirty Buffers….

I always thought that the creation of table is a mere data dictionary update. I mean to say that the data dictionary , dba_tables, OBJ$ and other similar ones would get updated when we create a table. The logic was that its just a definition so where else it would be going? Its correct too that its just a definition but still for couple of things, Oracle does access DataBuffer Cache too when it creates a table and (here is what I didnt even think to see) not just accesses it but also makes couple of its block dirty too. Doesn’t make a sense,well ok let the numbers,bits, flags speak then. Have a look,

The buffers from the table T are marked Dirty and are accessed in the XCUR mode which means the blocks are accessed in the Exclusive Current mode and is representing that these blocks are going to be modified. The description of the buffer status which is shown from V$BH(or X$BH.status) from 11gr1 docs is given here,

Here is the link for the same in docs.http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_1059.htm#REFRN30029

A much better description of the same is mentioned in the 9iR2 docs,in the RAC guide. The link is mentioned below and also the description,

http://download.oracle.com/docs/cd/B10501_01/rac.920/a96597/pslkgdtl.htm#19780

Block Access Modes and Buffer States

An additional concurrency control concept is the buffer state which is the state of a buffer in the local cache of an instance. The buffer state of a block relates to the access mode of the block. For example, if a buffer state is exclusive current (XCUR), an instance owns the resource in exclusive mode.

To see a buffer’s state, query the STATUS column of the V$BH dynamic performance view. The V$BH view provides information about the block access mode and their buffer state names as follows:

• With a block access mode of NULL the buffer state name is CR–An instance can perform a consistent read of the block. That is, if the instance holds an older version of the data.
• With a block access mode of S the buffer state name is SCUR–An instance has shared access to the block and can only perform reads.
• With a block access mode of X the buffer state name is XCUR–An instance has exclusive access to the block and can modify it.
• With a block access mode of NULL the buffer state name is PI–An instance has made changes to the block but retains copies of it as past images to record its state before changes.

Only the SCUR and PI buffer states are Real Application Clusters-specific. There can be only one copy of any one block buffered in the XCUR state in the cluster database at any time. To perform modifications on a block, a process must assign an XCUR buffer state to the buffer containing the data block.

For example, if another instance requests read access to the most current version of the same block, then Oracle changes the access mode from exclusive to shared, sends a current read version of the block to the requesting instance, and keeps a PI buffer if the buffer contained a dirty block.

At this point, the first instance has the current block and the requesting instance also has the current block in shared mode. Therefore, the role of the resource becomes global. There can be multiple shared current (SCUR) versions of this block cached throughout the cluster database at any time.

So the blocks are meant for the modification and hence they are marked dirty after being modified. The same is confirmed from the query written by Jonathan Lewis,

So indeed the buffers are marked are as dirty only. But the buffers are supposed to be marked dirty when they are changed and with the create table,what’s going on which has made them change. Each buffer belongs to a particular classLets try to find out the class description of these buffers using their numbers.The block classes are described here*,

So according to the result that we have got, the blocks belong to the classes 4,8,9 and these numbers represent the classes(in the same order ),Header Block,1st level Bitmap Block,2nd Level Bitmap Block.

When oracle creates a table, it has to allocate a segment header block. This is done by picking up a block from a cache,formatting it,writing into it the new description of the data segment.As it is written so it is marked dirty and subsequently is written to the datafile.

The Automatic Segment Space Management allocates 3 levels of the blocks for example, Level1 , Level2 , Level3. The level 3 is the bitmap header block which resides in the segment header. This is linked to the Level 2 block. This block is used to search Level 1 which is always the first block in the extent. Level 1 marks the blocks for their respective status. So it appears , Oracle does the same what it does for the Segment header block to the Bitmap Blocks i.e. acquires two blocks from the cache , marks as Bitmap blocks, writes into them and marks them dirty. So this is the reason that we get the blocks marked as dirty even when we have not done anything by ourselves to change them. For me it was a new concept as I was not aware about it before. I would still try to dig upon more and will try to post more as and when I shall find it out. Heartiest thanks to Jonathan Lewis for helping me to understand it.

References,

http://www.juliandyke.com/Internals/BlockClasses.html

http://www.jlcomp.demon.co.uk/buf_flag.html