1. ProTop Knowledge Base
  2. ProTop Trends Dashboard

Latch and Resource Activity Dashboard

The ProTop Trends latch and resource activity dashboard provides detailed statistics on latch usage and waits.

Many of the performance issues related to latch and resource waits can be resolved with simple configuration changes.  Each graph is described below.

Latches

These graphs show latch activity and waits. It is difficult to define "high" latch activity as it is directly correlated to database activity. You should attempt to minimize latch waits as much as possible.

img0050In a very well-tuned system such as this one, we see only a few dozen MTX latch waits per second during business hours, even though database reads are in the millions per second.

Here are descriptions of the latches listed in the image above:

Latch Description
AIB After-image buffer and control blocks
BF1-4 Roll-up counters for buffer pool block latches
BFP Buffer pool control blocks
BHT Buffer pool hash table
BIB Before-image buffer and control blocks
CDC Change Data Capture ??
CPQ Checkpoint queue latches
GST Global storage pool latch
LHT Lock table hash chains
LHT2 Lock table hash chains
LHT3 Lock table hash chains
LHT4 Lock table hash chains
LKF Lock table free chain
LKP Lock table purge chain
LRU Buffer pool LRU chain
MTX BI log space allocation and microtransaction
OM Object cache
PWQ Page writer queue
SCC Shared schema cache
SEQ Sequence cache latches
TXQ Transaction queue latches
TXT Transaction table latches
USR User table latches

Resources

These graphs show resource requests and waits. Similar to latches, our goal is to minimize resource waits.

img0200

Here are descriptions of the Resources in the image above:

Resource Description
DBBuf SL Database buffer share-lock queue
Rec Get Record GET lock (no-lock read) queue
Stmt Cache Statement cache
Crypt Buf Encryption buffer queue
DB Svc EnQ Database service enqueue
Repl TxAck Replication transaction end acknowledgment queue
TXE Excl Lk Transaction end exclusive lock queue
TXE Comm Lk Transaction end commit lock queue
TXE Upd Lk transactions end update lock queue
TXE Shr Lk Transaction end share lock queue
AI Buf Wr After image buffer write queue
AI Buf Rd After image buffer read queue
BI Buf Wr Before image buffer write queue
BI Buf Rd Before image buffer read queue
DBBuf2 Wr Database alternative buffer write queue
DBBuf2 XL Database alternative buffer exclusive-lock queue
DBBuf2 SL Database alternative buffer share-lock queue
DBBuf Avail Database buffer number of times a replacement buffer was needed for the LRU chain but all buffers in the cache were locked
DBBuf XL Database buffer exclusive-lock queue
DBBuf Bkup Database buffer being backed up
DBBuf Wr Database buffer write queue
DBBuf Rd Database buffer read queue
DBBuf IL Database buffer intent lock queue ( index blocks only)
Trx Comm Requests to delete a unique key
Schema Lk  Schema lock queue. Requests to make a schema change.
Shr Mem Shared memory lock queue.

The above details also apply to the "w" panel, Latches and Resources, in ProTop Real-Time.