Oracle动态视图v$active_session_history实战示例

Oracle动态视图实战之v$active_session_history

先看下官方解释

• Samples of wait event information are taken once per second and made available using the V$ACTIVE_SESSION_HISTORY view. An active session is one that is waiting on CPU or any event that does not belong to the "Idle" wait class at the time of the sample. The sample information is written to a circular buffer in the SGA, so the greater the database activity, the less time the information will remain available for.
• 有几个关键点：1秒采集一次，执行时间很快远小于1秒的SQL基本不会采集到，只写入非空闲状态的事件，循环存放活动越多保存的时间就越短。

实际工作中主要应用

v$active_session_history的字段非常丰富，实际工作中主要应用在下面这些情况：

a.应用场景：开发反应2023-03-02 00:22至00:35，数据落盘慢，根据情况查看此时间段的主要活动事件，数量，与sql_id（全局）
select count(*), sql_id, event, blocking_session
 from gv$active_session_history
 where sample_time between
 to_date('2023-03-02 00:22:00', 'yyyy-mm-dd hh24:mi:ss') and
 to_date('2023-03-02 00:35:00', 'yyyy-mm-dd hh24:mi:ss')
 group by sql_id, event, blocking_session
 order by 1;
（非全局）BLOCKING_INST_ID--被阻塞者, blocking_session--阻塞者
select count(*), sql_id, event, BLOCKING_INST_ID, blocking_session
 from v$active_session_history
 where sample_time between
 to_date('2023-03-02 00:20:00', 'yyyy-mm-dd hh24:mi:ss') and
 to_date('2023-03-02 00:35:00', 'yyyy-mm-dd hh24:mi:ss')
 group by sql_id, event, BLOCKING_INST_ID, blocking_session
 order by 1;
b.现在我们已经得到两个关键信息：sql_id与阻塞事件,首先根据sql_id我们可以再进一步使用此视图，实际中可以多调整几个较小的时间段，以突出最有代表的信息
select count(*),
 session_id,
 session_serial#,
 sql_id,
 event,
 BLOCKING_INST_ID,
 blocking_session
 from v$active_session_history
 where sample_time between
 to_date('2023-03-02 00:24:00', 'yyyy-mm-dd hh24:mi:ss') and
 to_date('2023-03-02 00:25:00', 'yyyy-mm-dd hh24:mi:ss')
 and sql_id = '1xfbtdvu3xb67'
 group by session_id,
 session_serial#,
 sql_id,
 event,
 BLOCKING_INST_ID,
 blocking_session
 order by 3;
c.加入等待事件后更清晰
select count(*),
 session_id,
 sql_id,
 event,
 BLOCKING_INST_ID,
 blocking_session
 from v$active_session_history
 where sample_time between
 to_date('2023-03-02 00:25:00', 'yyyy-mm-dd hh24:mi:ss') and
 to_date('2023-03-02 00:35:00', 'yyyy-mm-dd hh24:mi:ss')
 and event = 'library cache lock'
 and sql_id = '1j47z0mc6k02b'
 group by session_id, sql_id, event, BLOCKING_INST_ID, blocking_session
 order by 1;
结论：可以看出大量并发等待，最终是发现有什么阻塞了此SQL语句

结合我们的AWR报告

当然也要结合我们的AWR报告：（两份为同时间段，上一份为有争用，下一份为正常情况，报告太长，只截取了关键点）

关键点

最后关键点a:下面报告里的sql_id与事件与v$active_session_history里查出来的结果相同，进一步证明事件与此SQL的关联性。

• 总结时间：

我们根据SQL_ID找到相应的SQL语句，从而找到对应的TABLE，最终对应到两张分区表，分别为：AA_BBB_CCCC_DDDD_OUT，AA_BBB_CCCC_DDDD_IN。

在对开发进行严刑拷打逼问后（如果开发小哥不松口怎么办？下节预告：可以直接查询时间段的DDL语句执行情况），终于告诉我当天晚上时间点上对这两张表做了大量新建分区表的操作，至此基本水落石出。

#根据dba_objects确定创建时间是否匹配
select owner,
 object_name,
 object_type,
 to_char(created, 'yyyy-mm-dd hh24:mi:ss')
 from dba_objects
 where object_name = 'AA_BBB_CCCC_DDDD_OUT'
 and created > to_date('2023-03-01', 'yyyy-mm-dd')
 order by 4;
 select owner,
 object_name,
 object_type,
 to_char(created, 'yyyy-mm-dd hh24:mi:ss')
 from dba_objects
 where object_name = 'AA_BBB_CCCC_DDDD_IN'
 and created > to_date('2023-03-01', 'yyyy-mm-dd')
 order by 4;

最后关键点b:我一定要记住，应该最先查看OSWatch的数据，排除OS的问题。至于OSW怎么部署，运行和查看以后章节再补充。同时也得查看database的alert.log日志，有惊喜╰(°▽°)╯