HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

Static Cluster Screen 

This screen provides information about the clustering of indexes and
referential constraints in a DBEFileSet.

	       Click here to view figure.
            

To invoke the Static Cluster screen, use the c command from the Static
subsystem or the /st c command from other subsystems.

Field Definitions 

DBEFILESET       The name of the DBEFileSet.

                 The keyword DETACHED means that the DBEFileSet is
                 detached.  The keywords DETACHED DBEFILES mean that the
                 DBEFileSet is attached, but some of the DBEFiles within
                 it are detached.

OWNER.TABLE      The name of each table in the DBEFileSet that has an
                 index or referential constraint defined upon it.
                 Indexes and referential constraints are listed below the
                 table name, and are preceded by the following labels:

                 (INDEX)          A nonclustering index, created by using
                                  the CREATE INDEX statement or by
                                  specifying a unique constraint.

                 (INDEX*)         A clustering index, created by using
                                  the CREATE CLUSTERING INDEX statement
                                  or by specifying a unique constraint
                                  with the CLUSTERING ON CONSTRAINT
                                  clause in the CREATE TABLE statement.

                 (CONST)          A nonclustering PCR (parent-child
                                  relationship), created by defining a
                                  referential constraint.

                 (CONST*)         A clustering PCR (parent-child
                                  relationship), created by defining a
                                  referential constraint with the
                                  CLUSTERING ON CONSTRAINT clause in the
                                  CREATE TABLE statement.

TABLE PAGES      The number of pages containing data for the table.

TOTAL ROWS       The total number of rows in the table.

UNLOAD/LOAD      The percentage of rows in the table that are not
SUGGESTED %      physically stored in index order, calculated as follows:

                      ( (CCOUNT - TABLE PAGES) / (TOTAL ROWS - TABLE PAGES) ) * 100

                 When the index is well clustered, CCOUNT equals TABLE
                 PAGES, and the percentage is equal to zero.  As the
                 clustering of the index degrades, CCOUNT approaches
                 TOTAL ROWS, and the percentage increases.

                 Performance is best when CCOUNT is equal to TABLE PAGES.
                 As CCOUNT approaches TOTAL ROWS, performance degrades.
                 When the percentage is high, additional I/O is required
                 to perform an index scan over the table.  To improve
                 performance, UNLOAD the data in sorted order and LOAD it
                 back in again with ISQL.

CCOUNT           The total number of pages touched when a complete scan
                 of the table is made in index order.  CCOUNT is
                 incremented each time the next row to be accessed is on
                 a different page than the previous row.  If the data is
                 not physically stored in index order, the same page is
                 touched more than once.

Display Conventions 

The full bright, inverse video bars represent a value of 100%.  The
actual percentage is indicated by the half bright, inverse video bars,
which are delimited on the right with exclamation points.

Related SET Commands 

Use the SET DBEFILESET command to improve performance and display only
those tables contained in a particular DBEFileSet.

MPE/iX 5.0 Documentation