There’s a nice post on “How to determine the PCTFREE” over OTN by Anurag Tibrewal.Its worth reading.
http://forums.oracle.com/forums/message.jspa?messageID=4062829#4062829
I would be using below way to determine my pctfree based on average row length.
Lets take an example.I have a table employee. It has five column
– First_Name varchar2(40)
– Last_name varchar2(40)
– Middle_name varhcar2(30)
– Confirmation_date date
– Salary number(12)IF I would be using single byte encoding then all my character would be using 1byte.
Hence maximum bytes required to store above row would be 125 bytes. If during insert the average row length is 50 and there is frequent updatation and as a result the row may expand to an average length of 100.My block size is 8192. Oracle stores some data on header etc. Lets assume 192 bytes(for easy calculation) are required for this overhead. Hence I can store (8192-192)=8000 bytes of user data. So during insert, I can insert (8000/50) = 160 Rows per block.
But if Oracle do that then for every updation that expands a row above 50 (say to 100), it would be not able to find any free space in its current block and hence it would have to migrate that row to some new block which has 100bytes available. To stop this we would tell oracle to reserv some space during insert itself.
Since my average row length is 100 bytes, I would insert (8000/100) = 80 rows only per block during insertion. This would require 80*50=4000 bytes of space in a block during insert and so I would tell oracle to reserve (8000-4000) bytes for future updation. This means I would tell Oracle to reserve (4000/8000) = 50% of space during insert. Hence my pctfree is 50% and number of rows on an average would be 80.
But again this would not stop row migration. This would just decrease row migration. Why?
Lets say that Oracle inserted 80 rows with 50 bytes each in a block and hence occupied 4000 bytes. Now all these 80 rows were updated to 125 bytes (Since that is maximum bytes this row can store and still if you look at the average row length of entire table it is 100 bytes only). Then in that case First 53 rows would get space in its current block but 54th block would not be able to get space in this block and would have to migrate. This would free 50 bytes of space in its current block which can be utilized by other rows else they would also migrate. So to stop this migration one solution would be to keep reserved space for all these 125 bytes (maximum length instead of average length).In that case I would insert (8000/125) = 64 rows per block. Hence during insertion space required would be 64*50 (because 50 is average bytes required during insertion) = 3200. So my pctfree becomes ((8000-3200)/8000) = 60%.
Drawback I am wasting 10% space more just to prevent some percentage of row migration.
Does this prevent row migration?
What if I inserted some rows with 32 bytes each. This means each block can hold (3200/32) = 100 rows(With 60% pctfree). This could expand to 100*125=12500 bytes and hence again some row from this block would require migration.Point is you cannot stop migration with pctfree you can just minimize it.
Yes there are ways to restrict the number of rows per block (Here 64 rows per block) but that means you are just wasting your space. So there seems to be a trade off between space and row migration.Moreover you cannot do anything for row chaining. Because this arises when size of a single row is more than the size of a block.
Learn. Share. Repeat. 