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