rac上的sequence

昨天和网友探讨，RAC中的SEQUENCE，awr报告如下，摘取关键部分：

在RAC环境中，序列的Cache问题可能会对性能有着决定性的影响，缺省的序列Cache值为20，这对RAC环境远远不够。

如果存在序列号使用的竞争，就可能在数据库中看到明显的队列等待：

enq: SQ - contention

在RAC情况下，可以将使用频繁的序列Cache值增加到10000，或者更高到50000，这些值在客户的环境中都有采用。

这是RAC设置和RAC使用的基本常识，不可或忘。

在以下测试中，可以显示Cache序列对于性能的影响：

RAC两个会话分别处于不同node同时并发循环间断去取4万个值  ：            

    nocache：           　　  2100s

    cache ＝1000：　　       55s

差别却是好大。

单Instance数据库单会话循环不间断去1-4万个值  测试（在家里笔记本上测试结果）过程如下：

    nocache：             37.7s          10000　　　

    cache ：20            4.31s          10000

    cache ：100         2.92s           10000

    cache ：1000       5.56s          40000

    nocache：             97.7s         40000

基本上cache 大于20的时候性能基本可以接受，最好设置100以上，

nocache的时候性能确实很差，最大相差20倍．

排序参数：oracle默认是NOORDER，如果设置为ORDER；在单实例环境没有影响，在RAC环境此时，多实例实际缓存相同的序列，此时在多个实例 并发取该序列的时候，会有短暂的资源竞争来在多实例之间进行同步。因此性能相比noorder要差，所以RAC环境非必须的情况下不要使用ORDER，尤其要避免NOCACHE ORDER组合；

在某些版本中存在BUG，会导致过度的 enq : SQ 竞争。

如在Oracle Database 11g中存在 IDGEN$ 序列 cache 设置过小问题，可能导致严重竞争，建议增加该序列的Cache值设置。

RAC环境下与sequence相关的锁

oracle为了在rac环境下为了sequence的一致性，使用了三种锁：row cache lock、SQ锁、SV锁。

row cache lock的目的是在sequence指定nocache的情况下调用sequence.nextval过程中保证序列的顺序性；

SQ锁是应用于指定了cache+noorder的情况下调用sequence.nextval过程中。

SV锁（dfs lock handel) 是调用sequence.nextval期间拥有的锁。前提是创建sequence时指定了cache 和order属性 (cache+order)。order参数的目的是为了在RAC上节点之间生成sequence的顺序得到保障。

创建sequence赋予的cache值较小时，有enq:sq-contention等待增加的趋势。

cache的缺省值是20.因此创建并发访问多的sequence时，cacheh值应取大一些。否则会发生enq:sq-contention等待事件。

rac上创建sequence时，如果指定了cache大小同时赋予了noorder属性，则各节点将会把不同范围的sequence值cache到内存上。

create sequence TX_SEND_SEQ_ACC

minvalue 1

maxvalue 999999999999999999999999999

start with 673560

increment by 1

cache 20;

RAC1取序列

SQL> select tx_send_seq_acc.nextval from dual;
   NEXTVAL
----------
    673560
SQL> select tx_send_seq_acc.nextval from dual;
   NEXTVAL
----------
    673561
RAC2取序列
SQL> select tx_send_seq_acc.nextval from dual;
   NEXTVAL
----------
    673580
SQL> select tx_send_seq_acc.nextval from dual;
   NEXTVAL
----------
    673581

若两个节点之间都必须通过依次递增方式使用sequence，必须赋予如下的order属性

如果是已赋予了cache+order属性的sequence，oracle使用SV锁进行同步。SV锁争用问题发生时的解决方法与sq锁的情况相同，就是将cache 值进行适当调整。

在RAC多节点环境下，Sequence的Cache属性对性能的影响很大。应该尽量赋予cache+noorder属性，并要给予足够的cache值。如果需要保障顺序，必须赋予cache+order属性。但这时为了保障顺序，实例之间需要不断的交换数据。因此性能稍差。

oracle RAC环境sequence不一致问题

