RAC环境下的sequence详解(原创)

标签: rac 环境 sequence | 发表时间:2014-04-29 15:46 | 作者:
出处:http://www.iteye.com

在RAC环境中,序列的Cache问题可能会对性能有着决定性的影响,缺省的序列Cache值为20,这对RAC环境远远不够。
如果存在序列号使用的竞争,就可能在数据库中看到明显的队列等待:
enq: SQ - contention
在RAC情况下,可以将使用频繁的序列Cache值增加到10000,或者更高到50000,这些值在客户的环境中都有采用。
这是RAC设置和RAC使用的基本常识,不可或忘。
在以下测试中,可以显示Cache序列对于性能的影响:
http://space.itpub.net/14941137/viewspace-629941
摘要如下:
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.

参考至:http://www.aixchina.net/home/space.php?uid=20260&do=blog&id=26752

              http://blog.csdn.net/zftang/article/details/6321513

              http://www.eygle.com/archives/2012/05/oracle_rac_sequence_cache.html

              http://wenku.baidu.com/link?url=GpJLv3Lrl6YxxnYMkZZlMcKa9z9LCaAVyXsp-tjizHlvUluY3jQbPaZBAMiNvw1md9_yZEIWCl9CD2JeakbM--u8ngBz7Bs27lfLSNgEPg_

              http://bbs.csdn.net/topics/390310984

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:[email protected]



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [rac 环境 sequence] 推荐:

RAC环境下的sequence详解(原创)

- - ITeye博客
在RAC环境中,序列的Cache问题可能会对性能有着决定性的影响,缺省的序列Cache值为20,这对RAC环境远远不够. 如果存在序列号使用的竞争,就可能在数据库中看到明显的队列等待:. 在RAC情况下,可以将使用频繁的序列Cache值增加到10000,或者更高到50000,这些值在客户的环境中都有采用.

Oracle RAC Failover 详解

- - CSDN博客数据库推荐文章
Oracle  RAC 同时具备HA(High Availiablity) 和LB(LoadBalance). 而其高可用性的基础就是 Failover(故障转移). 它指集群中任何一个节点的故障都不会影响用户的使用,连接到故障节点的用户会被自动转移到健康节点,从用户感受而言, 是感觉不到这种切换.

12c 使用sequence作为列默认值

- - 惜分飞
官方文档创建表语句部分说明. 在12c中,表支持默认列为sequence值,而且不用使用传统的触发器来实现该功能. 12c创建表使用默认sequence测试过程. SQL> create table t_xifenfei 2 ( 3 id number GENERATED ALWAYS as identity ( 4 start with 1 5 increment by 1 6 ), 7 name varchar2(200) 8 ); Table created.

Oracle 双机/RAC/Dataguard的区别

- - 数据库 - ITeye博客
  Data Guard 是Oracle的远程复制技术,它有物理和逻辑之分,但是总的来说,它需要在异地有一套独立的系统,这是两套硬件配置可以不同的系统,但是这两套系统的软件结构保持一致,包括软件的版本,目录存储结构,以及数据的同步(其实也不是实时同步的),这两套系统之间只要网络是通的就可以了,是一种异地容灾的解决方案.

Oracle RAC JDBC connection string - multitude - 博客园

- -
官方文档, 一如既往地冗长, 可靠. 这个第三方总结很简单, 看样子是第三方驱动厂商.

分布式系统中sequence num的生成

- hikerlive - 淘宝核心系统团队博客
 当前有很多分布式系统都采用了不同方法来生成squence num,其中UUID是比较费力气和费空间的方法.在分配squence num时候,其实为了达到数据的分布和均衡效果,是应该把squence num分配给client. 现在介绍两种其他生成squence num的方法:. 1、通过config server(这个是单独的管理元数据的服务器,不同系统叫法不同,有的叫master  server,有的叫root server)来协调生成.

配置oracle10g rac使用过的一些脚本

- - CSDN博客数据库推荐文章
#10.2.0.1修改内核. #hangcheck模块. 作者:u011538954 发表于2014-2-23 17:57:55 原文链接. 阅读:87 评论:0 查看评论.

RAC系统巡检过程详细解释

- - CSDN博客数据库推荐文章
二 模拟两个节点内联网不通,观察RAC会出现什么现象. 本小题会模拟RAC的私有网络不通现象,然后定位故障原因,最后排除故障. 1.首先RAC是一个非常健康的状态. 检查CRS进程状态(CRS  CSS  EVM). 检查OCR磁盘状态,没有问题. 检查vote disk状态. 0.     0    /dev/raw/raw2                      显示2号裸设备为表决磁盘.

Oracle RAC的VIP和SCAN IP - 学海无涯2020 - 博客园

- -
    我们都知道Oracle RAC中每个节点都有一个虚拟IP,简称VIP,与公网IP在同一个网段.     没有VIP时,Oracle客户端是靠“TCP/IP协议栈超时”来判断服务器故障. 而TCP/IP协议栈是作为OS Kernel的一部分来实现,不同的OS有不同的阀值,用户获悉数据库异常的时间完全取决于OS Kernel的实现,虽然有些OS允许修改这个阀值,但是会对其它程序产生未知影响.

因asm sga_target设置不当导致11gr2 rac无法正常启动

- - 惜分飞
2014年第一个故障排查和解决:同事反馈给我说solaris 11.2 两节点rac无法启动,让我帮忙看下. 通过分析是因为sga_target参数设置不合理导致asm无法正常启动. 2014-01-01 00:41:02.016 [ctssd(1483)]CRS-2408:The clock on host zwq-rpt1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.