HANDY ASH SQL QUERIES ORACLE

Below are the SQL queries which can be used for troubleshooting of a performance bottleneck

--Query to find the ASH sample id at the time of issue

select min(sample_id),max(sample_id)
from v$active_session_history
where to_char(sample_time,'dd-mm-yy hh24:mi:ss') between
'10-09-21 03:20:00'
and
'10-09-21 03:30:00';

--Query to find the wait class id at when the issue occured

select * 
from 
(
select session_id,wait_class_id,wait_class,count(*) TC 
from v$active_session_history
where sample_id between 1399711 and 1400300
group by session_id,wait_class_id,wait_class
order by 4 desc
)
where rownum < 6;

--Query to find the wait event id at when the issue occured

------------------------------------------------------------------------------------------------------------------
select event_id,event,count(*) TC
from v$active_session_history
where sample_id between 1399711 and 1400300
and wait_class_id=1740759767
group by event_id,event
order by 3 desc;
------------------------------------------------------------------------------------------------------------------
--Query to find the SQL id at when the issue occured

select * 
from
(
select sql_id,count(*) TC
from 
v$active_session_history
where sample_id between 1399711 and 1400300
and 
event_id=2614864156
group by sql_id 
having count(*) > 1
order by 2 desc
)
where rownum < 6;
---------------------------------------------------------------------------------------------------------------------

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s