Oracle Performance Tuning Analyzing Latch Contention with Time Waited for Latches Using Oracle Tables v$system_event and v$sysstat

Oracle Performance Tuning: Analyzing Latch Contention with "Time Waited for Latches" using Using Oracle Tables v$system_event and v$sysstat

Purpose

In the world of Oracle database performance tuning, identifying and resolving latch contention is critical for maintaining a responsive system. Latches are low-level serialization mechanisms that protect shared data structures within the Oracle SGA (System Global Area). When multiple processes compete for the same latch, it can lead to delays and impact overall database performance.

The "Time Waited for Latches" query is a valuable tool in your performance tuning arsenal, providing insights into the specific events and wait times associated with latch contention.

Sample SQL Command

 1col event format a30
 2select	event
 3,	time_waited
 4,	round(time_waited*100/ SUM (time_waited) OVER(),2) wait_pct
 5from	(
 6	select	event
 7	,	time_waited
 8	from	v$system_event
 9	where	event not in (
10		'Null event'
11		,'client message'
12		,'rdbms ipc reply'
13		,'smon timer'
14		,'rdbms ipc message'
15		,'PX Idle Wait'
16		,'PL/SQL lock timer'
17		,'file open'
18		,'pmon timer'
19		,'WMON goes to sleep'
20		,'virtual circuit status'
21		,'dispatcher timer'
22		,'SQL*Net message from client'
23		,'parallel query dequeue wait'
24		,'pipe get')
25	union
26		(
27		select	name
28		,		value
29		from	v$sysstat
30		where name like 'CPU used when call started'
31		)
32	)
33order by 2 desc
34/

Code Breakdown

The provided SQL code retrieves information about the various wait events in the Oracle database, particularly focusing on those that indicate latch contention. It calculates the percentage of total wait time for each event, allowing you to quickly identify the primary contributors to performance issues.

Code Breakdown:

  1. Column Formatting:

    • col event format a30: Formats the 'event' column to a width of 30 characters for better readability.
  2. Main Query:

    • select event, time_waited, round(time_waited*100/ SUM (time_waited) OVER(),2) wait_pct: Selects the event name, the total time waited for that event, and calculates the percentage of the total wait time attributed to that event.
  3. Subqueries and Union:

    • from ( select event, time_waited from v$system_event where event not in (...) ): This subquery retrieves event names and wait times from thev$system_eventview, filtering out common non-latch-related events.
    • union ( select name, value from v$sysstat where name like 'CPU used when call started' ): This subquery includes CPU usage information fromv$sysstat, which can sometimes be relevant to performance analysis.
  4. Ordering:

    • order by 2 desc: Sorts the output in descending order based on the time_waited column, highlighting the events with the highest wait times.

Key Points and Insights:

  • Identifying Latch Contention: Look for events with high wait_pct values. These are the primary areas where latch contention is impacting performance.
  • Common Latch Contention Events: Some common latch-related events to watch out for include:
    • latch free
    • cache buffers chains
    • cache buffers lru chain
    • library cache
    • shared pool
  • Further Analysis: Once you've identified the problematic events, further investigation may be required to understand the root cause of the contention. Use tools like Oracle's AWR (Automatic Workload Repository) or ASH (Active Session History) to drill deeper into the specific SQL statements and database objects involved.

Explanations:

  • v$system_event: This dynamic performance view provides information about various wait events in the database.
  • v$sysstat: This view contains statistics about the system's performance, including CPU usage.
  • time_waited: Represents the total time (in microseconds) that processes have waited for a specific event.
  • wait_pct: Indicates the percentage of the total wait time attributed to each event.

Conclusion:

By actively monitoring and addressing latch contention using the "Time Waited for Latches" query, you can ensure optimal performance for your Oracle database. Remember that performance tuning is an ongoing process, and regular analysis is key to maintaining a healthy and efficient system.

Reference Links:

Posts in this series