RAC sql
RAC Instances, DB instances, instance status, List of all instances, with startup time, up till, host load.
Current Transaction for RAC
Session Wait for RAC
Sessions connected for RAC
Locks (Holder/Waiter) for RAC
col host_name for a16
select i.instance_number, i.host_name, i.instance_name,
to_char(startup_time, 'DD Mon YYYY HH24:Mi') startup_time,
floor(sysdate - startup_time) uptill, i.status, round(o.value, 3) value
from gv$instance i, gv$osstat o
where i.instance_number = o.inst_id
and o.stat_name = 'LOAD' order by instance_number;
-- e.g.
INSTANCE_NUMBER HOST_NAME INSTANCE_NAME STARTUP_TIME UPTILL STATUS VALUE
--------------- ---------------- ---------------- -------------------------- ---------- ------------ ----------
1 proddbx00101 dex011 12 Feb 2018 02:52 247 OPEN 15.979
2 proddbx00102 dex012 12 Feb 2018 05:25 247 OPEN 18
3 proddbx00103 dex013 12 Feb 2018 07:23 247 OPEN 21.59
4 proddbx00104 dex014 12 Feb 2018 09:37 247 OPEN 19.079
Current Transaction for RAC
set line 250
col "username_sid_inst" for a35
select s.username||'('||s.sid||', '||s.inst_id||')' username_sid_inst,s.sid,t.start_time,
t.used_ublk as "Block Count", round(t.used_ublk*8192/1024/1024,2) Size_MB
from gv$transaction t,gv$session s where s.saddr=t.ses_Addr and t.inst_id=s.inst_id order by start_time;
Session Wait for RAC
set line 250
col username for a24
col event for a45
col 'FileId# Obj#' for a16
select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#",
s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
from gv$session s, gv$session_wait sw
where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0
order by s.inst_id, s.username;
Sessions connected for RAC
-- Group by User (Summary)
select s.username, count(*) from gv$session s
group by s.username order by s.username;
-- Group by Instance, User
select s.inst_id, s.username, count(*)
from gv$session s
group by s.inst_id, s.username
order by s.inst_id, s.username;
Locks (Holder/Waiter) for RAC
column sess format A20
select substr(DECODE(request,0,'Holder: ','Waiter: ')||sid||' ('||inst_id||')',1,18) sess,
id1, id2, lmode, request, type, to_char(to_date(ctime,'sssss'),' hh24:mi:ss') time_h_m_s
from gv$lock
where (id1, id2, type) IN
(select id1, id2, type from gv$lock where request>0)
order by id1, request, ctime desc;
