|
--wait_event
set linesize 220
set pagesize 1000
select inst_id,event,count(*)
? from gv$session a
?where a.status='ACTIVE'
?and not (a.type = 'BACKGROUND' and a.state='WAITING' and ?a.wait_class='Idle')
?group by inst_id,event
?order by a.inst_id,count(*) desc;
--wait_event_block
set linesize 220
set pagesize 1000
select inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION, count(*)
? from gv$session a
?where a.status='ACTIVE'
?and not (a.type = 'BACKGROUND' and a.state='WAITING' and ?a.wait_class='Idle') and upper(event) like upper('%&event%')
?group by inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION
?order by inst_id ,count(*) desc, sql_id;
--wait_event_hash
set linesize 220
set pagesize 1000
select a.event, c.plan_hash_value,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets,count(*)
? from gv$session a,gv$sql c
?where a.status='ACTIVE'
?and not (a.type = 'BACKGROUND' and a.state='WAITING' and ?a.wait_class='Idle')
?and a.sql_id=c.sql_id(+)
?and a.sql_child_number=c.CHILD_NUMBER(+)
?and a.inst_id=c.inst_id
?group by a.inst_id,a.event, c.plan_hash_value
?order by a.inst_id,count(*) desc, c.plan_hash_value;
?
--wait_event_sqlid
set linesize 220
set pagesize 1000
select a.inst_id,a.event, a.sql_id,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets, count(*)
? from gv$session a,gv$sql c
?where a.status='ACTIVE'
?and not (a.type = 'BACKGROUND' and a.state='WAITING' and ?a.wait_class='Idle')
?and a.sql_id=c.sql_id(+)
?and a.sql_child_number=c.CHILD_NUMBER(+)
?and a.inst_id=c.inst_id
?group by a.inst_id,a.event, a.sql_id
?order by a.inst_id,count(*) desc, a.sql_id;
--
|