HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

Overview Subsystem 

You can use the Overview subsystem to determine the overall cause of a
performance problem, and then go to another subsystem for detailed
information.  For example, with the Overview screen you can detect

   *   transaction limit reached
   *   lock contention
   *   memory limit reached
   *   high data buffer miss rate
   *   log full condition

Transaction Limit Reached 

If a session attempts to begin a transaction, but the number of active
transactions is at the maximum, the session must wait until a transaction
slot becomes available.  How long the session waits depends on the
timeout value in effect for the session. 
In the example that follows, the ISQL session times out because it cannot
begin a new transaction:

     isql=> SET USER TIMEOUT 3; 
     isql=> BEGIN WORK; 
     Timeout expired (3 seconds).  (DBERR 2825)

The following screen shows that the transaction limit, MAX XACT, and the
number of active transactions, ACTIVE XACT, are both 5.


            

Therefore, you may need to raise the transaction limit.  Use the Load
subsystem for more information.

Lock Contention 

The next screen shows that there are 5 active transactions (ACTIVE XACT)
and 4 impeded transactions (IMPEDE XACT). This means that 4 out of 5
transactions are waiting to acquire a lock.


            

We also see that 25% of all lock requests are not granted immediately
because other sessions hold incompatible locks.


            

This DBEnvironment definitely has a locking problem.  You should use the
Lock subsystem to get more information.

Memory Limit Reached 

The runtime control block is an area of shared memory containing global
runtime information for the DBEnviroment.  All ALLBASE/SQL control blocks
are allocated from the runtime control block, and the majority of control
blocks are used for lock management.  For more information, see the
section "Shared Memory Considerations" in the chapter "Guidelines on
Transaction Design."

In the next screen, the runtime control block is 95% full. 

You can either

   *   increase its size using the SQLUtil ALTDBE command.  However, you
       must stop the DBEnvironment before you can issue this command.

   *   use the Lock Memory screen to identify the sessions that have the
       greatest number of locks.


            

After you have identified the sessions that have the most locks, use the
Lock TabSummary screen to identify the programs each session is running
and the tables that have the greatest number of locks.  You may wish to
change some PUBLICROW tables to PUBLIC to reduce the memory overhead
associated with them.

High Data Buffer Miss Rate 

If the value of the DATA BUFFER MISS RATE field on the Overview screen is
high, DBEnvironment performance degrades due to increased I/O. You can
use the IO subsystem to identify the sessions and programs that are
contributing to the data buffer miss rate.

Log Full Condition 

You can monitor log file capacity by checking the LOG FULL field on the
Overview screen.  In the example that follows, the archive log is 99%
full.


            

If you enter the following UPDATE statement to update the
PurchDB.SupplyPrice table, it fails because the log is so full:

     isql=> UPDATE PurchDB.SupplyPrice SET UnitPrice = UnitPrice * 1.2; 
     Log full.  (DBERR 14046)
     INSERT/UPDATE/DELETE statement had no effect due to execution errors.
     (DBERR 2292)
     Number of rows processed is 0

You can avoid this problem by using the Overview screen to monitor log
file capacity and by adding log files before the logs are full.  See the
chapter "Backup and Recovery" in the ALLBASE/SQL Database Administration 
Guide for instructions.

MPE/iX 5.0 Documentation