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('20190124 12:00:00','yyyymmdd hh24:mi:ss')
and a.SAMPLE_TIME<to_date('20190124 12:30:00','yyyymmdd hh24:mi:ss')
group by event,a.sql_id,sql_text
order by 4 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('20190124 12: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 by 3 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('20190125 15:50:00','yyyymmdd hh24:mi:ss')
and a.SAMPLE_TIME<to_date('20190125 16:10:00','yyyymmdd hh24:mi:ss')
group by event
order by 4 desc;
4、查询 undo 表空间使用较多的表
select a.segment_name, count(*)
from dba_undo_extents a,
(SELECT n.name name
FROM V$session s, V$transaction t, V$rollstat r, v$rollname n
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 by a.segment_name
order by count(*);
5、查询导致 undo 使用量和使用率高的会话
select b.sid,
文档被以下合辑收录
评论