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

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

SYMPTOMS

RAC databases are performing slowly due to heavy "enq: SQ - contention" waits.

AWR and ASH reports show that "enq: SQ - contention" is the top wait.

The following is an example from the AWR report:

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
enq: SQ - contention 10,690,523 46.2K 4 63.9 Configuration
DB CPU 11.8K 16.3
enq: TX - index contention 439,578 9004.6 20 12.5 Concurrency
buffer busy waits 1,104,082 1507.4 1 2.1 Concurrency
db file sequential read 1,122,853 1419.5 1 2.0 User I/O
db file scattered read 29,548 779.6 26 1.1 User I/O
library cache: mutex X 2,242,811 769.2 0 1.1 Concurrency
latch: ges resource hash list 529,983 412 1 .6 Other
cursor: pin S 933,128 227 0 .3 Concurrency
direct path read 99,101 186.9 2 .3 User I/O

 

CAUSE

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
 

SOLUTION

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 database

For 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 Sequences

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 
ALTER SEQUENCE <cache name> CACHE 10000;

For example, if the sequence name is SUPPLIER_SEQ, issue 
ALTER SEQUENCE SUPPLIER_SEQ CACHE 10000;