大量enq: SQ - contention 等待导致系统卡顿

本文主要是介绍大量enq: SQ - contention 等待导致系统卡顿，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Symptoms

上午收到邮件报警，提示有异常等待“log file sync”

等待事件邮件监控请参考

oracle常用监控脚本（纯干货，没有EMCC,ZABBIX也不怕）_oracle 监控及日常处理脚本-CSDN博客

查看OEM也能看到异常的波峰

OEM搭建可以参看这篇文章

EMCC13.5安装配置手册（详细版）-CSDN博客

登陆到主机查看等待事件，发现有超多的“enq: SQ - contention”

OEM下探也能看到是“enq: SQ - contention”等待

同时在OEM中可以看到影响最大的sql如下

select Object_SEQ.NEXTVAL from dual;

Cause

问题指向等待事件enq: SQ - contention，那这个等待如何引起的？

1. 等待事件背景：

Oracle 数据库使用序列来生成唯一的整数值，通常用于生成主键值。为了确保序列生成的唯一性和顺序性，Oracle 使用 SQ 锁来控制对序列对象的并发访问。
当多个会话试图同时访问相同的序列对象并请求下一个序列值时，如果序列无法立即提供下一个值，后续会话会进入 enq: SQ - contention 等待状态，直到持有锁的会话完成序列生成并释放锁。

2. 触发条件：

此等待事件通常在高并发环境中出现，特别是在多个会话频繁请求相同的序列对象时。这会导致竞争激烈，从而出现锁争用。
常见的触发场景包括：大量并发的插入操作使用相同的序列生成主键，或者某些情况下序列的缓存大小设置过小，导致频繁的序列生成和锁定。

3. 影响：

enq: SQ - contention 等待事件会导致数据库性能下降，特别是当大量并发会话长时间等待此锁时，整个应用程序的响应时间可能会受到影响。

了解了这些我们就可以知道应该是大量的插入并发，而且都在请求同一个sequence，导致争用，数据库中出现大量enq SQ的等待，造成系统的卡顿。由于该异常等待并不常见，我在wait event的监控脚本中并未添加，所以报出的是log file sync，其实也是系统卡顿的一种表现。

select Object_SEQ.NEXTVAL from dual;

Solution

知道了问题的原因，解决起来就相对简单一些，一般争用都是因为sequence的cache不够大，导致查询next指速度不够快，在OLTP系统中建议cache值要大一些

查询问题sequence的cache值和order

SQL> SELECT SEQUENCE_NAME, CACHE_SIZE, ORDER_FLAG FROM DBA_SEQUENCES where SEQUENCE_NAME='OBJECT_SEQ';SEQUENCE_NAME                  CACHE_SIZE O------------------------------ ---------- -OBJECT_SEQ                           20 N

如果sequence的默认cache是20，在高并发的OLTP系统中，需要改大一些，官方建议增加到10000。这里一条sql拉出该用户下所有的sequence修改sql，修改后观察一段时间，一切正常。

SELECT 'ALTER SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' CACHE 10000;' FROM DBA_SEQUENCES where SEQUENCE_OWNER='USERNAME';

参考官方文档也贴在这里

RAC and Sequences (Doc ID 853652.1)

High "enq: SQ - contention" waits in RAC (Doc ID 2156730.1)

1) Find out the sequence that is causing the "enq: SQ - contention" either from the system state dump, hang analyze output, AWR, or ASH report.On AWR report, for example, look at the sql that causes most elapsed time and look for a sql that uses cache.In the AWR report that showed the top wait events shown in the above "SYMPTOMS" section , The sql statement, "Select SUPPLIER_SEQ.NEXTVAL from dual", is the sql that was responsible for most elapsed time. 2) Issue "show sequence <sequence name>" or "SELECT SEQUENCE_NAME, CACHE_SIZE, ORDER_FLAG FROM USER_SEQUENCES;" after connecting to the problem databaseFor example, if the sequence name is SUPPLIER_SEQ, issue "show sequence SUPPLIER_SEQ" or "SELECT SEQUENCE_NAME, CACHE_SIZE, ORDER_FLAG FROM USER_SEQUENCES;"3) If the sequence is NOT defined with CACHE and NOORDER option, the check if the attribute for the problem sequence can be changed to CACHE and NOORDER option. If the application requires that the problem sequence must NOT be defined using CACHE and NOORDER option, then work with the application development to minimize the use of that sequence.For more information, refer to the Document 853652.1 RAC and Sequences4) If the sequence is defined with CACHE and NOORDER option and if the cache size is 20, the default cache size, (or a small value like 100), increase the cache size to 10000 by issuingALTER SEQUENCE <sequence name> CACHE 10000;For example, if the sequence name is SUPPLIER_SEQ, issueALTER SEQUENCE SUPPLIER_SEQ CACHE 10000;