From:Steve Adams
Date:27-Feb-2001 11:54
Subject:   PCTFREE and INITRANS

An INITRANS setting of 20 robs you of 456 bytes per block. I suggest that you allow INITRANS (and MAXTRANS) to default, use say 19 FREELISTS and 2 FREELIST GROUPS, set PCTFREE to the ceiling of the length of 1 row expressed as a percentage of the block size less 90 bytes, and set PCTUSED to 100 - 2 * PCTFREE. Also, consider partitioning the table.

I believe I made INITRANS 20. So if I have more than 20 transactions in the same block concurrently I may have locking trouble if their is no space in the block header.

So what is a good PCTFREE for a very high transactional database with only inserts and deletes? The tables are in terms of Gigs, and I do have storage problems (not enough disk space).

Should I maybe lower INITRANS and increase PCTFREE, or if I do not have more than 20 transactions in a block concurrently, will I be OK?

And if I have not asked enough questions yet, Is the overhead for each INITRANS in every oracle block 24 bytes? If its only 24 bytes for each INITRANS, I may save space by making sure INITRANS is set high enough for all max transactions per block. Meaning if I am storing 50 rows on average per block, set INITRANS and MAXTRANS to maybe 25-50.

No, 0 is not a good idea. Assuming INITRANS is 1, if two transactions need to insert, update or delete different rows in the same block concurrently, then the second will need 24 bytes of free space to extend the interested transaction list in the block header. If that free space is not available, the second transaction will have to wait for the first to commit, even though it does not need row locks on any of the same rows.

If I have a table that will only insert, delete and update CHAR fields. Is a PCTFREE of 0 a good idea or a bad idea?