Reviewing Schema Object Extent Allocations

Reviewing Schema Object Extent Allocations

A routine task that some DBAs perform is monitoring schema object extent allocations. However, today this task has become somewhat less important than it has been historically. With the great reduction in disk costs during this past decade , database sizes have increased greatly. Today, a 100GB database is not uncommon or considered especially large. And today's data warehouses are often tens to hundreds of terabytes in size. Add to that locally managed tablespaces with the option for uniform extent sizes, and extent counts become much less of a hot issue. So tables and indexes with hundreds, thousands, or even tens of thousands of extents may well be a non-issue for some databases.

TOAD provides two ways to review object extent allocations. The quickest and easiest way to monitor extents is by using the Schema Browser's Tablespaces tab and using the Extents tab on the right-hand side, shown in Figure 5.15. 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.15. TOAD Schema Browser Tablespace tab for viewing extents.

However, this interface does not provide any capabilities other than column sorting. Plus, the query to populate the data grid in this tab can take a very long time to run. So what if you wanted to look across all tablespaces and filter the results based upon the object type or owner? You cannot do so by using this interface. You would need to use TOAD's View Extents screen shown in Figure 5.16, which is located on the main menu at DBA, Extents. Also note that this screen offers a Print Extents Report button for a hardcopy report of the results. With all these additional capabilities, the View Extents screen is the recommended method for reviewing object extent allocations.

Figure 5.16. TOAD View Extents screen.