RAC 等待

获得会话等待信息

SET NUMWIDTH 10
COLUMN STATE FORMAT a7 tru
COLUMN EVENT FORMAT a25 tru
COLUMN LAST_SQL FORMAT a40 tru
SELECT sw.inst_id INSTANCE_ID, sw.sid SID, sw.state STATE, sw.event EVENT,
sw.seconds_in_wait SECONDS_WAITING, sw.p1, sw.p2, sw.p3,
sa.sql_text LAST_SQL
FROM    gv$session_wait sw, gv$session s, gv$sqlarea sa
WHERE  sw.event NOT IN ('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action',
'client message', 'pipe get', 'null event', 'PX Idle Wait',
'single-task message', 'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue', 'listen endpoint status',
'slave wait','wakeup time manager')
AND     sw.seconds_in_wait > 0
AND     (sw.inst_id = s.inst_id and sw.sid = s.sid)
AND     (s.inst_id = sa.inst_id and s.sql_address = sa.address)
ORDER BY seconds_waiting DESC;

GES 锁定阻塞者和等待者

-- GES LOCK BLOCKERS:
--INSTANCE_ID    The instance on which a blocking session resides
--SID            Unique identifier for the session
--GRANT_LEVEL Lists how GES lock is granted to user associated w/ blocking session
--REQUEST_LEVEL Lists the status the session is attempting to obtain
--LOCK_STATE     Lists current status the lock has obtained
--SEC Lists how long this session has waited
SET numwidth 10
COLUMN LOCK_STATE FORMAT a16 tru;
COLUMN EVENT FORMAT a30 tru;
SELECT dl.inst_id INSTANCE_ID, s.sid SID ,p.spid SPID,
dl.resource_name1 RESOURCE_NAME,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) AS GRANT_LEVEL,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) AS REQUEST_LEVEL,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Canceling','KJUSERCV','Converting') AS LOCK_STATE,
s.sid, sw.event EVENT, sw.seconds_in_wait SEC
FROM   gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
WHERE  blocker = 1
AND    (dl.inst_id = p.inst_id and dl.pid = p.spid)
AND    (p.inst_id = s.inst_id and p.addr = s.paddr)
AND    (s.inst_id = sw.inst_id and s.sid = sw.sid)
ORDER BY sw.seconds_in_wait DESC;
--GES LOCK WAITERS:
--INSTANCE_ID     The instance on which a blocking session resides
--SID               Unique identifier for the session
--GRANT_LEVEL Lists how GES lock is granted to user associated w/ blocking session
--REQUEST_LEVEL  Lists the status the session is attempting to obtain
--LOCK_STATE       Lists current status the lock has obtained
--SEC               Lists how long this session has waited
SET numwidth 10
COLUMN LOCK_STATE FORMAT a16 tru;
COLUMN EVENT FORMAT a30 tru;
SELECT dl.inst_id INSTANCE_ID, s.sid SID, p.spid SPID,
dl.resource_name1 RESOURCE_NAME,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) AS GRANT_LEVEL,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) AS REQUEST_LEVEL,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Canceling','KJUSERCV','Converting') AS LOCK_STATE,
s.sid,sw.event EVENT, sw.seconds_in_wait SEC
FROM gv$ges_enqueue dl, gv$process p,gv$session s,gv$session_wait sw
WHERE blocked = 1
AND    (dl.inst_id = p.inst_id and dl.pid = p.spid)
AND    (p.inst_id = s.inst_id and p.addr = s.paddr)
AND    (s.inst_id = sw.inst_id and s.sid = sw.sid)
ORDER BY sw.seconds_in_wait DESC;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22123669/viewspace-687054/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22123669/viewspace-687054/