To find Query's Involved in clustered waits # RAC

Step 1 # Check snapshots available on database 
Step 2 # get wait_class_id from gv$session_wait_class
Step 3 # Check what are the wait events occured between snapshots
Step 4 # Drill down, and get SQL ID involved in waits 

Query to check available shots on database
select dhdi.instance_name,
       dhdi.db_name,
       dhs.snap_id,
       to_char(dhs.begin_interval_time,'MM/DD/YYYY:HH24:MI') begin_snap_time,
       to_char(dhs.end_interval_time,'MM/DD/YYYY:HH24:MI')   end_snap_time,
       decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce
  from dba_hist_snapshot dhs,
       dba_hist_database_instance dhdi
 where dhdi.dbid             = dhs.dbid
   and dhdi.instance_number  = dhs.instance_number
   and dhdi.startup_time     = dhs.startup_time
   and dhs.end_interval_time >= to_date(sysdate - &&num_days_back)
 order by db_name, instance_name, snap_id; 

Get "Wait_class_id" from gv$session_wait_class
sql> select distinct wait_class,wait_class#,wait_class_id,inst_id from gv$session_wait_class;
get "wait_class_id" from this step # 

check what are the wait events occurred between snapshots 
SQL> select event_id, event, count(*) cnt from dba_hist_active_sess_history where snap_id between 53477 and 53480 and wait_class_id=3871361733 group by event_id, event order by 3;
3201690383 gc cr grant 2-way                29
1912606394 gc buffer busy acquire         31

You can then drill in further to find out what was causing the two longest waits
sql> select sql_id, count(*) cnt from dba_hist_active_sess_history where snap_id between 53477 and 53479 and event_id in (3201690383, 1912606394) group by sql_id having count(*)>10 order by 2;
SQL_ID               CNT
------------- ----------
aq8yqxyyb40nn         18

Find out SQL_TEXT using sql_id #

sql> select sql_text from dba_hist_sqltext where sql_id='aq8yqxyyb40nn'; update sys.job$ set this_date=:1 where job=:2

---Nikhil Tatineni--
--- Oracle DBA ---