2、查询一段时间内sql总等待时间
select event,a.sql_id,sql_text,sum(time_waited)
from v$active_session_history a,v$sql s
where a.sql_id=s.sql_id
and a.sample_time>to_date('2019012412:00:00','yyyymmdd hh24:mi:ss')
and a.sample_time'2019012412:30:00','yyyymmdd hh24:mi:ss')
group byevent,a.sql_id,sql_text
order by4 desc;
2、查询一段时间内sql单次执行时间
select sql_id,s.sql_text,s.elapsed_time/s.executions
from v$sqlstats s
where s.last_active_time>to_date('2019012412:00:00','yyyymmdd
hh24:mi:ss')
and s.last_active_time>to_date('20190124 12:00:00','yyyymmdd
hh24:mi:ss')
and s.executions>0
order by3 desc;
3、查询历史会话阻塞等待情况
select
a.sample_time,a.session_id,a.sql_id,a.wait_time,a.blocking_session
from v$active_session_history a
where a.machine='41cb3c835bb1'
and a.sample_time>to_date('2019012515:50:00','yyyymmdd hh24:mi:ss')
and a.sample_time'2019012516:10:00','yyyymmdd hh24:mi:ss')
group byevent
order by4 desc;
4、查询undo表空间使用较多的表
select a.segment_name, count(*)
from dba_undo_extents a,
(select n.name name
fromv$sessions,v$transactiont,v$rollstatr,v$rollnamen
where s.saddr = t.ses_addr
and t.xidusn = r.usn
and r.usn= n.usn) b
where a.segment_name = b.name
and a.status = 'active'
group bya.segment_name
order bycount(*);
5、查询导致undo使用量和使用率高的会话
select b.sid,
文档被以下合辑收录
评论