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）