Analyzing Tables and Indexes


For those DBAs using Oracle's cost-based optimizer, creating and maintaining accurate and timely statistics is both necessary and important. Fortunately, TOAD makes short work of dealing with statistics as it provides two very clean and simple ways to both see and collect them.

For simply examining any table's or index's statistics, you can use TOAD's Schema Browser to pick that object and view its Stats/Size tab as shown in Figure 6.17. Of course, with this approach, you can only see one object's statistics at a time and they're not laid out very aesthetically. The Schema Browser also provides context menu options with multiselect capabilities for invoking the Analyze Tables screen directly as shown in Figure 6.18. With this method, a few mouse clicks and you've analyzed all your tables or indexes. It's quick and easy.

Figure 6.17. TOAD Schema Browser ”Stats/Size tab.

Figure 6.18. Analyze via Schema Browser multiselect.

For more involved statistics maintenance from a more global view, TOAD offers the Analyze Tables screen shown in Figure 6.19 and located on the main menu at ToolsAnalyze All Tables. This screen presents all the relevant statistics information of concern in a nicely formatted grid, with separate tabs by major statistics type (that is, tables, table partitions, indexes, index partitions, and columns ). You merely filter the schema of concern and then multiselect the objects to analyze. Note that this screen also provides the ability to list chained rows and validate object structures. Of course, this requires that you have access to an appropriate CHAINED_ROWS table (such as the one created by Oracle's UTLCHN1.SQL script in the RDBMS admin directory).

Figure 6.19. TOAD Analyze Tables screen.



TOAD Handbook
TOAD Handbook (2nd Edition)
ISBN: 0321649109
EAN: 2147483647
Year: 2003
Pages: 171

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net