Latch Free Wait Event , Cause, Related SQL queries, Possible Resolution

advertisements

_____________________________________________________________________________________________________________________

wait event in Oracle occurs when a session is trying to acquire a latch but finds it unavailable, resulting in a wait condition. Latches are low-level synchronization mechanisms used by Oracle to protect shared data structures in the SGA (System Global Area) from concurrent access. 

 Explanation: Latches are used to coordinate access to data structures like buffers in the buffer cache, library cache, or other shared memory structures. 

The "Latch Free" wait event happens when a session is waiting for a latch to become available. 

The wait event can occur for various reasons, including contention for a particular latch, inefficient latch acquisition patterns, or excessive latch acquisition requests. 

 Queries: 

 Identifying Sessions with "Latch Free" Wait Event:

advertisements

 

SELECT SID, SERIAL#, EVENT, WAIT_TIME, SECONDS_IN_WAIT
FROM V$SESSION
WHERE EVENT = 'Latch Free';

Identifying Top Wait Events in the Database:

SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO/1000000 AS SECONDS_WAITED
FROM V$SYSTEM_EVENT
ORDER BY TOTAL_WAITS DESC;

Resolution: 

Optimize SQL and Application Design: Poorly designed SQL queries, applications, or coding practices can lead to excessive latch contention. Review and optimize these components. 

Avoid Hot Blocks: Hot blocks in the buffer cache can lead to latch contention. Distribute data and queries evenly across tablespaces and data files to avoid hot blocks. 

Use Parallelism: Parallel execution can reduce latch contention for certain operations. Evaluate if parallelism is suitable for your workload. 

The parameters for governing the library cache were examined. Here we see that we are not using cursor sharing unless they are the exact same SQL. And we have no session_cached cursors.

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ---------
cursor_sharing                       string                           EXACT
open_cursors                         integer                          500
session_cached_cursors               integer                          0
shared_pool_size                     big integer                      524288000

We could check the shared pool tuning to determine whether that might be a valid suggestion to increase the size of the shared pool. Cursor sharing can be implemented to prevent thrashing within the shared pool and the session_cached_cursors can be used to reduce the soft parses. The following Oracle initialization parameter changes resolved the performance degradation that resulted from the latch free wait event.

alter system set session_cached_cursors=150 scope=spfile;
alter system set shared_pool_size=900M scope=spfile;
alter system set cursor_sharing='FORCE' scope=spfile;

Reduce Locks: Excessive locking can lead to latch contention. Minimize the use of locks and transactions where possible. 

Optimize Buffer Cache: Review and optimize the size and configuration of the buffer cache (DB_CACHE_SIZE and DB_KEEP_CACHE_SIZE parameters) to minimize latch contention related to buffer cache access. 

Reduce Parsing: Frequent parsing of SQL statements can lead to latch contention in the library cache. Use bind variables and cursor sharing to reduce parsing. 

Upgrade or Patch: Sometimes, latch contention issues are addressed in Oracle patches or upgrades. Ensure you are using a supported version and apply relevant patches. 

Monitor and Analyze: Continuously monitor latch activity using tools like Oracle Enterprise Manager or custom scripts. Identify specific latches that are contended and focus on optimizing access to them. 

Adjust Parameters: In some cases, adjusting initialization parameters related to specific latches (e.g., _db_handles_cached) can alleviate contention. 

Review Hardware: Hardware-related issues, such as CPU or memory bottlenecks, can lead to latch contention. Ensure your hardware resources are sufficient for the workload. 

Resolving latch contention can be complex and may require a combination of the above approaches. Identifying the specific latches and understanding the workload patterns are key to resolving "Latch Free" wait events effectively.

_____________________________________________________________________________________________________________________

Website Stats