Space Analyst for Oracle - Tables Tab
Go Up to Using Space Analyst
The Tables tab displays diagnostic information regarding table space inefficiencies that may exist in your database.
The Table Reorganization Candidate Summary section uses a bar chart to count the various types of table space inefficiencies in your database. Note that the reorganization threshold options are used to compute the counts for each type of space inefficiency.
The table below describes the four distinct types of table space inefficiencies that Space Analyst computes for Oracle and Sybase ASE:
| Problem | Description |
|---|---|
|
Block Inefficiencies |
These tables have wasted space that exceeds the allowable percentage of wasted space set in the customizable thresholds of Space Analyst (to see all the thresholds, click the toolbar option labeled Reorganization Threshold Options). Tables with wasted space can take longer to scan and therefore contribute to slower response times. |
|
Next Extent Problems |
These tables cannot obtain their next extent of free space due to a lack of needed contiguous free space in the tablespace. Tables suffering from this problem may produce error conditions in the database if more data is added to them. This problem will be highlighted even if the tablespace that contains the table has its autoextend property for one or more datafiles set to true. |
|
Chain Problems |
These tables suffer from a chained or migrated row problem. The percentage of chained or migrated rows to overall rows contained in the table exceeds the allowable percentage of chained rows set in the customizable thresholds of Space Analyst (to see all the thresholds, click the toolbar option labeled Reorganization Threshold Options). Because chained/migrated rows cause more I/O to occur when they are accessed, overall response times can be degraded when these tables are referenced in SQL queries. |
|
Max Extent Problems |
These tables are nearing their maximum extent limit and have exceeded the allowable maximum extent percentage set in the customizable thresholds of Space Analyst (to see all the thresholds, click the toolbar option labeled Reorganization Threshold Options). Tables suffering from this problem may cause space-related errors to result if more data is added to them. |
The Table Reorganization Candidate Details section provides filters and a grid that you can use to view detailed space diagnostics for all or selected tables. The table below describes the grid data:
| Data | Description |
|---|---|
|
Table Owner |
The owner of the table. |
|
Table Name |
The name of the table. |
|
Segment Type |
Indicates if the object is a table, table partition, etc. |
|
Table (KB) |
The total allocated space of the table in KB. |
|
Number of Rows |
The number of rows that the table contains. NOTE: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package. |
|
Number of Blocks |
The total number of blocks allocated to the table. NOTE: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package. |
|
Empty Blocks |
The number of empty blocks that the table contains. NOTE: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package. |
|
Highwater Mark |
The last block that Oracle will scan up to when searching the table for data. NOTE: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package. |
|
Average Used Blocks |
The average number of blocks that the table is using. NOTE: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package. |
|
Block Inefficiency |
A percentage that indicates how much wasted space (space that the table is not using) that the table contains. High percentages indicate tables that may be candidates for reorganization. NOTE: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package. |
|
Chained Row Percent |
A percentage that indicates how many rows in the table suffer from chaining or migration. High percentages indicate tables that may be candidates for reorganization. NOTE: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package. |
|
Max Extent Percent |
A percentage indicating how close an object is to reaching its maximum extent limit. High percentages indicate tables that may be candidates for reorganization. |
|
Number of Extents |
The number of extents that the table consumes. |
|
Max Extents |
The maximum extent limit for the table. |
|
Extent Deficit |
Indicates if the object can allocate its next extent. If 'N', this is because the tablespace does not currently have enough contiguous free space. This problem will be highlighted even if the tablespace has its autoextend property for one or more datafiles set to true. |
|
Next Extent |
The next extent (in bytes) that the table will attempt to allocate when more space for the table is required. |
|
Max Free Space |
The largest contiguous block of free space that exists in the tablespace where the table resides. |
|
Tablespace Name |
The tablespace that contains the table. |
Available Functionality
- Note: Functionality differ by platform.
The following functionality is available in the Command menu, shortcut menu, and toolbar:
- Analysis Report
- Analyze
- DBCC
- Estimate Size
- Extract
- Map
- Tables Wizard (DB2 LUW)
- Open Wizard
- Place
- Quick Reorganize /Reorg
- Reorganize
- Threshold Options
- Update Statistics