Fragmentation in Oracle 11gR2–Myths and Realities

Fragmentation can hamper space management operations in the database, but it is a long-enduring myth that overall the number of extents in a segment always impacts performance against the database. It is an equally long-enduring myth that the number of extents never impacts performance. Bitmap indexes with many noncontiguous extents spanning multiple datafiles can cause a big performance problem. Generally, locally managed tablespaces can minimize most extent-related issues.

The need for repeated reorganizations should be a thing of the past for most DBAs (but not all DBAs), if you have set up your storage properly. Fortunately, if you do still need to deal with the occasional reorganization, you now have several ways to perform this activity while minimizing downtime.

To avoid performance issues with extent management, you can do the following:

• Use locally managed uniform-extent tablespaces when you know how big a segment will grow or the rate at which it will grow.
• Use extent sizes that are multiples of the database block size.
• Move tables to tablespaces with an appropriate extent size when they grow too large.
• Avoid row chaining by using Automatic Segment Space Management (ASSM).

I recommend that you regularly monitor your database to find segments that are growing to extreme numbers of extents (over a thousand) and then manage those segments appropriately:

 

 

 

 

TIP

Query DBA_SEGMENTS on a regular basis to ensure that objects are not building up too many extents (when not using ASM). Catching problems early is the key to avoiding performance issues later. The goal is to place objects correctly in tablespaces with uniform extent sizes that are appropriate for the expected growth of the objects.