You can obtain statistics on latch use and information on specific latches.
The statistics include the number of requests for latches and the number of times that threads had to wait to obtain a latch. These statistics provide a measure of the system activity.
Information on specific latches includes a listing of all the latches that are held by a thread and any threads that are waiting for latches. This information allows you to locate any specific resource contentions that exist.
You can use the following command-line utilities to obtain information about latches.
Execute onstat -p to obtain the values in the fields lchreqs and lchwaits. These fields store the number of requests for a latch and the number of times that a thread was required to wait for a shared-memory latch. A large number of latch waits typically results from a high volume of processing activity in which the database server is logging most of the transactions. (The administrator cannot configure or tune the number of latches; the database server sets this function internally.) Figure 51 shows onstat -p output, including the lchreqs and lchwaits fields.
... ixda-RA idx-RA da-RA RA-pgsused lchreqs lchwaits 5 0 204 148 151762 12
Execute onstat -s to obtain general latch information. The output includes the userthread column, which lists the address of any user thread that is waiting for a latch (see Figure 52.). You can compare this address with the user addresses in the onstat -u output to obtain the user-process identification number.
Latches with lock or userthread set name address lock wait userthread LRU1 402e90 0 0 6b29d8 bf[34] 4467c0 0 0 6b29d8
Query the sysprofile table to obtain the number of requests for a latch and the number of times a thread had to wait for a latch. The following rows are relevant.