24
Introduction to the Tablespace Map

The Tablespace Map provides a complete picture of the characteristics of all tablespaces associated with a particular Oracle database. These characteristics include analysis status, segment type, segment name, number of extents, and the total size of the extent in blocks. You can display all segments for a tablespace.

The Tablespace Map also provides an allocation map which illustrates the organization of a tablespace's segments. This map displays an overview of the sequential allocation of space for segment extents within a selected tablespace. Within the tablespace map, it is possible to point to an individual extent and see the following detail information:

• Segment name
• ID of the block in the extent
• ID of the extent
• Size of the extent (measured in blocks)
• Full name and path of the datafile which is the physical location of the extent

Another key feature of the Tablespace Map is the Tablespace Analysis tool. A tablespace analysis can detect potential space management problems. Once a tablespace analysis has been performed, a Tablespace Analysis report is created and provides a detailed listing of any detected problems and marks the problematic segments with a warning or alert flag. See Analyzing Segments in a Tablespace.

Accessing the Tablespace Map

The Tablespace Map is part of the Oracle Tuning Pack. When the Tuning Pack is installed, you can access the Tablespace Map from the Oracle Enterprise Manager console. The Tablespace Map is always launched in context to a selected tablespace.

• From the Oracle Enterprise Manager console:
  1. Open the target database by double-clicking on the database object in the console navigator tree.
  2. Open the Tablespaces container and select a target tablespace.
  3. Right-click on the tablespace and select the Show Tablespace Map menu item.

     Note: With the Oracle Tablespace Map version 9i, you can now run the Tablespace Map from a browser or on UNIX platforms. See your Oracle Enterprise Manager configuration guide for more information on using the Enterprise Manager console and management packs from a browser or on UNIX platforms.

Viewing the Tablespace Map

The Tablespace Map provides a list of all of the segments for a selected tablespace and a graphical display of the extents that make up those segments.

The list of segments includes the following information:

• A Reorg? column that shows whether or not the segment should be included in a reorganization job
• A colored flag indicating the segment problem status. The flags are set by running the Tablespace Analysis tool.
  • Gray indicates no analysis has been performed.
  • Red indicates an Alert status for the segment.
  • Yellow indicates a Warning status for the segment.
  • Green indicates no problem were detected for the segment.
• A symbol representing the segment type.
• The segment's schema and object name.
• The number of blocks that have been allocated for the segment.
• The number of extents that have been allocated for the segment.
• Maximum number of extents that can be allocated to the segment
• Size (in kilobytes) of the next extent to be allocated to the segment. (Only shown for segments within a dictionary managed tablespace.)
• Increase percentage of the next allocated extent. (Only shown for segments within a dictionary managed tablespace.)

The graphical display shows how space is being used within the tablespace:

• Extents allocated for all segments not selected in the segment list are displayed in cyan (light blue).
• Extents allocated for the segment selected in the segment list are displayed in yellow.
• Extents in the tablespace that have not been assigned to any segment are displayed in white. These extents are available for allocation the next time a segment needs to be extended.
• Blocks within a file that are used by the Oracle Server for internal bookkeeping are displayed in gray.
• Current size of the selected tablespace in megabytes
• Total amount of used space in megabytes
• Percentage of space currently allocated to the tablespace that is being used
• AutoExtensible (Yes or No) indicates whether or not the tablespace can be automatically extended

  
  

  Note: Choose View\Show Tabular Map from the menu bar to display the tablespace metrics in an easy-to-read table.

Figure 24-1 Tablespace Map

Text description of the illustration table_ma.gif

Analyzing Segments in a Tablespace

The Tablespace Analysis tool checks the tablespace for various space usage problems such as objects suffering from excessive row chaining and/or migration and overextended objects. The tool also generates a full report that describes all of the space usage problems in the current tablespace. The report can be printed and used in conjunction with the Reorg Wizard to correct the problems.

