HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

Load Subsystem 

The Load subsystem is useful in troubleshooting throughput problems.
This section describes how to handle transaction delays, rollbacks, and
lock contention.

Transaction Delays 

The ACTIVE XACT, IMPEDE XACT, and THROTTLE WT fields on the Load screen
help identify the source of transaction delays.  In the following screen,
these fields indicate two problems.


            

The first problem is that the transaction limit has been reached. 
The transaction limit (MAX XACT) for the DBEnvironment is 5.0.  The
average number of active transactions (ACTIVE XACT) is also 5.0.  When
the transaction limit is reached, other sessions that attempt to begin a
transaction are placed in the throttle wait queue.  These sessions must
wait until a transaction slot becomes free.  Notice that on average one
session is waiting in the throttle wait queue.  You can increase the
transaction limit by using

   *   The START DBE statement.

   *   The SQLUtil ALTDBE command.  (However, remember that you must stop
       the DBEnvironment before you use this command.)

The second problem is that other sessions hold incompatible locks. 
The current value of IMPEDE XACT is 1, which means that one active
transaction is waiting to acquire a lock.  On average, IMPEDE XACT is 3
and ACTIVE XACT is 5, which means that 3 out of 5 transactions are
waiting.

This DBEnvironment definitely has a locking problem.  At this point, you
should use the Lock subsystem to obtain more information.

Rollbacks 

In a DBEnvironment with high throughput, transactions are completed
quickly and successfully.  The following screen shows that on average, 2
transactions are started and completed every 10.0 seconds.


            

On average, ROLLBK WORK is 0, which means that typically no transactions
are rolled back.  However, during the last refresh cycle of the LOAD
screen, five transactions were rolled back.

To find out which sessions are rolling back transactions, access the Load
Session screen.  On the following screen, CID 4 is responsible for most
of the rolled back transactions.


            

You can see from the screen that the SET SORTLOAD 3 command has sorted
the sessions in descending order according to ROLLBK WORK activity.

Lock Contention 

The Load screen also provides an overall view of the amount of lock
contention in the DBEnvironment.  In the example that follows, 25% of the
lock requests are forced to wait.  For detailed information on locking,
you should use the screens in the Lock subsystem.


            

MPE/iX 5.0 Documentation