Querys to monitor RAC

following few  Query's will help to find out culprits- 

Query to check long running transaction from last 8 hours 

Col Sid Format 99999 

Col Serial# Format 999999 

Col Machine Format A15 Truncate 

Col Event Format A30 Truncate 

Col Inst Format 9999 

Select Inst_Id Inst,Sid,Serial#,Username,Machine,Event, Logon_Time,Sql_Id,Prev_Sql_Id From Gv$Session where type != 'BACKGROUND' and event not like 'SQL*Net%' and event not like 'Streams AQ: waiting%' And Nvl(24 * (Sysdate - Logon_Time),0) > 8 Order By Username; 

Active sessions on All nodes of RAC Cluster

SeleCT DISTINCT osuser, gv$session.inst_id, Sid, username, Substr(program,1,19) PROG , sql_text From gV$Session, gV$Sql Where status = 'ACTIVE' and gv$session.inst_id = gv$sql.inst_id And username is not null And gv$session.sql_hash_value = hash_value and gv$session.sql_address = gv$sql.address and sql_text not like '%DISTINCT osuser%' order by 2; 

Query to check highly resource SQL 

Select Inst_id, Sql_Id, First_Load_Time, Round(Elapsed_Time/1000000) Secs, Rows_Processed, Executions, Buffer_Gets, Disk_Reads, Sql_Text From Gv$Sql Where Upper(Sql_Text) Like '%SELECT TBD%' And Executions > 0 Order By 1;

Waitevents associated with culprit SQL can be known from wait v$active_session_history”

col SAMPLE_TIME format a25

col event format a30

select sample_time,EVENT,WAIT_TIME,TIME_WAITED from v$active_session_history where sql_id = ‘<CULPRITSQLID>’ order by sample_time;

##Nikhil Tatineni##

##Oracle Database##