Re: Multi block rows eating space

In article <19980115175100.MAA21321_at_ladder02.news.aol.com>, SloanAndy wrote:

With rows which are, on average, about 6K, I would create my database with a DB_BLOCK_SIZE of 8192. I would then size PCTFREE to ensure 1 row per block (around 20%). The rows could then update-in-place.

Unfortunately, you cannot change the database block size once it is created, so you would have to export, destroy, recreate and reimport the database to increase the block size.

Incidentally, if you are using UNIX and a volume manager it is most likely that your data is handled in 8K chunks.

Warning: Quadrupling your block size will also quadruple the amount of memory taken by your DB_BLOCK_BUFFERS. You may wish to reduce their number if you incur operating system problems such as paging/swapping.

regards

Neil Chandler

>I've got some tables with nasty multi block rows (each taking three 2k blocks),
>which are gobbling space at a frightening rate, despite the number of rows
>being static.
>
>There are only around 3000 rows, however, there is about 250 insert/deletes a
>day,
>and the tables grow by about half of these inserts. The database is 7.1.6
>(soon to
>be 7.3.3).
>
>Oracle support have been their usual wonderful self and not helped much.
>
>I don't think it's a problem with pctfree and pctused ( 10 and 40).
>
>The developers can't help much and I can't reproduce the problem in a test
>environment.
>
>I will soon have 100's of these tables, and the headache of garbage collecting
>them.
>
>Help please, any ideas? The 7.3.3 upgrade may help but I don't want to reply
>on it without evidence.
>
>cheers Andy
Received on Fri Jan 16 1998 - 00:00:00 CST