概述
分享两个SQL,这两个SQL只是基础原型,大家自己自定义条件去做一些调整。主要是在前面讲过的dba_hist_active_sess_history相关sql上总结出来的比较实用的sql。
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY. See "V$ACTIVE_SESSION_HISTORY" for further interpretation details for many of these columns (except SNAP_ID, DBID, and INSTANCE_NUMBER).
![c1971feba2b4f032a7136bbae91d4588.png](https://img-blog.csdnimg.cn/img_convert/c1971feba2b4f032a7136bbae91d4588.png)
![ed66981c74e27f5823bcbd1738b71ac8.png](https://img-blog.csdnimg.cn/img_convert/ed66981c74e27f5823bcbd1738b71ac8.png)
![6ea820c058cd7869f9754ea70064d8f3.png](https://img-blog.csdnimg.cn/img_convert/6ea820c058cd7869f9754ea70064d8f3.png)
![2d5c9ebfe0d13bd726808ebe226586b5.png](https://img-blog.csdnimg.cn/img_convert/2d5c9ebfe0d13bd726808ebe226586b5.png)
![442ba05f9a4f577bdbd23acaa03ca4d2.png](https://img-blog.csdnimg.cn/img_convert/442ba05f9a4f577bdbd23acaa03ca4d2.png)
查看故障时间段等待事件、问题sql id及会话访问次数
--alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select trunc(sample_time, 'mi') tm, sql_id, nvl(event,'CPU'),count(distinct session_id) cnt from dba_hist_active_sess_history where sample_time between to_date('2019-08-22 14:00:00') and to_date('2019-08-22 14:30:00') group by trunc(sample_time, 'mi'), sql_id,nvl(event,'CPU') order by cnt desc;
![0067dd496d38ef1c95f345c951822993.png](https://img-blog.csdnimg.cn/img_convert/0067dd496d38ef1c95f345c951822993.png)
查看该sql相关的等待事件及对应的会话访问次数
select sql_id, nvl(event, 'CPU'), count(distinct session_id) sz from dba_hist_active_sess_history a, dba_hist_snapshot b where sample_time between to_date('2019-08-22 14:00:00') and to_date('2019-08-22 15:00:00') and sql_id = '4ksvn2rgjnhcm' and a.snap_id = b.snap_id and a.instance_number = b.instance_number group by sql_id, nvl(event, 'CPU') order by sz desc;
![573b73c6e69b567be36738728e51260f.png](https://img-blog.csdnimg.cn/img_convert/573b73c6e69b567be36738728e51260f.png)
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
![9c1383d2c802ef4d30fc994adad43503.gif](https://img-blog.csdnimg.cn/img_convert/9c1383d2c802ef4d30fc994adad43503.gif)