enq: SQ - contention" waits in RAC

SQL> l
  1* select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
SQL> /
Enter value for event: enq: SQ - contention
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'enq: SQ - contention'

NAME                           PARAMETER1      PARAMETER2      PARAMETER3
------------------------------ --------------- --------------- ---------------
enq: SQ - contention           name|mode       object #        0

SQL>

原因:

最常见的原因是一个或多个序列被大量使用，并且这些序列的高速缓存大小可能不足以满足RAC
THe most common cause is that one or more sequences are getting used heavily, and the cache size for those sequences may not be adequate for RAC

解决：
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.
从系统状态转储，挂起分析输出，AWR或ASH报告中查找引起"enq：SQ  - 争用"的序列。

2) Issue "show sequence <sequence name>" or "SELECT SEQUENCE_NAME, CACHE_SIZE, ORDER_FLAG FROM USER_SEQUENCES;" after connecting to the problem database

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.
如果序列没有用CACHE和NOORDER选项定义，则检查问题序列的属性是否可以更改为CACHE和NOORDER选项。
如果应用程序要求不能使用CACHE和NOORDER选项来定义问题序列，那么应用程序开发将最小化该序列的使用。

4) 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 issuing
如果使用CACHE和NOORDER选项定义了序列，并且如果高速缓存大小为20，则默认高速缓存大小（或像100这样的小数值）通过发出将高速缓存大小增加到10000
ALTER SEQUENCE <cache name> CACHE 10000;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2149628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2149628/