序列设计之enq SQ - contention处理一则

某系统负责人反应系统很慢，收集持续报警短信。
查看数据库当前活动回话等待事件为enq: SQ – contention，当前活动session 200+ 
enq: SQ – contention是sequence相关的锁，在内存上缓存(cache)范围内，调用sequence.nextval期间拥有此锁。
如果创建sequence时，cache值太小（默认是20（单位是“个”）），在调用sequence.nextval，内存事先cache值会很快耗尽。这时需要将数据字典信息物理修改，再次执行cache的工作。
在此期间。SQL会一直拥有SQ锁，许多会话同时为了获取SQ锁而发生争用。对应的等待事件就是enq: SQ – contention。随着session的增长等待时间也会一直持续

 

 

其实到这里我们基本上可以定位问题，当然解决问题还是需要按部就班

收集对应时间段的awr和ash如下：

ASH:

通过ASH可以定位到产生这个等待事件的SQL如下：

 

insert into  t_ttt_pppppppddddinfo valus
  (OPERDATE,
   OPERSERIALNO,
   CHANNELCODE,
   SUBCHANNELCODE,
   CHANNELDATE,
   CHANNELSERNO,
   AGENTDATE,
   AGENTSERIALNO,
   PROLEVEL,
   PROTYPE,
   PRODESC,
   NOTE)
  select :OPERDATE,
         s_p2p_agentserialno.nextval as OPERSERIALNO,
         channelcode,
         channelcode,
         substr(channeldate, 1, 8),
         channelbusiseqno,
         :AGENTDATE,
         s_p2p_agentserialno.nextval,
         '1',
         '1',
         dealMsg,
         note
    from ttt_xxxxx t
   where t.dealStatus = '1'

 

 

 

1.查看活动session等待事件为enq: SQ – contention的p1,p2,p3

2.查看v$event_name看等待事件enq: SQ – contention p1,p2,p3含义------>P1: name|mode p2: object # p3: 0

3.根据p2值去dba_objects里定位到的对象是正是这个insert里使用到的序列

4.查看序列源码

由于序列cache太小（20），并发insert使用序列大造成的等待。因此创建使用量很大的sequence时，cache值应该设置在200以上。

 

我们常常说,开发人员有时候写出一条神一样的SQL会拖垮整个数据库性能。然而同样的，数据库对象的设计（表设计，索引，序列，视图等）如果前期没有考虑周全
上线之后，随着对象的数据增长、并发增加等可能会导致极其严重的性能问题。对于一条杀手SQL，我们只需要找出来并优化就可以立竿见影解决问题，但是一些数据库对象一旦出现问题就不能像优化SQL这么简单了，结果常常是牵一发而动全身。所以数据库对象设计的问题一定要扼杀在萌芽阶段。

本公司针对oracle数据库的SQL审核产品-SQL_FILTER。通过内置算法和规则，抓取一系列写法或者执行计划有问题的SQL以及不合理的数据库对象设计并给出详细优化建议。还可以针对数据库的不合理参数进行排查分析。监控数据库并排查以后可能出现问题的潜在风险SQL.

SQL_FILTER用于在应用代码的开发测试阶段将不合理的SQL和数据库对象设计做审核；并在上线之后对数据库整体进行分析，及时发现上线之后性能下降或者的低效的SQL；并可以对稳定的系统进行监控，帮助DBA及时发现系统隐患。

了解更多SQL_FILTER 请联系本人（微信/手机号：15652625652）