Space Analyst - 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.
Sybase ASE
The following table describes the distinct types of table space inefficiencies that Space Analyst computes for 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 is 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 following table describes the grid data:
| Data | Description |
|---|---|
|
Table Owner |
The owner of the table. |
|
Table Name |
The name of the table. |
|
Segment Type |
Indicates whether 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.
|
|
Number of Blocks |
The total number of blocks allocated to the table. {Note|This column may report NULL if the table is not yet analyzed via the ANALYZE command or through the DBMS_STATS package.}} |
|
Empty Blocks |
The number of empty blocks that the table contains.
|
|
Highwater Mark |
The last block that Oracle will scan up to when searching the table for data.
|
|
Average Used Blocks |
The average number of blocks that the table is using.
|
|
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.
|
|
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.
|
|
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 autoextend property for one or more datafiles is set to true for this tablespace. |
|
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 following table 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 Wizard to change the Index Tablespace in table definition. |
The following table 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 |
|
|
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 following table 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 more than 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.
|
|
Tablespace |
Tablespace where the table data resides. |
|
Indexspace |
Tablespace where the index data resides. |
Available Functionality
Note: Functionality differs 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