Re: Multi block rows eating space

From: sloanandy_at_aol.com (SloanAndy) >>
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. << and
>> I don't think it's a problem with pctfree and pctused ( 10 and 40). <<

Actually, the Oracle pctused default of 40 is not very efficient at reclaiming space unless your inserts are done in batches using sequential keys. With more randon deletes and inserts you may find 60 - 80% works much better at keeping the table. There is a formula in the DBA manual for figuring the optimal pctused/pctfree row size relationship that I have found often raises the pctused to above 80.

Also, where the row length is the wrong size in relation to the pctused and blocksize that only a few rows will fit in a block that Oracle will never reuse the block after reaching the pctfree limit unless all the rows in the block are deleted. Changes to pctused made after table creation will only be in effect for blocks access after the alter and not for already used blocks.

Personally, I would never build a database using a 2k block size. This may be the default, but it is based on the world as it existed a decade ago. By using a 4k block you reduce the fixed overhead by 50% making a little bit more space available for user data. This adds up on larger tables. I made the conversion from 2k to 4k on a VMS machine and the database hummed in comparision after the change. This was true even retaining the same number of total bytes in the buffer pool. To make the comparision fair we had re-org most of our tables prior to the conversion so the compression of the database that results from a total rebuild did not affect our results.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Sat Jan 17 1998 - 00:00:00 CST