Oracle-活动会话数剧增

1. 现象

某天收到一数据库活动会话数剧增不降，业务系统部分功能缓慢。

2. 分析

2.1 分析活动会话数变化趋势

select 
 to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME
,count(*) cnt
from dba_hist_active_sess_history ash
where ash.instance_number=1
 and ash.wait_class <> 'Idle'  /* 非空闲回话 **/
 and ash.sample_time between sysdate -1/2 and sysdate
 group by SAMPLE_TIME
 having count(*) >80 
 order by SAMPLE_TIME
;

2.2 抽一个时间点分析等待事件及SQL执行情况

select
   to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME
   ,ash.sql_id
   ,ash.event
   ,count(*) CNT /* SQL的数量 **/
   ,TRUNC(SUM(TIME_WAITED) / 1000000,2) SECONDS_IN_WAIT /* SQL的等待时间 **/
   ,SUM(to_number(CAST(ash.sample_time AS DATE)-ash.sql_exec_start) *24*60*60 ) SECONDS_IN_EXECUTE /* SQL的执行时间 **/
from dba_hist_active_sess_history ash
where ash.instance_number=1
 and ash.wait_class <> 'Idle'
 and to_char(ash.sample_time,'YYYY-MM-DD HH24:MI:SS') = '2016-12-01 09:28:27'
group by
  ash.sample_time
 ,ash.sql_id
 ,ash.event
having count(*) > 5
order by SAMPLE_TIME,4
;

-- 发现174r8w7amsr17,83zyfck594vfk SQL执行时间较长，特别是174r8w7amsr17这条语句执行5+分钟

2.3 分析语句执行过程

-- 83zyfck594vfk 语句执行情况
col event for a32
col p1text for a18
col p2text for a18
col p3text for a18

select
  to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME
 ,ash.event
 ,ash.sql_id 
 ,ash.blocking_inst_id
 ,ash.blocking_session
 ,ash.blocking_session_serial#
 ,count(*) cnt
from dba_hist_active_sess_history ash 
where ash.instance_number=1
 and ash.wait_class <> 'idle'
 and ash.sql_id = '83zyfck594vfk'
 and to_char(ash.sample_time,'YYYY-MM-DD HH24:MI:SS') = '2016-12-01 09:28:27'
group by 
  sample_time
 ,ash.event
 ,ash.sql_id 
 ,ash.blocking_inst_id
 ,ash.blocking_session
 ,ash.blocking_session_serial#
order by sample_time,7
;

-- 发现session(1:4448,58047)阻塞了5个会话，下图

2.4 分析下4448，58047会话的历史执行情况

select 
 to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') sample_time
,to_char(ash.sql_exec_start,'YYYY-MM-DD HH24:MI:SS') SQL_START_TIME
,ash.instance_number inst_id
,ash.session_id sid
,ash.session_serial# serial 
,ash.blocking_inst_id b_inst_id
,ash.blocking_session b_sid
,ash.blocking_session_serial# b_serial
,ash.sql_id
,ash.event
,to_number(CAST(ash.sample_time AS DATE)-ash.sql_exec_start) *24*60*60 SECONDS_IN_EXECUTE
,ash.xid
from dba_hist_active_sess_history ash
where 
 and ash.instance_number=1
 and ash.session_id = 4448
 and ash.session_serial# = 58047
order by sample_time
;

-- session(1:4448,58047) 从2016-12-01 09:25:33 建立了一个事务 (xid = '02FB001B0000619A') 持续到 2016-12-01 09:47:49 , 至少22分钟里面执行 83zyfck594vfk 语句134次才提交,而每次执行时间挺小的，但整个事务执行时间过长，持续20多分钟时间。而且83zyfck594vfk 语句执行存在“BY LOCAL INDEX ROWID”回表或是“INDEX SKIP SCAN ”操作，而索引跳跃扫描的性能不好。需要优化。

2.5 分析174r8w7amsr17语句是被前面这类多个83zyfck594vfk SQL长时间事务阻塞

3. 处理方案

3.1 优化83zyfck594vfk语句的索引；

3.2 检查业务83zyfck594vfk功能模块，分割大事务成多个小事务，减少阻塞。