Basic Oracle RAC information
                                                 Last update (2010-07-27 14:30:35)
                                                                                                                   Date added (2008-04-01 11:35:56)

Summary
Some useful information about the RAC instances. First when the instances started
SELECT inst_id, instance_name, host_name, VERSION, status, TO_CHAR(startup_time, 'DD/MM/YYYY HH24:MI:SS') STARTUP
FROM gv$instance
ORDER BY inst_id;
Sessions group by instance
SELECT v$active_instances.INST_NAME,  COUNT(*) FROM gv$session, v$active_instances 
WHERE gv$session.inst_id = v$active_instances.INST_NUMBER
GROUP BY v$active_instances.INST_NAME;
GLOBAL CACHE CR PERFORMANCE
This shows the average latency of a consistent block request. AVG CR BLOCK RECEIVE TIME should typically be no more than 3 milliseconds depending on your system configuration and volume, is the average latency of a consistent-read request round-trip from the requesting instance to the holding instance and back to the requesting instance.
For Oracle9i 
SELECT b1.inst_id, b2.VALUE "GCS CR BLOCKS RECEIVED", 
b1.VALUE "GCS CR BLOCK RECEIVE TIME", 
ROUND(((b1.VALUE / b2.VALUE) * 10),3) "AVG CR BLOCK RECEIVE TIME (ms)" 
FROM gv$sysstat b1, gv$sysstat b2 
WHERE b1.NAME = 'global cache cr block receive time' AND 
b2.NAME = 'global cache cr blocks received' AND b1.inst_id = b2.inst_id;
More information you can find at Metalink Note:135714.1

RAC related common wait events
SELECT sw.inst_id, sw.SID, s.serial#, sw.state, sw.event, sw.seconds_in_wait seconds, 
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', 
'queue messages', 'PL/SQL lock timer', 'SQL*Net message to client', 'SQL*Net message from dblink', 
'PX Deq Credit: send blkd', 'PX Deq Credit: need buffer', 'PX Deq: Execute Reply')
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 DESC;
Reviews
Filters
Search