HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

Static Subsystem 

The Static subsystem allows you to troubleshoot full DBEFileSets, poorly
clustered indexes, indirect rows, and hash overflow pages.

Full DBEFileSets 

A transaction fails if it attempts to insert a row into a table whose
DBEFileSet is full.  To prevent this, you should monitor DBEFileSet
capacity on a regular basis.  The Static DBEFile screen displays the
capacity of each DBEFile and DBEFileSet in a DBEnvironment.  In the
following Static DBEFile screen, the InvoiceFS DBEFileSet is 92% full.
It contains only 12 pages and should be expanded.


            

You can plan for capacity by monitoring the growth of DBEFileSets and the
database objects contained within them.

Poorly Clustered Indexes 

A poorly clustered index forces ALLBASE/SQL to access more physical pages
during index scans.  Performance degrades because I/O increases.
Applications that frequently access rows in index order perform better if
the rows are physically stored together on disk in index order.

To check the clustering of an index, go to the Static Cluster screen.  In
the following example, the CCOUNT of the InvoiceIndex is approximately
equal to the number of rows in the table, which indicates that the data
is poorly clustered.


            

If this index is used frequently to access rows, you should unload the
PurchDB.Invoice table in sorted order and reload it with ISQL. For more
information, see the section "Clustering Indexes" in the chapter
"Guidelines on Logical and Physical Design."

Indirect Rows 

Avoid indirect rows, because they waste disk space and increase the
amount of I/O needed to access data.  Use the Static Indirect screen to
detect the presence of indirect rows.  On the screen that follows, the
PurchDB.Invoice table has 12% indirect rows.  For instructions on how to
remove indirect rows, see "Unloading and Reloading to Remove Indirect
Rows" in the chapter "Guidelines on Logical and Physical Design."


            

Hash Overflow Pages 

As the number of hash overflow pages grows, the amount of I/O necessary
to obtain table data increases.  To monitor the overflow pages of a hash
structure, access the Static Hash screen.  In the next example, the
PurchDB.Invoice table has seven overflow pages, and should be unloaded
and reloaded to improve performance.


            

For instructions, see "Unloading and Reloading to Remove Overflow Pages"
in the chapter "Guidelines on Logical and Physical Design."
[REV END]

MPE/iX 5.0 Documentation