23
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 or all segments for a datafile.

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 or datafile. Within the tablespace map, it is possible to point to an individual extent and see the following detail information:

• ID of the block in the extent

• ID of the extent

• Full name and path of the datafile which is the physical location of the extent

Another key feature of the Tablespace Map is the Segment Analysis tool. A Segment Analysis can detect potential space management problems. Once a Segment Analysis has been performed, a Segment 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.

Note: You can print the current tablespace extent map by selecting File=>Print from the menu bar. When you print the extent map, a list of the segments that make up the tablespace will also be provided.

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 DBA Studio, Oracle Storage Manager and from the Oracle Enterprise Manager console. The Tablespace Map is launched in context to a selected tablespace.

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 colored flag indicating the segment problem status. The flags are set by running the Segment Analysis tool.
  • A gray flag indicates no analysis has been performed.

  • A red flag indicates an Alert status for the segment.

  • Yellow indicates a Warning status.

  • Green indicates no problem were detected for the segment.

  The analysis results for each segment can be seen by viewing the Segment Analysis tab.

• A symbol representing the segment type.

• The segment's schema and object name.

• The number of extents that have been allocated for the segment.

• The number of blocks that have been allocated for the segment.

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.

• Block(s) within a file that is used by the Oracle Server for internal bookkeeping are displayed in gray.

In addition to viewing the space usage, you can point to an individual extent in the graphical display to obtain additional detailed information on the extent including:

• Segment owner and object name associated with the extent

• Block ID

• Extent ID

• Full name and path of the datafile containing the extent

Figure 23-1 Tablespace Map

Analyzing Segments in a Tablespace

The Segment Analysis tool checks the tablespace for various space usage problems such as objects suffering from excessive row chaining and/or migration as well as overextended objects.

To run the Segment Analysis, use the Tools->Segment Analysis menu item or the Segment Analysis tool bar button. The Analysis is performed immediately. The results are displayed in the Analysis column of the Segment List, in the form of colored flags which represent the problem status of each segment. Segments within the tablespace that suffer from one or more space management problems will be identified by a colored flag:

• A gray flag indicates that the Segment 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 Segment Analysis.

The analysis results for each segment can then be viewed from the Segment Analysis Tab.

To view segment analysis results for an individual segment:

1. Select a red or yellow flagged segment from the Segment List. If all of the segments in the Segment List are flagged with green flags, no problems were detected.

2. Click on the Segment Analysis tab. The analysis results for the selected segment appear in a detailed report.

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

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

• Segments 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 contiguous 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

• Index stagnation. As index values are updated and deleted, the amount of unusable space in an index increases and additional index levels may be required to store the index blocks. Indexes containing 3 or more levels are flagged as potentially suffering from index stagnation.

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. Reviewing Segment Analysis Results

The Segment Analysis Tab presents the results of a Segment Analysis for each segment in the tablespace.

To view segment analysis results for an individual segment:

1. Run Segment Analysis from the Tools menu.

2. Select a red or yellow flagged segment from the Segment List. If all of the segments in the Segment List are flagged with green flags, no problems were detected.

3. Click on the Segment Analysis tab. The analysis results for the selected segment appear in a detailed report.

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.

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

---