1、业务现象:业务高峰期时,数据库处理能力陡降,超时严重。截图如下:
查询active_session_history 发现均是平时的业务语句占用:
select t.sql_id,s.SQL_TEXT, count(*)
from v$active_session_history t,v$sqlarea s
where t.SAMPLE_TIME >
to_timestamp('20190306 15:00:00', 'YYYYMMDD hh24:mi:ss')
and
t.SAMPLE_TIME <
to_timestamp('20190306 15:01:00', 'YYYYMMDD hh24:mi:ss')
-- and t.SESSION_TYPE='FOREGROUND'
and t.SQL_ID=s.SQL_ID
group by t.SQL_ID,s.SQL_TEXT
order by count(*) desc
2、查看等待时间及sql 如下:
selec