Re: Long Datatype Chaining

From: Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk>
Date: 1995/06/17
Message-ID: <803405937snz_at_jlcomp.demon.co.uk>#1/1


In article <1995Jun15.182713.6653_at_dhnews.dehavilland.ca>

           t89003_at_isdserv.dehavilland.ca "Rennick Sandra" writes:

:
: We have a table that contains a column declared as long. We know that the
: data in the long column is causing extensive chaining to occur in the table.
: This table is being used by an application which only performs inserts and
: deletes. Because no updates are occuring, the PCTFREE on the table is
: defined as 0. Every night, a process runs that removes a large amount of the
: data out of the table. However, we are finding that the free blocks are not
: being used very efficiently, and the table allocates new extents frequently.
:
: Does anyone know if chained blocks remain chained together even when there
: is no data in them? Can anyone explain what is happening in this table?
:

No, chaining is a row-based mechanism, not a block-based mechanism.

I suspect that what you are seeing is the effect of 'delayed block cleanout'. The simple solution may be for your delete process to be followed by a simple 'select count(*) from table;'

Explanation:



Your delete process presumably empties a number of blocks that contain nothing by parts of a single chained row. These blocks are put on the 'XCT' list, which is a stage before the free-list. When blocks are on the XCT list, they are in a state where they are known to be usable, but may need a rebuild. They will be transferred to the real free-list when another process visits them at a later stage: I think the test condition at that point is that no transaction that started prior to the transaction that put them on the XCT list is still running.

In your case no other processes will visit the blocks because they are completely empty, so they stay on the XCT list and never move to the free-list.

NB This is all hypothesis, based on examining header block dumps, so I could be completely wrong, but the select count(*) might still work.

-- 
Jonathan Lewis
Received on Sat Jun 17 1995 - 00:00:00 CEST

Original text of this message