大量慢SQL导致节点宕机的故障分析

故障背景：项目负责人找到我说2018-08-28 11:40:00-2018-08-28 11:45:00左右收到频繁告警短信 数据库节点1宕机，节点2 SESSION数一直在增加。这是一个数据仓库系统，正常情况下SESSION一般是90左右，现在将近500，而且还在增长

select *
  from dba_hist_active_sess_history
 where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') >
       '2018-08-28 11:40:00'
   and to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') <
       '2018-08-28 11:45:00'
   and instance_number = 1
       group by sql_id order by 1 desc

发现果然没有数据返回，说明这时候节点1已经无法不提供服务了

再次向接口人确认得知从2018-08-28 11:00:00就开始收到告警短信了……于是发出如下查询

select max(to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss')),
       min(to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss'))
  from dba_hist_active_sess_history
 where instance_number = 1
   and to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') >
       '2018-08-28 11:00:00'
   and to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') <
       '2018-08-28 11:45:00'

 '2018-08-28 11:00:09'  '2018-08-28 11:21:59'
说明节点一在2018-08-28 11:21:59的时候 就已经GG了

一、查一下这最后的20分钟里面节点1经历了什么？首先查SQL语句，有哪些执行时间很长的SQL语句

select count(*),sql_id
  from dba_hist_active_sess_history
 where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') >
       '2018-08-28 11:00:09'
   and to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') <
       '2018-08-28 11:21:59'
       and  instance_number = 1
       group by sql_id order by 1 desc

6903  5cj4kmp6aa9ux
5873  9zn6bkn0kcgv7
3032  cxqmbbffgdbhx
1374  22wxyvttg0xr3
1240  g1q9a31rmfhh9
1095  8bp70fca7gbuk
…………省略

发现节点1的SQL执行时间很过分，将近7w秒，20个小时。

（为什么我们采集时间是20分钟，能够采集到将近7000个snapshot？其实很容易理解，同一个SQL_ID执行了很多次……）

产生大批量的SQL"堵塞"的原因 一般是1.有锁，2.SQL本身的执行时间很长。

如果产生锁 一般也是holder的语句执行时间太长，导致后面大量的waiter在排队等待。我一般先排查SQL问题

查看SQL语句历史执行计划以及相关表信息

select * from table(dbms_xplan.display_awr('5cj4kmp6aa9ux'))
SQL_ID 5cj4kmp6aa9ux
--------------------
SELECT trd.ID          as id,
       trd.BILL_CODE   as billCode,