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;
