RE: 'avsp' and 'tosp' in datablock dumps

Hi Gopal,

Most of the free space in a data block is typically in a single area between the end of the transaction header and the start of the row data for the most recently inserted row. In a block dump 'fsbo' and 'fseo' are the offsets into the block where this area of free space begins and ends. When rows are inserted, they are placed at the end of this free space area, and 'fseo' is moved up. If the row directory or ITL has to be dynamically expanded, then 'fsbo' is moved down. The free space is kept in the middle in this way to allow both the (transaction and data) headers and row data area to grow without internal block free space management normally being required.

However, when rows are deleted or updated free space can also be created between rows in row data area. If necessary, Oracle can coalesce this fragmentary free space with the free space area by moving rows down to close up the gaps and correcting the offsets to those rows in the row directory at the top of the data area. There are several reasons why it is best for Oracle not to coalesce internal block free space immediately. In particular, it saves CPU time and reduces the retention of the exclusive buffer lock required for the change, thus improving concurrency. In a block dump 'tosp' is the total of all free space in the block, and 'avsp' is the readily available free space in the free space area that can be used without needing to coalesce the other free space fragments in the block, if any.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: K Gopalakrishnan [mailto:kaygopal_at_yahoo.com] Sent: Friday, 8 December 2000 22:02
To: Oracle DBA List (Lazy DBA)
Cc: Oracle List (Telelist)
Subject: Datablock Dumps again

In block dumps the free space available
in the block is visible in two places.

avsp=0x333

tosp=0x333

In most cases both are same, Received on Mon Dec 11 2000 - 02:01:08 CST