Wednesday, February 01, 2012

RAC Wait events

Important RAC wait events

SQL> select event from v$system_event  where event like '%global%' order by event;

EVENT
----------------------------------------------------------------
buffer busy global CR
buffer busy global cache
ges global resource directory to be frozen - no
ges global resource directory to be unfrozen - no
global cache busy
global cache cr request
global cache domain validation - no
global cache null to s
global cache null to x
global cache open s
global cache open x
global cache s to x

buffer busy global cache

This wait event falls under the umbrella of ‘global buffer busy events’. This wait event occurs when a user is waiting for a block that is currently held by another session on the same instance and the blocking session is itself waiting on a global cache transfer.

buffer busy global CR

This wait event falls under the umbrella of ‘global buffer busy events’. This wait event occurs when multiple CR requests for the same block are submitted from the same instance before the first request completes, users may queue up behind it

global cache busy

This wait event falls under the umbrella of ‘global buffer busy events’. This wait event means that a user on the local instance attempts to acquire a block globally and a pending acquisition or release is already in progress.

global cache cr request

This wait event falls under the umbrella of ‘global cache events’. This wait event determines that an instance has requested a consistent read version of a block from another instance and is waiting for the block to arrive

global cache null to s and global cache null to x

This wait event falls under the umbrella of ‘global cache events’. These events are waited for when a block was used by an instance, transferred to another instance, and then requested back again.

global cache open s and global cache open x

This wait event falls under the umbrella of ‘global cache events’. These events are used when an instance has to read a block from disk into cache as the block does not exist in any instances cache. High values on these waits may be indicative of a small buffer cache, therefore you may see a low cache hit ratio for your buffer cache at the same time as seeing these wait events.

global cache s to x

This wait event falls under the umbrella of ‘global cache events’. This event occurs when a session converts a block from shared to exclusive mode.


To find locks in RAC
SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request
;

3 comments:

Unknown said...

This is an amazing post. It is also very informative. This will help many people solve their problems. Thanks for posting
Ethernet Over Mains

BalaGanesh Gembali said...

Thanks.

Feroz Mohd said...

good to troubleshoot

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...