Sequence – SV contention

Waits for cached sequence when sequence is used from multiple instance

10046 trace: 
WAIT #139899759861648: nam='enq: SV -  contention' ela= 4276 type|mode=1398145029 
id1=88851 id2=0 obj#=-1 tim=1385221394056801

SQL> select owner, object_name, object_type from  dba_objects where object_id=88851;
OWNER                   OBJECT_NAME            OBJECT_TYPE
----------------------- ---------------------- ------------------
SCOTT                   T1_SEQ                 SEQUENCE

Waits for UNCACHED sequences when used from multiple instances

WAIT #140557198270352: nam='row cache lock' ela= 1227 cache id=13 mode=0 request=5 obj#=-1 tim=1384249119100670
SQL> select type, parameter from v$rowcache where cache#=13;
TYPE        PARAMETER
----------- --------------------------------
PARENT        dc_sequences
..
WAIT #140557198260504: nam='gc cr block 2-way' ela= 529 p1=1 p2=667 p3=1 obj#=74 tim=1384249119101498
WAIT #140557198253360: nam='gc current block 2-way' ela= 687 p1=1 p2=667 p3=33554433 obj#=74 tim=1384249119102947

SQL> SELECT segment_name, segment_type, block_id, blocks
        FROM   dba_extents
        WHERE file_id = &file_no AND ( &block_value BETWEEN block_id 
         AND ( block_id + blocks -1 ) );
Enter value for file_no: 1
Enter value for block_value: 667
SEGMENT_NAME   SEGMENT_TYPE         BLOCK_ID     BLOCKS
------------- -------------------- ---------- ----------
SEQ$          TABLE                      664          8a

• For each seq.nextval we are waiting 1.2 ms for a row cache lock and 0.6 ms for a CR request on SEQ$ and again 0.6 ms for getting a the most CURRENT block from SEQ$.
• The complete wait time using a UNCACHED sequence is about 2.4 ms for each seq.nextval operation
• UNCACHED sequences triggers a library cache lock where CACHED sequences triggers a SV lock

Performance test results

Test Env : 11.2.0.4 3-node RAC 3-node Rac cluster / 10 Threads each  inserting 1000 rows - 10.000 rows in summary 
Test                                    Command                             Runtime    TPS
Using sequence with ORDER NOCACHE       $ java UCPDemo grac4 10 1000 -seq       46s    212
Using sequence with ORDER CACHE 20      $ java UCPDemo grac4 10 1000 -seq       33s    297
Using sequence with ORDER CACHE 1000    $ java UCPDemo grac4 10 1000 -seq       32s    312      
Using programming-methods to create     $ java UCPDemo grac4 10 1000 -noseq     11s    875

•  Always try to use application based sequences if possible ( about 4x faster than ORDER NOCACHE )
• Use CACHE attribute if possible and avoid ORDER NOCACHE combination
•  For heavily used sequences use 1000 for its cache size – for infrequently use sequences use 20 for cache size parameter

AWR report for NOCACHE ORDER sequence sample

    ---------------------------------------------------   ----------------------   ------------------------------   --------------------------
  I#   Class      Event                      Waits %Timeouts        Total(s) Avg(ms) %DB time        Avg      Min      Max  Std Dev  Cnt
----   ---------- ------------------------- ------------ ---------   ------------- ------- --------   -------- -------- -------- -------- ----
   *   Concurrenc row cache lock                  9,572       0.0          368.76    38.5    82.70      38.26    35.73    42.92     4.04    3
                  DB CPU                            N/A       N/A           25.39     N/A     5.69                                          3
       Commit     log file sync                   9,968       0.0           24.44     2.5     5.48       2.42     2.07     2.69     0.32    3
       Other      row cache process              73,768       0.0            5.78     0.1     1.30       0.08     0.03     0.17     0.08    3
       Cluster    gc cr block busy                2,896       0.0            4.37     1.5     0.98       1.66     1.28     2.37     0.61    3
       Cluster    gc current block 2-way          6,603       0.0            4.08     0.6     0.91       0.62     0.60     0.64     0.02    3
       Cluster    gc current block 3-way          3,223       0.0            3.44     1.1     0.77       1.08     0.94     1.32     0.20    3
       Cluster    gc cr block 2-way               4,047       0.0            3.18     0.8     0.71       0.83     0.66     0.99     0.17    3
       Cluster    gc cr block 3-way               2,431       0.0            2.87     1.2     0.64       1.43     1.12     1.59     0.27    3
       Other      gcs log flush sync              3,310       0.0            1.87     0.6     0.42       0.53     0.49     0.59     0.05    3

AWR report for CACHE 1000 ORDER sequence sample

      ----------------------------------------  ----------------------   ------------------------------   -----------------------------------
  I#   Class      Event                           Waits %Timeouts        Total(s) Avg(ms) %DB time        Avg      Min      Max  Std Dev  Cnt
----   ---------- ----------------------------- ------------ ---------   ------------- ------- --------   -------- -------- -------- --------
   *   Other      enq: SV -  contention         9,953       0.0          197.99    19.9    80.68      21.22     7.87    28.11    11.56    3
       Commit     log file sync                10,005       0.0           24.83     2.5    10.12       2.41     2.04     3.09     0.59    3
                  DB CPU                          N/A       N/A           12.25     N/A     4.99                                          3
       Configurat enq: HW - contention            73       0.0             3.12    42.7     1.27      43.72    34.21    59.78    13.99    3
       Other      reliable message                 5       0.0             1.79   357.3     0.73     594.44     1.67  1779.91  1026.65    3
       Cluster    gc current block 3-way         250       0.0             1.58     6.3     0.64       5.18     1.91    11.56     5.52    3
       Concurrenc library cache lock              30      10.0             0.96    32.1     0.39      34.92    25.53    52.26    15.04    3
       Cluster    gc buffer busy acquire         118       0.0             0.94     8.0     0.38       8.64     3.69    13.70     5.01    3
       Cluster    gc current block 2-way         612       0.0             0.86     1.4     0.35       1.41     0.78     2.54     0.98    3
       Cluster    gc current block busy          161       0.0             0.81     5.0     0.33       4.96     3.92     5.66     0.92    3

•  Each update on a sequence requires a row cache lock ( Caching reduces this updates – but can lead to gaps if instance crashes )
•  if using ORDER and NOCACHE attribute every seq.nextval access triggers a row cache lock
• if using NOCACHE sequence row cache lock event could be  a major wait event
• If using   CACHE enq: SV –  contention becomes a major wait event