How to analyze v$active_session_history table to find the wait events in oracle

v$active_session_history is a important table to find the wait events inside oracle. In this article we'll show to use this table to find following important information.



a. Where the db has waited in last 30 mins.
b. In last one days which are the top users who waited most
c. Which are the top waiting sqls in last one days
and most important
d. what are the objects which is experiencing wait and for what reason

Lets see the scripts directly.

a. where the db has waited in last 30 mins

select a.event
      , sum(a.wait_time+a.time_waited) wait_time
from v$active_session_history a
where a.sample_time  between (sysdate-60/2880) and sysdate
group by a.event
order by 2 desc



b. In last one days which are the top users who waited most
add this query with v$session to know the top waited users.

select b.sid
      , b.username
      , sum(a.wait_time+a.time_waited) wait_time
from v$active_session_history a, v$session b
where a.sample_time  > sysdate-1
and   a.session_id = b.sid
group by b.sid, b.username
order by 3 desc

c. which are the top waiting sqls in last one days

select a.user_id
      , b.username
      ,c.sql_text
      , sum(a.wait_time+a.time_waited) wait_time
from v$active_session_history a, dba_users b, v$sqlarea c
where a.sample_time  > sysdate-1
and   a.sql_id = c.sql_id
and   a.user_id=b.user_id
group by a.user_id, b.username,c.sql_text
order by 4 desc



d. what are the objects which is experiencing wait and for what reason
Now come to the part to focus on exact objects where the delays is happening. The following query not only identify that object as well as identify the delay reason. If we find the delay is due to network or application related, we can exclude that object from performance tuning.

select
   obj.object_name,
   obj.object_type,
   ash.event,
   sum(ash.wait_time + ash.time_waited) wait_time
from
   v$active_session_history ash,
   dba_objects              obj
where
   ash.sample_time > sysdate -1
and
   ash.current_obj# = obj.object_id
group by
   obj.object_name,
   obj.object_type,
   ash.event
order by 4 desc;





No comments :