enq：SQ-contention（SQ鎖）測試

oracle為了管理Sequence使用了以下三種鎖:
row cache lock :在調(diào)用Sequnece.nextval過程中.將數(shù)據(jù)字典信息進(jìn)行物理修改時獲取.賦予NOCACHE屬性的Sequence上發(fā)生。
enq：SQ-contention（SQ鎖）：在內(nèi)存上緩存（cache)的范圍內(nèi).調(diào)用 Sequence.nextval 期間擁有此鎖.賦予了CACHE厲性的Sequence上發(fā)生。
DFS lockhandle(SV鎖)：RAC上節(jié)點之間序得到保陣的悄況下.調(diào)用Sequence.nextval期間擁有賦予了CACHE+ORDER厲性的Sequence上發(fā)生。
通過v$lock_type可以看到關(guān)于該鎖的描述：

select * from v$lock_type a where a.TYPE in ('SQ','SV')

TYPE                          : SQ
NAME                          : Sequence Cache
ID1_TAG                       : object #
ID2_TAG                       : 0
IS_USER                       : NO
DESCRIPTION                   : Lock to ensure that only one process can replenish the sequence cache
-----------------
TYPE                          : SV
NAME                          : Sequence Ordering
ID1_TAG                       : object #
ID2_TAG                       : 0
IS_USER                       : NO
DESCRIPTION                   : Lock to ensure ordered sequence allocation in RAC mode

賦予了 CACHE 屬性的Sequence調(diào)用nextval期間，應(yīng)該以SSX模式獲得SQ鎖。許多會話同時為了獲取SQ鎖而發(fā)生爭用過程中，若發(fā)生爭用，則等持 enq:SQ-contention事件。enq:SQ-contention事件的P2值是 Sequence 的OBJECT ID。因此，若利用P2值與DBA_OBJECTS的結(jié)合，就可以知道對哪個Sequence發(fā)生了等待現(xiàn)象。

創(chuàng)建Sequence賦予的CACHE值較小時，有enq:SQ-contention等待增加的趨勢。CACHE值較小時，內(nèi)存上事先CACHE的值很快被耗盡，這時需要將數(shù)據(jù)字典信息物理修改后，再次執(zhí)行CACHE的動作。在此期間，因為一直擁有SQ鎖，相關(guān)的enq:SQ-contention 事件的等待時間也會延長；很不幸的是在創(chuàng)建Sequence時，將CACHE值的缺省值設(shè)定為較小的20。因此創(chuàng)建使用量多的Sequence時，CACHE值應(yīng)該取1000以上的較大值。

測試：

1.創(chuàng)建seq:
drop sequence app.info_seq;
create sequence app.info_seq
  start with 1
  maxvalue 999999999999999999999999999
  minvalue 1
  nocycle
  order;
2.創(chuàng)建過程
create or replace procedure pseq(a number) as
next_sql number;
begin
for x in 1..a loop
    execute immediate 'select app.info_seq.nextval from dual' into next_sql;
end loop;
end;
/
3.在session 1、sesseion2同時執(zhí)行：
exec pseq(400000)
4.等待事件：
SQL> select a.EVENT, a.SID, a.P1, a.P2  from v$session a where a.WAIT_CLASS# <> 6;
EVENT                                  SID         P1         P2
------------------------------- ---------- ---------- ----------
log file parallel write                 11          2        144
enq: SQ - contention                    36 1397817350      65009
enq: SQ - contention                    37 1397817350      65009
SQL*Net message to client               66 1413697536          1
SQL> select chr(bitand(p1, -16777216) / 16777215) ||
  2         chr(bitand(p1, 16711680) / 65535) "Lock",
  3         bitand(p1, 65535) "Mode"
  4    from v$session
  5   where event = 'enq: SQ - contention';
