Q:Table Fragmentation. How do I reduce it?
>>Continuing the general discussion on table and index extents and do they
>>For those DBA's who believe that table extents do not matter then I suggest
>>you see the following two books and duplicate the test described in the
>>1) Oracle & Unix Performance Tuning by Ahmed Alomari, Prentice Hall, 1997.
>>Mr. Alomari has the title of Sr. Performance Engineer with Oracle Corp.
>>2) Advanced Oracle Tuning and Administration, Oracle Press. I do not have
>>the book handy to list the authors, but Loney is one of them, and this book
>>gives a clear repeatable example of how extents and their sizing can and
>>does affect performance.
>>On a small table it is difficult to notice the effect of multiple extents,
>>but as tables grow toward the gigabyte range the effect becomes much more
>>pronounced. Poor extent sizing can really hurt. None of these books
>Don't mix the # of extents and their sizing. The (excellent) reference
>you give proves this. The example you mention can be found on page 89.
>This were the conclusions :
>1. If the extents are properly sized, the number of extents has no
>impact on the number of reads required by table scans.
>2. If the extents are not properly sized, the number and size of the
>extents can greatly increase the amount of work performed by the
>database during a full table scan.
>So, once again : having several well-sized extents does NOT harm
>performance significantly. But the size does indeed matter a lot.
And of course, page 90: "...forcing each object in your database to have just
one extent will yield little in the way of performance improvements, and may
actually hurt your performance." Then mentions you can't stripe, can't
effectively use Parallel Query, and can't determine growth rate.
If the extent size is a multiple of the multiblock read, "...there is no
These opinions are my own and not necessarily those of Information Quest