Oracle-活动会话数剧增

1. 现象

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

2. 分析

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

01 02 03 04 05 06 07 08 09 10 11

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 分析语句执行过程

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

-- 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会话的历史执行情况

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20

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功能模块，分割大事务成多个小事务，减少阻塞。