Sequences in Oracle 10g RAC 
Just recently I got a call from a developer. He had a table with a primary key populated by a sequence, a timestamp column with the current date and some other columns. He had a specific set of data that, when ordered by the primary key had out of order timestamps. He was puzzled how this could be. This is a RAC database and the sequence was created with the default values.  Not only the sequences cache was the default of 20, but it was “noordered”.  Being “noordered” Oracle will not guarantee the order in which numbers are generated. 
Example of “noorder” sequence in 10g RAC:
Session 1 on node-A: nextval -> 101 
Session 2 on node-A: nextval -> 102 
Session 1 on node-B: nextval -> 121 
Session 1 on node-B: nextval -> 122 
Session 1 on node-A: nextval -> 103 
Session 1 on node-A: nextval -> 104 
The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesnt make “ordered” the default for sequences.  So I explained to the developer how sequences work in RAC and how each node has its own “cache”. 
We changed the sequence to “ordered” and increased the cache to 1000. Now selecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out. 
How does RAC synchronize sequences? 
In Oracle 10g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.  This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequences next value.  The wait event associated with this activity is recorded as "events in waitclass Other" when looked in gv$system_event. So much for event groups, it couldn't be more obscure. That view shows overall statistics for the session. 
However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1″ parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session. 
In a SQL_TRACE with waitevents (10046 trace) it will be a "DFS lock handle" but in AWR or statspack reports it will be “events in waitclass Other”. So much for consistency. 
How does that change our example? 
Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read) 
Session 2 on node-A: nextval -> 102 
Session 1 on node-B: nextval -> 103 (DFS Lock handle) 
Session 1 on node-B: nextval -> 104 
Session 1 on node-A: nextval -> 105 (DFS Lock handle) 
Session 1 on node-A: nextval -> 106 
(more selects) 
Session 1 on node-A: nextval -> 998 
Session 1 on node-B: nextval -> 999 (DFS Lock handle) 
Session 1 on node-B: nextval -> 1000 (CR read) 
The cache size also has some RAC synchronization implications. When the cached entries for the sequence are exhausted, the sequence object needs to be updated. This usually causes a remote CR (current read) over the interconnect for the block that has the specific sequence object. So a bit more activity here. 
Test case: 
create sequence test_rac; 
declare 
  dummy number; 
begin 
  for i in 1..50000 loop 
    select test_rac.nextval into dummy from dual; 
  end loop; 
end; 
/ 
Results: 
50 000 loops with cache = 20 (default) 
1 node = 5 seconds 
2 nodes at same time = 14 seconds 
2 nodes at same time ordered = 30 seconds 
50 000 loops with cache = 1000 
1 node = 1.5 seconds 
2 nodes at same time = 1.8 seconds 
2 nodes at same time ordered = 20 seconds 
With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes 
The conclusion 
By default sequences in 10g RAC are created without ordering. Beware of using applications that rely on sequences to be ordered and using it in a RAC environment.  Consider changing all user sequences to “ordered” as a precaution and increasing the cache size.  The default cache value is still very low and even not-ordered sequences will cause contention in a highly-active sequence even in non-RAC and causing an additional block exchange every 20 values in RAC. 
For high volume insert operations where ordering is not performed on the value returned from the sequence, consider leaving the sequence “noordered” but increasing the cache size significantly. 
Either way, the sequence parameters should be reviewed, as chances are, the defaults are not what you need.  I remember reading somewhere that in Oracle 9i the “ordered” clause in RAC was equivalent to “nochache”.  I cant imagine how bad that would be in concurrent selects from the same sequence.  It would be interesting if someone running 9i RAC performs the test case and I would appreciate if you post the results in the comments.

下面是公司环境，看到对于频繁更新的表都是cache很大，并且都是默认的noorder，在rac中，cache+noorder

Select 'create sequence ' || Sequence_Name || ' minvalue ' || Min_Value || ' maxvalue ' || Max_Value || ' start with ' ||
Last_Number || ' increment by ' || Increment_By || ' cache ' || Cache_Size || ' ;'
From Dba_Sequences;
create sequence SEQ_CMS_ACCESSORY minvalue 1 maxvalue 999999999999999 start with 1 increment by 1 cache 10000 ;
create sequence SEQ_CMS_CHANNEL minvalue 1 maxvalue 999999999999999 start with 100606 increment by 1 cache 10000 ;
create sequence SEQ_CMS_IMAGE minvalue 1 maxvalue 999999999999999 start with 260430 increment by 1 cache 10000 ;
create sequence SEQ_CMS_INFO minvalue 1 maxvalue 999999999999999 start with 671134 increment by 1 cache 10000 ;
create sequence SEQ_CMS_INFO_CHANNEL_LINK minvalue 1 maxvalue 999999999999999 start with 210007 increment by 1 cache 100
00 ;
create sequence SEQ_CMS_INFO_PROP minvalue 1 maxvalue 999999999999999 start with 60001 increment by 1 cache 10000 ;
create sequence SEQ_CMS_INFO_PROP_CONFIG minvalue 1 maxvalue 999999999999999 start with 50003 increment by 1 cache 10000
 ;
create sequence SEQ_CMS_INFO_USER_LINK minvalue 1 maxvalue 999999999999999 start with 460003 increment by 1 cache 10000
;
create sequence SEQ_CMS_LONG_TEXT minvalue 1 maxvalue 999999999999999 start with 681286 increment by 1 cache 10000 ;
create sequence SEQ_CMS_TEMPLATE minvalue 1 maxvalue 999999999999999 start with 20187 increment by 1 cache 10000 ;
create sequence SEQ_CMS_TEMPLATE_VIEW_VERSION minvalue 1 maxvalue 999999999999999 start with 44 increment by 1 cache 100
00 ;
create sequence SEQ_CMS_WEBSITE minvalue 1 maxvalue 999999999999999 start with 40001 increment by 1 cache 10000 ;
create sequence SEQ_CMS_WEBSITE_DOMAIN minvalue 1 maxvalue 999999999999999 start with 100 increment by 1 cache 10000 ;
create sequence SEQ_SCH_DISTRIBUTE_TASK_EXEC_C minvalue 1 maxvalue 999999999999999 start with 181 increment by 1 cache 2