Generating a Tablespace Analysis Report

To run the Tablespace Analysis, use the Tools=>Tablespace Analysis menu item or the Tablespace Analysis tool bar button. The Analysis is performed immediately. The results are displayed in the form of colored flags in the Analysis column of the Segment List. The colored flags represent the problem status of each segment. The status conditions are defined below.

• A gray flag indicates that the Tablespace Analysis has not been performed.
• A red flag indicates an alert status for one or more problems.
• A yellow flag indicates a warning status for one or more problems.
• A green flag indicates that the segment is free from the problems detectable by the Tablespace Analysis.

The analysis results can then be viewed from the Tablespace Analysis Report tab.

Viewing Tablespace Analysis Results

The analysis results can be viewed from the Tablespace Analysis Report tab.

Space management problems associated with the Tablespace Analysis warning and alert status flags are as follows.

An Alert status (Red Flag) may include one or more of the following problems:

• Segments residing in dictionary managed tablespaces that have more than 1024 extents. Even though the performance of database access and transaction operations on a segment is generally unaffected by the number of extents, a maximum of 1024 extents is recommended to ensure efficient handling of certain DDL operations such as dropping or truncating a table.
• Segments that are within 1 extent of hitting their current MAXEXTENTS setting.
• Segments that will be unable to extend due to insufficient free space in their current tablespace.
• Tables with excessive (greater than 10%) row chaining and/or row migration. Row chaining occurs when a row is too large to fit into a single data block, and must be stored in two or more chained data blocks. Row migration occurs when a row that originally fit into a single data block is updated and the resulting row size exceeds the block's free space, causing the row to migrate entirely to another block. In either case, I/O performance may decrease because the Oracle Server must read multiple blocks to return the single row
• Indexes that are inefficient. Indexes can become inefficient for two reasons: 1) as index values are updated and deleted, the amount of unusable space in an index increase; 2) there are times when the height of the index is greater than the optimal height. In both situations scans of the index are less efficient. Indexes suffering from inefficient tree use and indexes that will reduce one level of the tree when rebuilt are flagged

A Warning status (Yellow Flag) may include one or more of the following problems:

• Segments that are within 2 extents of hitting their current MAXEXTENTS setting.
• Tables with excessive (greater than 5%) row chaining and/or row migration.
• User objects existing in the SYSTEM tablespace. User data should not be stored in the SYSTEM tablespace as this increases the potential for space management problems in the tablespace.
• Segments with nonzero PCTINCREASE setting. The PCTINCREASE setting should be zero as a nonzero setting can lead to tablespace free space fragmentation problems.

  Note: You can specify alert and warning option settings for the current session by selecting Tools=>Tablespace Analysis Options.

Launching Reorg Wizard to Correct Space Problems

You may decide that you want to correct the space usage problems within the tablespace. To correct these problems, select Reorg Wizard from the Tools menu or the Reorg Wizard toolbar button. The Reorg Wizard allows you to reorganize the space used by problematic schema objects or the entire tablespace. You can also use the Reorg Wizard to repair migrated rows.

There are two options for launching the Reorg Wizard from Tablespace Map:

• Tools=>Reorganize Tablespace

  Launches the Reorg Wizard to reorganize the entire tablespace

• Tools=>Reorganize Selected Segments

  Launches the Reorg Wizard in context to the segments that have been identified with blue check marks in the Reorg? column of the Segment List. By default, segments identified by the Tablespace Analysis as having space usage problems are selected for reorganization.

  Note: By default, segments identified by the Tablespace Analysis tool as having space usage problems are automatically selected for reorganization. You can select or deselect a segment for reorganization by clicking on the red cross or blue check mark, respectively. This is useful when there are segments selected for a reorganization that you do not wish to include in the reorganization job.

  
  
  

For more information on using the Reorg Wizard, see Chapter 23, "Introduction to the Reorg Wizard".