Thanks for the question, Lindsay.
Asked: September 11, 2000 - 8:56 am UTC
Last updated: September 11, 2000 - 8:56 am UTC
Version: 8.1.6
Viewed 1000+ times
You Asked
I am trying to calculate the PCTFREE and PCTUSED for my tables. Do you have any good scripts or formulas to follow to compute these percentages? I realize these values are dependent on how much the rows will be updated etc.
I have some some formulas:
PCTFREE = ((Average Row Size - Initial Row Size) * 100) / Average Row Size
However, I am unsure as to how to calculate the Initial Row Size.
PCTUSED = 100 - PCTFREE - ((Average Row Size * 100) / Available Data Space)
Any pointers or a good rule of thumb is greatly appreciated.
and Tom said...
In order to calculate the initial row size, the only tried and true method is to fill a representative table with lots of rows that are of your "initial" size. If you use a sample of somewhere between 1 and 10% of your final tablesize and put in "initial" rows (rows as they will be inserted) -- you can analyze the table and determine the initial row size from user_tables.avg_row_len.
Then, you can update these rows to be what they will be after a couple of updates -- analyze -- and get the average final row size. In fact, at that point you can analyze list chained rows to see if you even need to concern yourself with this issue at all.
I personally don't change these from the defaults EXCEPT for tables that chain frequently (which can be found via the analyze table list chained rows). For any given table T, I don't worry about it -- it is just the exceptions...
Is this answer out of date? If it is, please let us know via a Comment