Re: Fragmenation Question

bennett_at_cc.gatech.edu (Paul Bennett) wrote in <3872705D.E02AFA0F_at_cc.gatech.edu>:

>This question is in response to the paper:
>"Avoiding a Database Reorganization"
>by Craig A. Shallahamer.
>
>Having a higher PCTFREE for a table will reduce the number of chained
>rows during updates; however, it will increase the initial amount of
>table block fragmentation. Has anyone studied the trade off between
>these two types of fragmentation? Which type causes the biggest
>performance issues? What types of applications would be hit hardest by
>choosing to reduce one type by increasing the other. Are there other
>papers about this floating around somewhere?

a larger pctfree (then the default of 10) doesn't mean more fragmentation on the initial load; it just means that the table will initially be larger or have more extents or have a higher high-watermark. fragmentation is bad because logically adjacent rows are physically far apart. that doesn't occur just because pctfree > 10. rest assured the db will still be clean.

if you set pctfree=50 (for example) the data will be more spread out and occupy more blocks. a full scan of the table visits more blocks and does twice the i/o, as the same data loaded with pctfree=0. --
dave mausner Received on Tue Jan 04 2000 - 23:55:00 CST