Latch Monitor 
Definition
This submonitor in the SAP/Oracle Database Monitor lets you view Oracle latch activity. A latch is a low-level serialization mechanism to protect shared data structures by preventing concurrent access to shared data structures in the Shared Global Area (SGA). Processes often have to wait to obtain a latch in order to access the data, which wastes CPU cycles.
Use
You choose Detailed Analyses ® Resource Consumption ® Latch Monitor.
You can view history information in this monitor.
Structure
Entries marked “RAC only” are only relevant for Oracle Real Application Cluster (RAC).
· Latch Overview
You can use this tab page to identify the latches with the worst hit rates and the latches causing the most sleeps. There might be a problem if one of the library cache latches is causing the most sleeps.
This tab page displays the following information:
Column |
Description |
Name |
Latch name |
Inst Id RAC only |
Instance ID |
Wait time |
Elapsed time waiting for the latch in microseconds |
% Wait time |
Wait time as a percentage of total wait time |
Gets |
Number of times the latch was requested in “willing-to-wait” mode and the requestor had to wait |
Misses |
Number of times the latch was requested in willing-to-wait mode and the requestor had to wait |
Misses/Gets |
Ratio of Misses to Gets |
Sleeps |
Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch |
% Sleeps |
Sleeps as a percentage of total sleeps |
Immediate Gets |
Number of times a latch was requested in no-wait mode |
Immediate Misses |
Number of times a no-wait latch request did not succeed (that is, missed) |
Spin Gets |
Willing-to-wait latch requests which missed the first try but succeeded while spinning |
Sleep 1 |
Waits that slept once |
Sleep 2 |
Waits that slept twice |
Sleep 3 |
Waits that slept three times |
Sleep 4 |
Waits that slept four times |
To see the children of the selected latch, select a row and choose Latch Children.
· Latches Overview with Total Lines – RAC only
This tab page displays the same information as in the table above plus Total lines for each Name. This helps you identify a latch monitor problem that is not caused by a specific instance.
- Latch Holder
...
This tab page shows details of the latch holders, based on the view GV$LATCHHOLDER. It helps you to identify if the session holding the latch is changing and to check whether a latch is stuck on a particular session.
This tab page displays the following information:
Column |
Description |
Name |
Latch name |
Inst ID RAC only |
Instance ID |
SID |
ID of the session that owns the latch |
- Latch Children
This tab page shows the number of children for the latches shown
Column |
Description |
Name |
Latch name |
Inst ID RAC only |
Instance ID |
Count |
Number of children |
- Latch Holders SQL Stmt
This tab page shows the SQL statements that are currently being executed by the latch holders, based on the view GV$LATCHHOLDER. Be sure to refresh the display frequently. To view the detailed SQL statement, choose More Details.
Column |
Description |
Name |
Latch name |
Inst ID RAC only |
Instance ID |
SID |
ID of the session that owns the latch |
- Cache Buffers Chains
Before you view, make sure that you have implemented SAP Note 159510 and use SAP$BH instead of X$BH.
This tab page shows cache buffer chains, based on the view GV$LATCH_CHILDREN. The default view is the top 200, ordered by wait time (descending) and sleeps (descending). You can use it to identify hot blocks (that is, frequently accessed blocks) in the buffer cache and also, in some cases, poorly tuned SQL statements.
Column |
Description |
Name |
Latch name |
Inst ID RAC only |
Instance ID |
Address |
Address of the latch object |
Wait Tm(ms) |
Elapsed time waiting for the latch in microseconds |
% Wait Tm |
Wait time as a percentage of total wait time |
% Ttl Wait Tm |
Percentage of total wait time |
Gets |
Number of times the latch was requested in “willing-to-wait” mode and the requestor had to wait |
% Gets |
Percentage of total gets |
Misses |
Number of times the latch was requested in willing-to-wait mode and the requestor had to wait |
% Misses / Gets |
Misses as a percentage of gets |
Sleeps |
Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch |
% Sleeps |
Sleeps as a percentage of total sleeps |
% Ttl Sleeps |
Percentage of total sleeps |
Imm Gets |
Number of times a latch was requested in no-wait mode |
Imm Misses |
Number of times a no-wait latch request did not succeed (that is, missed) |
% Imm Misses / Imm Gets |
Percentage of immediate misses to immediate gets |
Waits Holding Ltc |
Number of waits for the latch while the waiter was holding a different latch |
Spin Gets |
Willing-to-wait latch requests that missed the first try but succeeded while spinning |
% Sleeps / Gets |
Percentage of sleeps to gets |
You can choose All Cache Buffers Chains to view all entries, not just the first 200.
You can choose Hot Blocks to view the most frequently accessed blocks in the buffer cache.
- Latch Protected Stmts in Library Cache
This tab page shows statements in the library cache that are protected by a latch. The library cache latch serializes access to the objects in the library cache. Every time an SQL statement, a PL/SQL block, or a stored object (that is, procedures, packages, functions, or triggers) is executed this latch is acquired.
¡ On tab page SQL Stmts for Latches of Top-20 SQL Statements, you can see the latches from the top 20 SQL statements:
Column |
Description |
Name |
Latch name |
Inst ID RAC only |
Instance ID |
User |
User ID |
Executions |
Number of executions |
% Executions |
Percentage of executions |
Parse Calls |
Number of parse calls |
% Parse Calls |
Percentage of parse calls |
Parse Calls / Executions (%) |
Parse calls as a percentage of executions |
¡ On tab page SQL Stmts for Top Latch Protected SQL Stmts you can see the latches from the top protected SQL statements:
Column |
Description |
Child latch |
Latch name |
Inst ID RAC only |
Instance ID |
Address |
Address of the latch object |
Child latch |
Number of the child latch |
Hash value |
Hash value of the SQL statement |
User Name |
Name of the user connected to the database. For example, SAP applications connect as user SAPR3. |
Executions |
Number of executions that took place on this object since it was brought into the library cache |
Parse Calls |
Number of parse calls for this child cursor |
Parse calls / Exe |
Number of parse calls for each execution |
% Parse Calls |
Percentage of parse calls |
CPU Time(ms) |
CPU time in milliseconds |
CPU Time/Exe (ms) |
CPU time in milliseconds for each execution |
% CPU Time |
Percentage of CPU time |
Elapsed time |
Elapsed time in milliseconds |
Elapsed time/Exe |
Elapsed time in milliseconds for each execution |
% Elapsed Time |
Percentage of elapsed time |
Disk Reads |
Number of disk reads for this child cursor |
Disk reads/Exe |
Number of disk reads for each execution |
% Disk Reads |
Percentage of disk reads |
Buffer Gets |
Number of buffer gets for this child cursor |
Buffer Gets/Exe |
Number of buffer gets for each execution |
% Buffer Gets |
Percentage of buffer gets |
Rows processed |
Number of rows the parsed SQL statement returns |
Rows processed/Exe |
Number of rows processed for each execution |
Module |
Name of the module that was executing at the time that the SQL statement was first parsed |
SQL Stmt |
SQL statement for this child cursor |
You can select a row and choose Execution Plan of SQL Statement to see the execution plan of the selected SQL statement.
You can select a row and choose Call Point in ABAP Program to see the ABAP call point of the selected SQL statement.
Integration
This monitor is based on the following views:
- GV$SQL
- GV$OBJECT_DEPENDENCY
- GV$DBA_SEGMENTS
- GV$SYSSTAT
- GV$SEGMENT_STATISTICS