Space Analyst for Oracle, Sybase ASE, and IBM DB2 for Linux, Unix, and Windows - 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. |
IBM DB2 for Linux, Unix, and Windows
The table below describes the four distinct types of Reorganization Candidate Summary that Space Analyst computes for IBM DB2 for Linux, Unix, and Windows:
Problem | Description |
---|---|
Overflow Problems |
Displays the number of tables that fall outside the preset limits for the thresholds of this area. Can be addressed with reorganization of the affected table(s). |
Space Utilization Problem |
Displays the number of tables that fall outside the preset limits for the thresholds of this area. Can be addressed with reorganization of the affected table(s). |
Page Utilization Problem |
Displays the number of tables that fall outside the preset limits for the thresholds of this area. Can be addressed with reorganization of the affected table(s). |
Index Contention |
Displays the number of tables where the index space is shared by the Tablespace. To resolve, use the Space Management Wizardto change the Index Tablespace in table definition. |
The table below describes the options and functionality on the Table Reorganization Candidate Details section:
Option | Description |
---|---|
Tablespace |
Displays all Tablespaces (regular) in the database and the value [All]. |
Table Schema |
Displays all schema in the database and the value [All]. |
Display |
All Tables Tables with Overflow Problems - Displays a list of all tables where overflow threshold is exceeded. Tables with Space Utilization problems - Displays a list of all tables where space utilization threshold is exceeded. Tables with Page Utilization problems - Displays a list of all tables where the page utilization threshold is exceeded. Tables with Index Contention - Displays a list of all tables where the table and indexes on the table share the same Tablespace. |
Refresh |
Refreshes the table grid with criteria selected in the above lists. |
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 Schema |
The schema name for the table. |
Table Name |
The name of the table. |
Cardinality |
The number of rows in the table. |
Size |
The size of Table in bytes. |
Overflow |
The number of overflow rows. Overflow rows can be created when rows are updated and the new rows contain more bytes than the old ones (VARCHAR fields), or when columns are added to existing tables. |
Pages with Data |
The number of allocated pages that contain data. |
Total Pages |
The total number of allocated pages. |
Active Blocks |
The number of Active Blocks in Tables with Multidimensional clustering). |
Overflow % |
Percentage of overflow rows. This should typical be a small value (5%). |
Space Utilization% |
A low space utilization is an indicator of excessive fragmentation (internal and external). This can occur when there are many pages with free space and/or many free pages. This should typically be > 68%. |
Page Utilization % |
A low page utilization is an indicator of excessive external fragmentation. This value should typically be more than 80%. |
Reorg |
This is an indicator of how severe the fragmentation problem for the table is. --- means Overflow%, Space Utilization% and Page Utilization % are all with in the thresholds. *-* means that Overflow%, and Page Utilization % are outside the threshold but Space Utilization% is with in the threshold. |
Tablespace |
Tablespace where the table data resides. |
Indexspace |
Tablespace where the index data resides. |
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