Oracle Study之-- enq:SQ contention等待事件

Oracle Study之-- enq:SQ contention等待事件

通过AWR Report发现“enq:SQ contention”等待事件：

应用环境：

转自：Http://www.xuebuyuan.com/1027129.html 

enq:SQ contention/row cache lock/DFS lock handle(SV) 这三个等待事件都与Oracle 的Sequence 有关。

       oracle Sequence Cache 参数说明

       http://blog.csdn.net/xujinyang/article/details/6831361

       Oracle 常见的33个等待事件

       http://blog.csdn.net/xujinyang/article/details/6882035

Oracle为了管理sequence使用了以下三种锁

       （1）row cache lock

       在调用sequence.nextval过程中，将数据字典信息进行物理修改时获取，赋予了nocache属性的sequence上发生。

       （2）SQ锁 -- enq: SQ

       在内存上缓存(cache)范围内，调用sequence.nextval期间拥有此锁，赋予了cache+noorder 属性的sequence上发生。

       （3）SV锁 -- DFS lock handle  

       RAC上节点之间顺序得到保障的情况下，调用sequence.nextval期间获得，赋予了cache+order属性的sequence上发生。

     赋予了CACHE属性的sequence调用nextval期间，应该以SSX模式获得SQ锁，许多会话同时为了获取SQ锁而发生争用过程中，若发生争用，则等待enq:SQ-contention.

       enq:SQ-contention事件的P2值是sequence的object ID,因此，若利用P2值与DBA_OBJECTS的结合，就可以知道对哪个、 Sequence发生了等待对象。

     创建Sequence赋予的CACHE值较小时，有enq:SQ-contention等待增加的趋势，CACHE值较小，内存上事先CACHE的值很快被耗尽，这时需要将数据字典信息物理修改，再次执行CACHE的工作，在此期间，因为一直要拥有SQ锁，相应的Enq:SQ-contention事件的等待时间也会延长，很不幸的是，在创建Sequence时，将CACHE值的缺省值设定为较小20, 因此创建使用量最多的Sequence时，CACHE值应该取1000以上的较大值。

     偶而一次性同时创建许多会话，有时会发生enq:SQ-contention等待事件，其理由是V$SESSION.AUDSID(auditing sessionid) 列值是利用Sequence创建的，oracle在创建新的会话后，利用名为SYS.AUDSESS$的sequence的nextval创建AUDSID的值，SYS.AUDSESS$ Sequence的CACHE大小的缺省值设定为 20，许多会话同时连接，可以将SYS.AUDSESS$ sequence的CACHE大小扩大至1000，以此可以解决 enq:SQ-contention等待问题。

RAC上创建Sequence时，在赋予了CACHE属性的状态下:

       （1）若没有赋予ORDER属性，则各节点将会把不同范围的Sequence值CACHE到内存上，比如拥有两个节点的RAC环境下，创建CACHE值为100的 sequence时，1节点会使用1-100，2节点会使用101-200。 使用时从各自节点取sequence。

       （2）若两个节点之间会通过递增的使用sequence，必须赋予如下ORDER属性。

     sql>Create sequence ordered_sequence cache 100 order;

       在order 的情况下，2个节点取的sequence是递增的。 下文会有示例来说明这两种情况。

     如果已赋予CACHE+ORDER属性的sequence, oracle使用SV锁进行行同步，即，对赋予了ORDER属性的sequence调用nextval时，应该以SSX模式拥有SV锁，在获取SV锁过程中，若发生了争用，不是等待ROW CACHE或者是enq:SQ-contention,而是等待名为DFS lock handle事件，正因如此V$EVENT_NAME视图上不存在类似与"enq:SV-contention"

       DFS lock handle事件是在OPS或者RAC环境下，除了 高速缓冲区 同步之外，还有 行高速缓冲区 或者 库高速缓冲区 同步获取锁的过程中的等待事件。      若保证全局范围内获得锁，在此过程中会发生DFS look handle等待，在获取SV锁的过程中发生的DFS lock handle等待事件的P1,P2值与enq:SQ-contention等待事件相同(p1=mode+namespace,p2=object#).因此会从P1值能确认是否是SV锁，通过P2可以确认哪些是Sequence发生过等待.

       SV锁争用问题发生时的解决办法与SQ锁的情况相同，就是CACHE值进行适当的调整，这也是唯一的方法。  

测试1：NOORDER的Sequence

node1：

SQL> create sequence seq_noorder start with 1 increment by 1 cache 20 NOORDER;

Sequence created.

SQL> select seq_noorder.nextval from dual;

   NEXTVAL

----------

         1

SQL> /

   NEXTVAL

----------

         2

SQL> /

   NEXTVAL

----------

         3        

Node2：

SQL>  select seq_noorder.nextval from dual;

   NEXTVAL

----------

        21

SQL> /

   NEXTVAL

----------

        22

SQL> /

   NEXTVAL

----------

        23

node2上不是从4开始的，是从21开始的，因为node1已经cache了20个。

测试2： ORDER的Sequence

node1：

SQL> create sequence seq_order start with 1 increment by 1 cache 20 ORDER;

Sequence created.

SQL> select seq_order.nextval from dual;

NEXTVAL

----------

1

SQL> /

NEXTVAL

----------

2

SQL> /

NEXTVAL

----------

3

Node2：

SQL> select seq_order.nextval from dual;

NEXTVAL

----------

4

SQL> /

NEXTVAL

----------

5

SQL> /

NEXTVAL

----------

6

指定Order 之后，取的序列就是顺序的。

在下面的链接中讲到了RAC 之间序列同步:

       Sequences in Oracle 10g RAC

       http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/

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 sequence's 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.

小结：

     没有赋予CACHE属性时，不管ORDER属性是否或RAC环境是否，一直等待ROW CACHE事件，ROW CACHE LOCK是否可以在全局范围内使用的锁，单实例环境或多实例环境同时可以发生。

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

       在RAC等多节点环境下，sequence的CACHE值给性能带来的影响比单节点环境更严重，因此，尽量赋予CACHE+NOORDER属性，并要给与足够大的CACHE值。

       但是如果使用了Cache，如果此时DB 崩溃了，那么sequence会从cache 之后重新开始，在cache中没有使用的sequence 会被跳过。即sequence 不连续。 所以只有在多节点高峰并发量很大的情况且对连续性要求不高的情况下，才使用：noorder + cache

根据创建Sequence时赋予的属性，整理等待事件的结果如下：

       NOCACHE :  --> row cache lock

       CAHCE+NOORDER  -->  enq: SQ-contention（SQ lock）

       CACHE+ORDER(RAC):  --> DFS look handle（SV lock）