Pctfree & Pctused

[jomon_s]

Hi all


Can anyone tell me how do we calculate the values for PCTFREE & PCTUSED for an object.Is there any rule that the values of these two parameters should be 100.

also how do correlate these parametres


Regards

Joe

[rohitsn]

Hi,
The performance can be improved by properly using PCTUSED and PCTFREE storage parameters in the DDL statements like CREATE TABLE, CREATE INDEX, CREATE CLUSTER commands. These parameters are space related and have control over the data blocks. Theoretically, PCTUSED can be considered as low water mark and PCTFREE as the high water mark. If the space in a data block is such that there is less space left than PCTFREE, no new rows can be added in that block until the amount of space in the table is less than PCTUSED. The total of PCTUSED and PCTFREE cannot be over 100. (Default values for PCTFREE and PCTUSED are 10 and 40.)

Example: Consider having following values for a DDL statement storage parameters: PCTFREE = 30 PCTUSED = 50
Then, new rows can be added to the data block until the data block becomes 70% FULL (100 PERCENT - PCTFREE).When this occurs, no new rows can be added to this data block. The space is reserved for growth of existing rows.

PCTFREE · A high value for PCTFREE may improve performance because blocks have to be reorganized less frequently and chaining is also reduced. There is more space for growth of existing rows. · A low value for PCTFREE may reduce performance since reorganization becomes more often and chaining would be increased.

PCTUSED · A high value for PCTUSED may decrease performance because more migrated and chained rows are present. But this reduces space wastage by filling the data block more completely. · A low value for PCTUSED may increase performance because of less migrated and chained rows. But the space usage is not efficient due to unused space in data blocks.

Tips for PCTUSED and PCTFREE · If the application frequently performs UPDATES that alter sizes of rows greatly, then PCTFREE can be set high and PCTUSED can be set low. This would allow for large amount of space in data blocks for row size growth. · If there is more INSERT activity with less UPDATES, the PCTFREE can be set low with average value for PCTUSED to avoid chaining of rows. · If the main concern is performance and more space is available, then PCTFREE can be set very high and PCTUSED very low. · If the main concern in space and not performance, then PCTFREE can set very low and PCTUSED very high.

Regards,
Rohit Nirkhe,Oracle/Apps DBA,OCP 8i
oracle-support@indiatimes.com

[quester]

Yeah. PCTFREE + PCTUSED must be <= 100.

While PCTFREE indicates the amount of free space left for Future Updates of existing data, PCTUSED indicates the threshold of freespace only beyond which the block is put back into the Free List for future inserts.

By default, PCTFREE is 10 and PCTUSED is 40.

If your table/application has too many updates on existing data, then it obviously means that you might need to have a bigger chunk of PCTFREE. Likewise if your application has too many inserts, it means your PCTUSED value must be High as the higher the value of PCTUSED, faster it gets into the Free List for furute Inserts. Hope this is clear.

Let me know for any clarifications.

Quester