Re: Formatted/Unformatted Oracle Block....

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 30 Nov 2008 07:50:24 -0800 (PST)
Message-ID: <4f195694-1c36-4cd2-9789-7e6f33a4337e@t11g2000yqg.googlegroups.com>


On Nov 30, 5:49 am, Aman Sharma <amansharma1..._at_gmail.com> wrote:
> Hi,
> When a block comes under the HWM,I believe its formatted by
> Oracle.What I want to know is that is there any bit in block dump
> which does differentiate that the block is unformatted or formatted?
> Secondly, is there a difference in the performance for oracle when it
> formats a totally clean block(block which is above the HWM) or it
> reinserts in the same block which is already formatted due to some
> earlier DML operation?
> Database version:-10.2.0.1/11.1.0.6
> Operating System: Win Xp proffessional
> Thanks
> Aman....

Hpuxrac provides excellent suggestions for determining the answers. I am guessing that the above question is related to this thread: http://forums.oracle.com/forums/thread.jspa?threadID=832220&tstart=0

I was curious, so I started performing Google searches related to block structure.

I found this article (scroll down to The Cache Header and Tail), which states that there is a 1 byte flag field with an unknown purpose : http://www.ixora.com.au/q+a/datablock.htm

A little more searching found this article: http://www.ixora.com.au/notes/cache_header.htm Where the flag byte is described as follows: A combination of 1-bit flag values.
"1 = virgin block

2 = last change to the block was for a cleanout operation
4 = checksum value is set
8 = temporary data"

I also found this article "BBED - Oracle Block Browser and EDitor - A hacker tool?":
http://www.petefinnigan.com/weblog/archives/00000999.htm Which lead to this article:
http://www.orafaq.com/papers/dissassembling_the_data_block.pdf

For the second question, consider what contention (as would be visible in the wait events) might occur when the high watermark is raised. It might make a difference if the /*+ APPEND */ hint is used to add the additional rows, whether or not there are multiple sessions performing the inserts, whether or not rows will extend into multiple blocks (HWM raised multiple times at 5 blocks each time for a single row insert), etc.

What happens when the /*+ APPEND */ hint is used: http://www.oaktable.net/getChallenge.jsp?id=77

Excellent article related to examination of HW Enqueue wait event: http://www.pythian.com/blogs/1008/resolving-hw-enqueue-contention

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Nov 30 2008 - 09:50:24 CST

Original text of this message