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.
In 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.
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.