Lock           Mode
-------- ----------
SQ                6
SQ                6
Mode Value     Description 
1     Null mode 
2     Sub-Share 
3     Sub-Exclusive 
4     Share 
5     Share/Sub-Exclusive 
6     Exclusive
SQL> select d.owner,d.object_name,d.object_type  from dba_objects d where object_id  in (
  2  select p2  from v$session a where a.WAIT_CLASS# <> 6 and event = 'enq: SQ - contention');
OWNER    OBJECT_NAME     OBJECT_TYPE
-------- --------------- -------------------
APP      INFO_SEQ        SEQUENCE

另外，偶爾一次性同時創(chuàng)建許多會話時，有時會發(fā)生enq:SQ-contention等待事件。其理由是V$SESSION.AUDSID（auditingsessionid）列值是利用Sequence創(chuàng)建的。Oracle在創(chuàng)建新的會話后，利用名為SYS.AUDSES$的Sequence的nextval，創(chuàng)建AUDSID值。SYS.AUDSES$Sequence的CACHE大小的缺省值設(shè)定為20。許多會話同時連接時，可以將SYS.AUDSES$Sequence的CACHE大小擴(kuò)大至1000，以此可以解決enq:SQ-contention等待問題。10g下默認(rèn)20,11g下默認(rèn)為10000。
RAC上創(chuàng)建Sequence時，在賦予了CACHE屬性的狀態(tài)下，若沒有賦予ORDER屬性，則各節(jié)點將會把不同范圍的Sequence值CACHE到內(nèi)存上。比如，擁有兩個節(jié)點的RAC環(huán)境下，創(chuàng)建CACHE值為100的Sequence時，1號節(jié)點使用1-100.2號節(jié)點使用101-200。若兩個節(jié)點之間都通過遞增方式使用quence，必須賦予如下ORDER屬性。

SQL> create sequence ordereQuence cacke 100 order;

如果是已賦予了CACHE+ORDER屬性的Sequence,Oracle使用SV鎖進(jìn)行行同步。即，對賦予了ORDER屬性的Sequence調(diào)用nextval時，應(yīng)該以SSX模式擁有SV鎖。在獲取SV鎖過程中，如果發(fā)生爭用時，不是等持rowcachelock事件或enq:SQ-contention事件，而是等待名為DFSlockhandle事件。正因如此，V$EVENT_NAME視圖上不存在類似enq:SQ-contention的事件。DFSIockhandle事件是在OPS或RAC環(huán)境下，除了高速緩沖區(qū)同步之外，還有行高速緩沖區(qū)或庫高速緩沖區(qū)的為了同步獲取鎖的過程中等待的事件。若要保降多個節(jié)點之間Sequence順序，應(yīng)該在全局范圍內(nèi)獲得鎖，在此過程中會發(fā)生DFSlockhandle等待。在獲取SV鎖的過程屮發(fā)生的DFSlockhandle等待事件的PI、P2值與enq:SQ-contertion等待事件相同P1=mode+namespace、P2=object#)，因此從PI值能確認(rèn)是否是SV鎖，通過P2值可以確認(rèn)對哪些Sequenced生過等待。SV鎖爭用問題發(fā)生時的解決方法與SQ鎖的怙況相同，就是對CACHE值進(jìn)行適當(dāng)調(diào)整，這也是唯一的方法。
RAC等多節(jié)點環(huán)境下，Sequence的CACHE值給性能帶來的影響比單節(jié)點更加嚴(yán)重。因此，盡量賦予CACHE+NOORDER屬性，并要給予足夠大的CACHE值。如果要求保持順序，必須賦予CACHE+ORDER屬性。但這時為了保障順序，實例之間不斷發(fā)生數(shù)據(jù)的交換。因此，賦予了NOORODER屬性的時候相對性能稍差。

根據(jù)創(chuàng)建Sequence時賦予的屬性，等侍事件的結(jié)果如下：

NOCACHE： row cacke lock
CACHE+NOORDER： enQ :SQ-contention
CACHE+ORDER(RAC): DTS lock Handle

如果序列作為索引中的一部分，高并發(fā)環(huán)境會導(dǎo)致索引分裂問題的發(fā)生，常見的等待事件為:enq: TX - index contention