Session 3: Identifying Storage Structure Problems Using Space Analyst for Oracle

Go Up to Space Analyst for Oracle tutorial

Space Analyst can help you quickly find storage structure problems that threaten the availability of your database.

Pinpointing ‘out of space’ Problems Using Space Analyst for Oracle

To quickly tell if any of the tablespaces in your database are about to run out of free space, do the following:

1. Select the Database Overview tab.
2. In the Tablespace Space Overview section, position the pointer over the graph.
3. Right-click and select Toggle Display.
4. Examine the Percent Free columns of each tablespace along with the Autoextend column. If a tablespace is nearing the end of free space and cannot automatically grow, take action to prevent an out-of-space error.

Locating Fragmentation Issues Using Space Analyst for Oracle

There are a number of facilities within Space Analyst that helps you understand storage structure fragmentation.

To get summary information regarding tablespace fragmentation, do the following:

1. Select the Database Overview tab.
2. In the Tablespace Fragmentation section, position the pointer over the graph.
3. Right-click and select Toggle Display.

The tablespace fragmentation grid scores each tablespace in terms of fragmentation levels and also present data regarding the number of free chunks in a tablespace along with the largest free chunk size. Tablespaces with poor fragmentation ratings are highlighted in red. Note that you can sort the grid from worst to best tablespaces by clicking on the Fragmentation Index column header in the grid.

To get the same information for datafiles, do the following:

1. Select the Data Files tab.

   The datafile fragmentation grid scores each datafile in terms of fragmentation levels and also present data regarding the number of free chunks in the datafile along with the largest free chunk size. Note that temp files are not shown in this display. Datafiles with poor fragmentation ratings are highlighted in red.

To determine the type of fragmentation for a tablespace (bubble or honeycomb), do the following:

1. Select a tablespace or datafile that appears to have fragmentation issues.
2. Right-click and select Map.

   Space Analyst displays a graphical rendering of the tablespace on the Tablespace Map tab.

Bubble fragmentation can be confirmed by the presence of many free space fragments being found throughout the tablespace. Honeycomb fragmentation can be identified by seeing free space fragments adjacent to one another in the map.

Note: Large tablespace maps can be read easier if the “Full Screen” option is chosen. The standard view may be returned from full screen by clicking on the small full screen dialog box that appears at the upper left side of the display.

Note that object extent fragmentation problems can also be identified in Space Analyst’s tablespace map. To see if any table or index in a designated tablespace suffers from extent fragmentation, do the following:

1. Select the Tablespace Map tab.
2. Select a tablespace.
3. In the View By section, select the Extent Problems option.

   Space Analyst reconstructs the tablespace map and accompanying left-hand object grid to reveal any objects that have extent problems. All objects with extent problems are color-coded in red for easy identification. Objects colored in green indicate that they doe not suffer from any extent problems.

Using Analysis Reports in Space Analyst for Oracle

You can run HTML-formatted reports that pinpoint all major storage-related problems for a particular tablespace and the objects that are contained within it. To produce an Analysis Report, do the following:

1. On the toolbar, click Analysis Report.

   Space Analyst opens the Analysis Report dialog.

2. In Report File Name and Report Title you can change the location and report title of the report.
3. In Category, select the types of space problems you would like to investigate. The default option is All.
4. In Tablespace, select the tablespace you would like to analyze.
5. Click Execute.

   Space Analyst creates a frame-based Analysis report for the tablespace.

Proceed to Session 4: Pinpointing Object Performance Inefficiencies Using Space Analyst for Oracle