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;

enq: SQ - contention" waits in RAC

相關文章