following few Query's will help to find out culprits-
Query to check long running transaction from last 8 hours
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##
