Checking the Level of Tablespace Fragmentation

A routine task many DBAs perform is monitoring tablespace fragmentation. But ask DBAs what they mean by tablespace fragmentation, and you'll most likely get at least several different answers. The reason is twofold. First, there is both confusion and disagreement on what the general term means. And second, there are several kinds of Oracle database fragmentation often referred to when referencing the general term . A quick review of the Oracle fragmentation terminology follows :

• Tablespace Free space Fragmentation (TFF) occurs when a tablespace contains more than one extent of free space.

• Segment Fragmentation (SF) occurs when a segment is so large as to consume multiple extents.

• Data Block Fragmentation (DBF) occurs when rows are deleted from data blocks and leave holes of unused space.

• Index Leaf Block Fragmentation occurs when rows are deleted, thus creating partially or completely empty leaf blocks in the index B-tree.

• Row Fragmentation (RF) or chaining occurs when an update increases a row length such that it cannot fit in its current data block and must migrate to a new data block that has sufficient room for the entire row.

TOAD offers screens for examining tablespace free space and segment fragmentation, which will be covered next. TOAD doesn't offer anything for Data Block Fragmentation, and TOAD's offerings for both Index Leaf Block Fragmentation and Row Fragmentation are covered in the next chapter.

For viewing Tablespace Free space Fragmentation (TFF), you should use the TOAD Schema Browser's Tablespaces tab and look at the Fragmentation tab on the right-hand side of the window, shown in Figure 5.17. The Schema Browser is launched by either selecting its icon on the main toolbar (second icon from the top left by default) or from the main menu at Database, Schema Browser. Remember that the Schema Browser may actually be the default initial screen, as defined in the TOAD Options for Startup.

Figure 5.17. TOAD Schema Browser Tablespace tab for viewing TFF.

Note that the screen in Figure 5.17 breaks this information down into nice, digestible pieces. It shows that there are two free space extents over 100 megabytes and six of them from 1 and 10 megabytes. Of course, one of those free space extents that's over 100 megabytes is probably our free space extent above the high water mark (for example, never been utilized yet). Naturally this free space information is provided by data file, because new extent allocations cannot span across them.

For viewing Segment Fragmentation by tablespace, you should use the TOAD Tablespace Map screen, which is located on the main menu at DBA, Tablespace Map and shown in Figure 5.18. The basic formula used to calculate the map's coloring scheme is:

Figure 5.18. TOAD Tablespace Fragmentation Map.

• 100 - ROUND((SUM OF the BLOCKS FOR that SEGMENT - LARGEST BLOCK IN that SEGMENT) / SUM OF the BLOCK FOR that SEGMENT * 100))

Note that as you hover your mouse over cells in the map, it displays which segments consume data blocks represented by that cell. In Figure 5.18, the map shows that a table and its primary key share data blocks in that cell. But that cell might represent hundreds of actual data blocks if this were a large tablespace. Thus they may not overlap at all. Of course, having a table and its indexes in the same tablespace is a bad idea (although not with regard to fragmentation, but for spreading IO to avoid hot disks). So keep in mind that red cells really represent segments that consume a high percentage of blocks for their overall size. Thus they are candidates for object rebuilds (a topic covered in the next chapter).

Note that the Tablespace Fragmentation Map screen offers some very useful capabilities through its toolbar icons. You can coalesce the selected tablespace, display a graphical legend, display a pop-up window for showing the pointed-to cell's segments (rather than relying on the bubble help format shown in Figure 5.18), and display a pop-up window for filtering the displayed segments by data file, object type, owner, and object. So look at Figure 5.19 where these last three options have been turned on. Notice that some of the cells are now shown in yellow. These represent cells containing the data blocks for the filter selection of the index MOVIES_PROD.MOVIERENTAL_PK. Note too that the Filters window has a toolbar icon for Rebuild Table/Index, which allows rebuilding those objects that the connected schema owns.

Figure 5.19. TOAD Tablespace Fragmentation Map pop-up windows .

Finally, note that there are a few options within TOAD related to using the Tablespace Map screen, depicted in Figure 5.20. The TOAD Options screen can be opened either by using the Toolbox icon on the main toolbar or from the main menu at View, Options. Probably the most useful feature is the ability to define new threshold percentages and colors for highlighting on the tablespace map.

Figure 5.20. TOAD Tablespace Fragmentation Map options.