Re: Q:Table Fragmentation. How do I reduce it?

Continuing the general discussion on table and index extents and do they matter:
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 second reference:

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 mentioned explain the use of the extent list contained in the table header block and sequential scans. I would like to know if it has to be accessed once for each and every extent or just once for the entire list. This would require latching the block. A small price, but since this block would be in the buffer pool I imagine it would affect the LRU. Ver 7.3+ allows multiple LRU latches but prior versions have only one and our system is constrained during peak periods. Received on Tue Mar 24 1998 - 00:00:00 CST