Re: Long Datatype Chaining

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

In article <803405937snz_at_jlcomp.demon.co.uk>

           Jonathan_at_jlcomp.demon.co.uk "Jonathan Lewis" writes:

: 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;'
:
: 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.

I've done a little more research into XCT lists, and some dumping of blocks with LONGs in them. The original problem is nothing to do with XCT lists and late promotion to the SEG list: Oracle simply does some extraordinary things with rows containing longs.

First, the correction to my previous XCT note: An XCT list is 'owned' but a transaction. Whilst the transaction is uncommitted, the XCT list does not get transferred to the free (SEG) list. When the transaction is committed, the XCT list becomes eligible for transfer to the Free list, but Oracle delays transferring it as long as possible (which is a sensible strategy) by using up the existing contents of the Free list before making a call to any XCT lists available.

Second: LONGs.
The experiments I have done so far revolve around 2K datablocks, and a table defined as (n1 number, l1 long); In all cases the long item is generated as a strings of 3800 bytes, so that it requires a little over two blocks just to hold the long itself when pctfree=10.

The results of these initial test show the following interesting feature: Insert first row into table: blocks 1,2,3.

	The head of the row goes into block _3_
	The long starts in block _1_
	The long continues to block _2_
	The long _ends_ in block _3_ 

Block _3_ (at this point holding about 90 bytes) goes OFF the free list !

The same sort of thing happens as rows are deleted and new rows are used: the use of the free list seems to be the standard usage, but there are blocks which get the head and tail of a row with a long, that go off the free list with virtually no data in them.

First tentative conclusions are: when a long is going to cause a chain, the long is made to start in a separate block.

To be investigated:

	What happens if there are several columns before the long
	What happens if the long makes the row chain, but is not so long
	that it has to wrap on to a third (or back to the first) block
	Does the starting block always go off the free list, or does this
	only happen if the long wraps back toit.
	Can new rows be made to start in a block into which a long has
	already wrapped.

-- 
Jonathan Lewis

Received on Sat Jun 24 1995 - 00:00:00 CEST