热块及毛刺问题定位

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;