Re: Very Large Table extending after mass deletes

Tables with LONGs in them tend to be major pains. Even though your average row length is only 895 bytes (I assume you have 16K blocks by the way), is it may possible that a significant number of rows are much longer - say one in 12 is up to 4K ?

• Just seen your follow up post - my guess
• about the block size was wrong, but the
• general theory still applies.

I haven't checked the following notes recently, but it applied to earlier versions of Oracle and may still be true.

When you try insert a row into a 'pre-used' block on the free list, if it is too long then Oracle removes that block from the free list and tries the next block - for a total of 5; after 5 discards Oracle bumps the high water mark to get some clean blocks, and does the insert there.

Therefore - if you have the odd row of 4K, and the free space in your free block is less than 5.5K (the 4K of the row plus the 10% of a 16K block) the block is bumped off the free list. It wouldn't take many every long rows to cause lots of blocks to have a 25% wastage in them.

There are a few events you can set to watch the activity of the free lists (not guaranteed to work on all versions on all platforms).

10083 - trace the hwm has been bumped
10084 - trace when the free list becomes empty
10045 - may trace all free-list update operations.

Its a long time since I've played about with these, so I don't know what they do on this version of Oracle.

There are also some hidden parameters which may have an effect if the problem is as I described:

    _release_insert_threshold

If you reduce this, Oracle doesn't dump 5 blocks before calling for a new one, so you don' waste to many blocks with space for shorter rows.

    _bump_highwater_mark_count

if you reduce this, Oracle allocates fewer blocks above the hwm when it decides to call for a clean block - so you get to retry some dirty blocks sooner.

    _walk_insert_threshold

How far to step through the free list before giving up and calling for clean blocks. If you reduce this Oracle gives up searching sooner.

I think they all default to 5.

Experiment at your own risk - but your first check should be on the extremes in variation in length of the rows.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jane Schrock wrote in message <3898a01d_3_at_news3.prserv.net>...
>I'm on Oracle 7.3.4.5. I have very large table that continues to extend
>even though about two weeks ago half of the records were deleted from the
>table. The allocated size of the table is 12.5G. However, I believe there
>is only about 7G of data in the table. This is based on the row count,
>apprx 9 million, and the average row length from estimating statistics,
>around 850 bytes. This is a heavy insert table. It currently has only 1
>process freelist. The table has a LONG RAW column. I have done 5 random
>data block dumps and have seen a peak of 17 interested transactions. The
>extent growth has been as follows:
>
>12-13 - 247
>01-20 - 248
>01-24 - 251
>01-27 - 258
>02-02 - 259
>
>Note that the mass delete was done in the time period of 01-20 thru 01-27,
>when we see the most rapid extension. I believe this may have been due to
>deletes and inserts being concurrent. However, the deletes were done in
>multiple long running transactions. Once one is committed, the free blocks
>from that transaction should be available. Another change that occured in
>the 01-20 to 01-27 timeframe is that I revamped the rollback segments. I
>resized and added more to improve concurrency. The pctfree is 10 and the
>pctused is 90 for this table.
>
>Does anyone have any clues as to why the table must extend to find free
>space? Are there any stats I can look at to figure this out? In my
dreams,
>I want to reorg this table and create at least 20 freelists, but I'm going
>to have to get creative because I cannot get 48 hours of downtime. For
now,
>I just need to understand why this table is extending and not finding free
>space on the free list. When you delete half the rows of the table you
>gotta figure that a good portion of the blocks that were deleted from have
>enough space to fit some more rows. Any insight is greatly appreciated.
>
>Jane
>
>
Received on Wed Feb 02 2000 - 16:12:53 CST