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;
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 :
Post a Comment