Which sql is causing my cluster waits?

from Cluster Waits to sql 

define p_inst=1
define p_days=7
column dt heading 'Date/Hour' format a11
set linesize 500  pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;


'              '11'              '12'              '13'              '14'              '15'              '16'              '17'              '18'              '19'              '20'              '21'              '22'              '23'
----------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- -----------------
11/10/21                37346             37347             37348             37349             37350             37351             37352             37353             37354             37355             37356             37357             37358             37359             37360             37361             37362             37363             37364             37365             37366             37367             37368             37369
11/11/21                37370             37371             37372             37373             37374             37375             37376             37377             37378             37379             37380   



col wait_class for a20
select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 37346 and 37347
group by wait_class_id, wait_class
order by 3;



    WAIT_CLASS_ID WAIT_CLASS                         CNT
----------------- -------------------- -----------------
       4166625743 Administrative                       1
       2723168908 Idle                                 1
       4108307767 System I/O                          10
       1893977003 Other                               22
       3871361733 Cluster                            528
       1740759767 User I/O                           669
                                                    1072

7 rows selected.


col event for a30
select event_id, event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 37346 and 37347 
and wait_class_id=1740759767
group by event_id, event
order by 3;


col event for a30
select event_id, SQL_ID,event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 37346 and 37347
and wait_class_id=1740759767
group by event_id,sql_id, event
order by 3;


         EVENT_ID SQL_ID        EVENT                                        CNT
----------------- ------------- ------------------------------ -----------------
       2652584166 8cnh50qfgwg73 db file sequential read                      478
       3056446529 8cnh50qfgwg73 read by other session                        191


select sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 37346 and 37347
and event_id in ( 2652584166,3056446529 )
group by sql_id
having count(*)>200
order by 2
 ;
 
 
 define sql_id='8cnh50qfgwg73'
 select sql_text from dba_hist_sqltext where sql_id='&sql_id';


define sql_id='8cnh50qfgwg73'
col b.sql_text 	for a70 wrap
col username 	for a20
col sql_opname 	for a15
col sample_time for a25
col program 	for a25
col module 		for a20
col machine 	for a20
select a.SQL_ID,c.username,a.sample_time, a.sql_opname, a.sql_exec_start, a.program, a.module, a.machine,b.sql_text 
from dba_hist_active_sess_history a, dba_hist_sqltext b, dba_users c
where a.SQL_ID = b.SQL_ID(+)
and a.user_id=c.user_id
--and c.username='username'
and a.sql_id='&sql_id'
and rownum<10
order by a.sql_exec_start asc;


define sql_id='8cnh50qfgwg73'
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', format=>'ADVANCED'));


set lines 500 pages 5000 trimspool on trimout on verify off

define sql_id='8cnh50qfgwg73'
DEF from_time=sysdate-1/24/60*5
DEF to_time=sysdate

--DEF cols=session_state,event,sql_id

DEF cols=session_state,event,sql_id


SELECT * FROM (
  SELECT
        &cols
      , count(*)
      , lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
    FROM
        -- active_session_history_bak
        gv$active_session_history
        -- dba_hist_active_sess_history
    WHERE
        sample_time BETWEEN &from_time AND &to_time
  --  AND where_clause
  and sql_id='&sql_id'
    GROUP BY
        &cols
    ORDER BY  percent DESC
)
WHERE ROWNUM <= 20
/



SESSION EVENT                                                            SQL_ID          COUNT(*) PERCENT
------- ---------------------------------------------------------------- ------------- ---------- ----------------------------------------
WAITING db file sequential read                                          8cnh50qfgwg73        198        35%
WAITING gc buffer busy acquire                                           8cnh50qfgwg73        166        29%
WAITING read by other session                                            8cnh50qfgwg73         87        15%
WAITING gc cr disk read                                                  8cnh50qfgwg73         62        11%
ON CPU                                                                   8cnh50qfgwg73         58        10%
WAITING gc cr request                                                    8cnh50qfgwg73          1         0%

6 rows selected.




select
    sql_id
  , sql_hash_value
  , sql_child_number
  , count(*)
from
    gv$session
where
    status='ACTIVE'
and type !='BACKGROUND'
and sid != (select sid from v$mystat where rownum=1)
 and sql_id='&sql_id'
group by
    sql_id
  , sql_hash_value
  , sql_child_number
order by  count(*) desc
/



col parsed format a6
col sql_text format a40
set lines 200  pages 300

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id 
 and  stat.snap_id=snap.snap_id and
 snap.begin_interval_time>=sysdate-1 
-- and  lower(sql_text) like '%t%' 
 and  parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by elapsed_time_delta asc;