1. 查看活动会话最高的时间点和SAMPLE_ID
select sample_id,sample_time,count(*)
from v$active_session_history a
where sample_time between to_date('xxxxxxxxx','yyyymmddhh24miss') and to_date('xxxxxxxxx','yyyymmddhh24miss')
group by sample_id,sample_time
order by 3 desc;
2. 如果是xxxx库DEADLOCK问题使用以下脚本处理，不是DEADLOCK问题跳到3
select sql_id,event,count(*)
from v$active_session_history a
where sample_id=xxxxxxxxx
and event='buffer deadlock'
group by sql_id,event
order by 3 desc
2.1定位到执行计划慢在哪一行
select sql_id,sql_plan_hash_value,sql_plan_line_id,event,count(*)
from v$active_session_history a
where sample_id=xxxxxxxxx
and sql_id='5zzddv8mwnmpk'
and event is not null
group by sql_id,sql_plan_hash_value,sql_plan_line_id,event
order by 5 desc;
2.2 登录平台查看SQL执行计划定位到热块的对象，比如说SQL_PLAN_LINE_ID=9
3.使用SAMPLE_ID或者SAMPLE_TIME查看数据库TOP EVENT和TOP SQL
select event,count(*)
from v$active_session_history a
where sample_id = xxxxxxxxx
group by event
order by 2 desc;
select sql_id,count(*)
from v$active_session_history a
where sample_time between to_date('xxxxxxxxx','yyyymmddhh24miss') and to_date('xxxxxxxxx','yyyymmddhh24miss')
group by sql_id
order by 2 desc;
4. 关注SQL_EXEC_ID，如果长时间都是同一个，说明这个SQL执行了很久没有结束，可以结合SQL_EXEC_START判断出SQL执行时间
select sql_id,count(*),sample_time,sql_exec_start,sql_exec_id
from v$active_session_history a
where sample_time between to_date('xxxxxxxxx','yyyymmddhh24miss') and to_date('xxxxxxxxx','yyyymmddhh24miss')
and sql_id in ('xxxxxxxxx')
group by sql_id,sample_time,sql_exec_start,sql_exec_id
order by 3
5. 关注DURATION，SQL执行时间是否有变化
select sql_id,sample_time,sql_plan_hash_value, max(sample_time) - SQL_EXEC_START as duration,sql_exec_start,sql_exec_id,min(sql_exec_start)--machine,program,
from v$active_session_history a
where sample_time between to_date('xxxxxxxxx','yyyymmddhh24miss') and to_date('xxxxxxxxx','yyyymmddhh24miss')
and sql_id in ('xxxxxxxxx')
group by sql_id,sql_exec_id,sql_plan_hash_value,sql_exec_start,sample_time--,machine,program
order by 4 desc;
