Database Reference
In-Depth Information
3.
Busy wait events:
Time is accounted to these events if the block transfer suffered from
additional processing delays encountered by LMS process. For example, LMS process may
have applied undo records to create CR mode blocks.
4.
Congestion-related events:
These events imply that delays were incurred since the LMS
process was too busy servicing other requests or the LMS process was suffering from a
resource starvation.
Wait events can be further grouped depending on the number of instances participating in a block transfer. Up
to three instances can participate in a block transfer. Requesting instance is the instance requesting the block, master
instance tracks the resource status, and the owner instance currently holds a lock on the resource. If three instances
participate in a block transfer, then that block transfer is tagged with 3-way wait events. In some cases, the resource
master instance might be the owner instance too. In that case, only two instances participate in a block transfer;
hence, those wait events are tagged with 2-way wait events.
A Generic Analysis for all Wait Events
You can identify the object, sql_id, or distribution of wait time for any wait event. This section provides general
guidance to understand the details of all RAC wait events. Use this section in conjunction with individual RAC wait
events to understand the performance bottlenecks better.
Identify Object
Active Session History (ASH) can be queried to identify the objects suffering from wait events. Listing 10-3 introduces
script ash_gcwait_to_obj.sql to identify the object_name.
Listing 10-3.
Script: ash_gcwait_to_obj.sql
col owner format a30
col object_name format a30
set lines 160
WITH ash_gc AS
(SELECT inst_id, event, current_obj#, COUNT(*) cnt
FROM gv$active_session_history
WHERE event=lower('&event')
GROUP BY inst_id, event, current_obj#
HAVING COUNT (*) > &threshold )
SELECT * FROM
(SELECT inst_id, nvl( owner,'Non-existent') owner ,
nvl ( object_name,'Non-existent') object_name,
nvl ( object_type, 'Non-existent') object_type,
cnt
FROM ash_gc a, dba_objects o
WHERE (a.current_obj#=o.object_id (+))
AND a.current_obj# >=1
UNION
SELECT inst_id, '', '', 'Undo Header/Undo block', cnt
FROM ash_gc a WHERE a.current_obj#=0
UNION
SELECT inst_id, '', '', 'Undo Block', cnt
