[Oracle]记一次由sequence引发的enq sv-contention等待事件

数据库版本:11.2.0.4 RAC
(1)问题现象
从EM里面可以看到,在23号早上8:45~8:55时,数据库等待会话暴增,大约到了80个会话。通过查看EM的SQL信息,发现等待产生于SQL语句

select TIMEKEYID.nextval from dual

image

image

 

(二)问题追踪
获取AWR报告观察,在TOP事件中,排名第二的enq:SV-contention

image

再去查看AWR报告,发现该语句执行频率非常的高,在8:00~9:00期间执行了51万多次。

image

从执行的语句可以看出,使用到的数据库对象是一个sequence,查询可看到该sequence的语法:

CREATE SEQUENCE MODMGR.TIMEKEYID
  START WITH 1000
  MAXVALUE 999
  MINVALUE 0
  CYCLE
  CACHE 100
  ORDER;

(1)定位哪些程序执行该SQL

select    to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') timekey,
          ash.session_id,
          ash."SESSION_SERIAL#",
          ash."MODULE"
          --count(*) as sql_count
from      dba_hist_active_sess_history  ash
where     ash.instance_number = 1
and       ash."SQL_ID" = '6ac0x1yudr8gq'
and       ash.sample_time between to_date('2018-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss') 
          and to_date('2018-12-23 09:00:00','yyyy-mm-dd hh24:mi:ss')
group by  to_char(sample_time,'yyyy-mm-dd hh24:mi:ss'),
          ash.session_id,
          ash."SESSION_SERIAL#",
          ash."MODULE"
order by  timekey;

image

(2)定位该语句的执行频率

select    to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') timekey,
          --ash.session_id,
          --ash."SESSION_SERIAL#",
          --ash."MODULE"
          count(*) as sql_count
from      dba_hist_active_sess_history  ash
where     ash.instance_number = 1
and       ash."SQL_ID" = '6ac0x1yudr8gq'
and       ash.sample_time between to_date('2018-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss') 
          and to_date('2018-12-23 09:00:00','yyyy-mm-dd hh24:mi:ss')
group by  to_char(sample_time,'yyyy-mm-dd hh24:mi:ss')
          --ash.session_id,
          --ash."SESSION_SERIAL#",
          --ash."MODULE"
order by  timekey;

image

(3)再把时间扩长一些,查看最近4天的该sql捕获记录,发现其它时间段该sequence的使用并不是如此频繁,真正出问题是在大约23日8:49

select    sample_time,
          ash.session_id,
          ash."SESSION_SERIAL#",
          ash."MODULE",
          ash.event
from      dba_hist_active_sess_history  ash
where     ash.instance_number = 1
and       ash."SQL_ID" = '6ac0x1yudr8gq'
and       ash.sample_time between to_date('2018-12-20 00:00:00','yyyy-mm-dd hh24:mi:ss') 
          and to_date('2018-12-24 00:00:00','yyyy-mm-dd hh24:mi:ss')
order by sample_time;

image

至此可以得出结论:
程序BidmMES在早上8:49产生了大量的“select TIMEKEYID.nextval from dual”语句,导致缓存的100个sequcence快速使用完,缓存使用完后,数据库实例会为其分配新的缓存,异常就发生在分配缓存的时候,Oracle会更新sequence的字典信息,频繁的数据字典更新会导致要使用该sequence的session产生enq:SV-contention等待。

(三)解决方案

如果确认业务没问题,那么需要修改序列的最大值为9999和cache值为1000

alter sequence modmgr.TIMEKEYID maxvalue 9999 cache 1000;

另外,需要考虑,业务上是采用3位的sequence来与其它字符做连接,如果需要保持业务一致,需要截取数字。

(四)案例重现
(1)创建sequence

CREATE SEQUENCE b7dba.seq_test
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 0
  CYCLE
  CACHE 10
  ORDER;

(2)创建一个plsql来消耗seq_test

create or replace procedure p_seq_test is
  seq_value    number ;begin
  --for seq in 1..50 loop
    select seq_test.nextval into seq_value from dual;
  --end loop;end p_seq_test;

(3)创建400个job来调用该pl/sql

create or replace procedure create_more_job is
  v_counter    number;begin
  for v_counter in 1..400 loop
    declare
      job1 number;
    begin
      sys.dbms_job.submit(job => job1,
                          what => 'p_seq_test;',
                          next_date => sysdate,
                          interval => 'sysdate + 1/(1440*60)'    --每隔1s执行一次                         
                          );
    commit;
    end;
  end loop;end create_more_job;

(4)通过修改cache来查看等待
alter sequence b7dba.seq_test cache {cache数量};
(4.1)no cacahe

drop  SEQUENCE b7dba.seq_test;CREATE SEQUENCE b7dba.seq_test
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 0
  CYCLE
  NOCACHE
  ORDER;

(4.2)cache = 2

drop  SEQUENCE b7dba.seq_test;
CREATE SEQUENCE b7dba.seq_test
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 0
  CYCLE
  CACHE 2
  ORDER;

(4.3)cache = 10

alter sequence b7dba.seq_test cache 10;

(4.4)cache = 100

alter sequence b7dba.seq_test cache 100;

(4.5)cache = 1000

alter sequence b7dba.seq_test cache 1000;

image

【完】

posted @ 2019-02-23 17:03  gegeman  阅读(1912)  评论(2编辑  收藏  举报