Space Analyst for Oracle, Sybase ASE, and IBM DB2 for Linux, Unix, and Windows - Tables Tab

From DBArtisan
Jump to: navigation, search

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:

Topics