程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 【等待事件】序列等待事件總結（enq: SQ，enqsq

【等待事件】序列等待事件總結（enq: SQ，enqsq

編輯：Oracle教程

【等待事件】序列等待事件總結（enq: SQ，enqsq

【等待事件】序列等待事件總結（enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention）

1 BLOG文檔結構圖

2 前言部分

2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 序列等待事件總結

② enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention模擬

③ 序列的CACHE值性能測試

④ RAC中序列的ORDER和NOORDER測試

⑤ 序列等待的相關案例處理

Tips：

① 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。

② 文章中用到的所有代碼、相關軟件、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載，小麥苗的雲盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。

③ 若網頁文章代碼格式有錯亂，請下載pdf格式的文檔來閱讀。

④ 在本篇BLOG中，代碼輸出部分一般放在一行一列的表格中。其中，需要特別關注的地方我都用灰色背景和粉紅色字體來表示，比如在下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字體標注；對代碼或代碼輸出部分的注釋一般采用藍色字體表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- ------------------- ---------- ---------

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XLHRD_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

2.2 相關文章鏈接

有關隊列的更多介紹可以參考：http://blog.itpub.net/26736162/viewspace-2126079/

【故障處理】序列cache值過小導致CPU利用率過高”，連接地址為：http://blog.itpub.net/26736162/viewspace-2123996/

2016-10-18

【等待事件】等待事件系列（5.1）--Enqueue(隊列等待)--5.1.6 enq: SQ - contention 序列

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771602&idx=1&sn=3112cd29b4877435ffe37b603b263d00&chksm=fe8bbaeac9fc33fc6b0df4efa6e923f7c4f7ba2830659b75934627bf079dc76842bfc2b6f976&scene=21#wechat_redirect

2016-08-24

【故障處理】序列cache值過小導致CPU利用率過高

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771414&idx=1&sn=0cbc454bc7d5a513bbca6083958e2f34&scene=21#wechat_redirect

2016-10-14

【等待事件】等待事件系列（5.1）--Enqueue(隊列等待)--5.1.2 數據字典

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771592&idx=1&sn=2a2b8c842aa95fd05c5de559495f4055&chksm=fe8bbaf0c9fc33e66b5b2dc4b8a0b411837777a202201b89e853bd41a49ed1ee9ec1d74bb22d&scene=21#wechat_redirect

2016-10-11

【等待事件】等待事件系列（5.1）--Enqueue(隊列等待)--5.1.1 簡介部分

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771584&idx=1&sn=037855f956bb297168b3e2282c436e53&chksm=fe8bbaf8c9fc33eebeca20a9c66c18800bd49e5f90059e0df6f1516f456c6de2968f2322d1f6&scene=21#wechat_redirect

等待事件系列

【故障處理】隊列等待之TX - allocate ITL entry引起的死鎖處理

http://blog.itpub.net/26736162/viewspace-2124771/

【故障處理】隊列等待之enq: US - contention案例

http://blog.itpub.net/26736162/viewspace-2124767/

【推薦】【故障處理】隊列等待之TX - allocate ITL entry案例

http://blog.itpub.net/26736162/viewspace-2124735/

【推薦】等待事件系列（1）--User I/O類型（下）

http://blog.itpub.net/26736162/viewspace-2124435/

【推薦】等待事件系列（1）--User I/O類型（上）

http://blog.itpub.net/26736162/viewspace-2124417/

【推薦】【故障處理】隊列等待之enq: TX - row lock contention

http://blog.itpub.net/26736162/viewspace-2124369/

【推薦】【等待事件】等待事件系列（5.1）--Enqueue(隊列等待)

http://blog.itpub.net/26736162/viewspace-2126079/

【推薦】【等待事件】等待事件系列（3+4）--System IO（控制文件）+日志類等待

http://blog.itpub.net/26736162/viewspace-2125065/

【推薦】等待事件系列（1）--User I/O類型（下）

http://blog.itpub.net/26736162/viewspace-2124435/

【推薦】等待事件系列（1）--User I/O類型（上）

http://blog.itpub.net/26736162/viewspace-2124417/

【推薦】【故障處理】隊列等待之TX - allocate ITL entry引起的死鎖處理

http://blog.itpub.net/26736162/viewspace-2124771/

【推薦】【故障處理】隊列等待之enq: US - contention案例

http://blog.itpub.net/26736162/viewspace-2124767/

【推薦】【故障處理】隊列等待之TX - allocate ITL entry案例

http://blog.itpub.net/26736162/viewspace-2124735/

【推薦】【故障處理】隊列等待之enq: TX - row lock contention

http://blog.itpub.net/26736162/viewspace-2124369/

【推薦】【故障處理】序列cache值過小導致CPU利用率過高

http://blog.itpub.net/26736162/viewspace-2123996/

2.3 本文簡介

在2016年8月24日發布過一篇文章，BLOG名稱為：“【故障處理】序列cache值過小導致CPU利用率過高”，連接地址為：http://blog.itpub.net/26736162/viewspace-2123996/，文章中根據案例詳細分析了一次有關enq: SQ - contention等待事件的問題，基本上把所有的有關序列問題導致的等待事件全部講解了一遍，但是那篇文章是針對Oracle 10g數據庫而言的，而在Oracle 11g中，對SV鎖進行了分離，出現了enq: SV - contention等待事件，而在Oracle 10g不存在“enq: SV - contention”該等待事件，而是表現為DFS lock handle，所以這篇文章將再次把序列等待事件整理一下。

第二章序列等待事件

1 基礎知識介紹

序列的等待事件基本上都與隊列的等待事件相關，有關隊列的更多介紹可以參考：http://blog.itpub.net/26736162/viewspace-2126079/

其實，“enq: SQ - contention”、“row cache lock”、“DFS lock handle”和“enq: SV - contention”這4個等待事件都與Oracle的序列有關，如下所示：

SELECT *

FROM V$EVENT_NAME

WHERE NAME IN ('row cache lock',

'enq: SQ - contention',

'DFS lock handle',

'enq: SV - contention');

其中，PAREMETER1的值為“name|mode”或“type|mode”的事件為隊列等待。在這類等待事件中，name代表隊列的名稱，type代表隊列的類型，mode代表隊列的模式。使用如下的SQL可以查詢到鎖的名稱和請求的mode值：

SELECT CHR(BITAND(P1,-16777216)/16777215)||

CHR(BITAND(P1, 16711680)/65535) "LOCK",

BITAND(P1, 65535) "MODE"

FROM V$SESSION_WAIT

WHERE EVENT IN ('enq: SQ - contention',

'DFS lock handle',

'enq: SV - contention');

其中，MODE值如下表所示：

使用如下的SQL可以查詢SQ和SV這2種鎖的解釋：

SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('SV','SQ');

事實上，Oracle為了管理序列使用了如下表所示的三種鎖：

RAC上創建序列時，在賦予了CACHE屬性的狀態下，若沒有賦予ORDER屬性，則各節點將會把不同范圍的序列值CACHE到內存上。比如，擁有兩個節點的RAC環境下，創建CACHE值為100的序列時，1號節點使用1～100，2號節點使用101～200。若兩個節點之間都通過遞增方式使用序列，必須賦予ORDER屬性。Oracle序列默認是NOORDER，如果設置為ORDER，那麼在單實例環境沒有影響，在RAC環境中，多實例實際緩存相同的序列，此時在多個實例並發取該序列的時候，會有短暫的資源競爭來在多實例之間進行同步。因次性能相比NOORDER要差，所以RAC環境非必須的情況下不要使用ORDER，尤其要避免NOCACHE ORDER組合。

有一點必須要注意，沒有賦予CACHE屬性時，不管ORDER屬性使用與否或RAC環境與否，一直等待row cache lock 事件。row cache lock是可以在全局范圍內使用的鎖，單實例環境或多實例環境同樣可以發生。如果使用了CACHE，如果此時DB崩潰了，那麼序列會從CACHE值之後重新開始，在CACHE中沒有使用的序列會被跳過，這樣就會導致序列不連續。在創建序列時，CACHE的缺省值設定為較小的20。因此創建並發量多的序列時，CACHE值應該取1000以上的較大值。

另外，若一次性同時創建許多會話時，有時會發生enq: SQ - contention等待事件。其原因是V$SESSION.AUDSID列值是利用序列創建的。Oracle在創建新的會話後，利用名為SYS.AUDSES$的序列的NEXTVAL來創建AUDSID值。在Oracle 10g下SYS.AUDSES$的CACHE值默認20，但在Oracle 11g下SYS.AUDSES$的CACHE值默認為10000，通過如下的SQL可以查詢：

SELECT * FROM DBA_SEQUENCES D WHERE D.SEQUENCE_NAME ='AUDSES$';

2 RAC中的序列的ORDER和NOORDER測試

2.1 測試1：NOORDER屬性的序列

NODE1：

SQL> CREATE SEQUENCE SEQ_NOORDER_LHR START WITH 1 INCREMENT BY 1 CACHE 20 NOORDER;

SEQUENCE CREATED.

SQL> SELECT SEQ_NOORDER_LHR.NEXTVAL FROM DUAL;

NEXTVAL

----------

SQL> /

NEXTVAL

----------

SQL> /

NEXTVAL

----------

NODE2：

SQL> SELECT SEQ_NOORDER_LHR.NEXTVAL FROM DUAL;

NEXTVAL

----------

SQL> /

NEXTVAL

----------

SQL> /

NEXTVAL

----------

NODE2上不是從4開始的，是從21開始的，因為NODE1已經CACHE了20個。

2.2 測試2：ORDER屬性的序列

NODE1：

SQL> CREATE SEQUENCE SEQ_ORDER_LHR START WITH 1 INCREMENT BY 1 CACHE 20 ORDER;

SEQUENCE CREATED.

SQL> SELECT SEQ_ORDER_LHR.NEXTVAL FROM DUAL;

NEXTVAL

----------

SQL> /

NEXTVAL

----------

SQL> /

NEXTVAL

----------

NODE2：

SQL> SELECT SEQ_ORDER_LHR.NEXTVAL FROM DUAL;

NEXTVAL

----------

SQL> /

NEXTVAL

----------

SQL> /

NEXTVAL

----------

指定ORDER之後，取的序列就是順序的。

3 序列的CACHE值性能測試

SYS@lhrdb> CREATE SEQUENCE SEQ_NOCACHE_LHR NOCACHE;

Sequence created.

SYS@lhrdb> CREATE SEQUENCE SEQ_CACHE20_LHR CACHE 20;

Sequence created.

SYS@lhrdb> CREATE SEQUENCE SEQ_CACHE100_LHR CACHE 100;

Sequence created.

SYS@lhrdb> CREATE SEQUENCE SEQ_CACHE1000_LHR CACHE 1000;

Sequence created.

SYS@lhrdb> SET TIMING ON;

SYS@lhrdb> DECLARE

2 X NUMBER;

3 BEGIN

4 FOR I IN 1 .. 600000 LOOP

5 SELECT SEQ_NOCACHE_LHR.NEXTVAL INTO X FROM DUAL;

6 END LOOP;

7 END;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:16.16

SYS@lhrdb> DECLARE

2 X NUMBER;

3 BEGIN

4 FOR I IN 1 .. 600000 LOOP

5 SELECT SEQ_CACHE20_LHR.NEXTVAL INTO X FROM DUAL;

6 END LOOP;

7 END;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.79

SYS@lhrdb> DECLARE

2 X NUMBER;

3 BEGIN

4 FOR I IN 1 .. 600000 LOOP

5 SELECT SEQ_CACHE100_LHR.NEXTVAL INTO X FROM DUAL;

6 END LOOP;

7 END;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.22

SYS@lhrdb> DECLARE

2 X NUMBER;

3 BEGIN

4 FOR I IN 1 .. 600000 LOOP

5 SELECT SEQ_CACHE1000_LHR.NEXTVAL INTO X FROM DUAL;

6 END LOOP;

7 END;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.74

SYS@lhrdb>

Oracle 11gR2測試，單實例數據庫單會話循環不間斷取600000個值。

類別

用時

NOCACHE

00:01:16.16

CACHE 20

00:00:17.79

CACHE 100

00:00:15.22

CACHE 1000

00:00:13.74

基本上cache 大於20的時候性能基本可以接受，nocache的時候性能確實很差。

4 等待事件模擬

4.1 row cache lock序列等待模擬

版本：11.2.0.4

CREATE SEQUENCE LHR_SEQ01 START WITH 1 NOCACHE ;

開2-3個窗口分別執行如下的SQL語句：

DECLARE

V_N NUMBER;

BEGIN

FOR CUR IN 1 .. 100000000 LOOP

SELECT LHR_SEQ01.NEXTVAL INTO V_N FROM DUAL;

END LOOP;

END;

可以使用vmstat 1查看CPU的使用率。

SELECT A.SID,

A.BLOCKING_SESSION,

A.SQL_ID,

(SELECT B.SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) SQL_TEXT,

A.P1

FROM V$SESSION A

WHERE A.STATUS = 'ACTIVE'

AND A.EVENT = 'row cache lock';

SELECT * FROM V$ROWCACHE A WHERE A.CACHE#=13;

SELECT * FROM v$lock a WHERE a.sid IN (6,8,114) AND a.TYPE <>'AE';

SELECT * FROM V$lock_Type a WHERE A.TYPE='TO';

4.2 enq: SQ - contention模擬

版本：11.2.0.4

DROP SEQUENCE LHR_SEQ01;

CREATE SEQUENCE LHR_SEQ01 START WITH 1 CACHE 2 NOORDER;

DECLARE

V_N NUMBER;

BEGIN

FOR CUR IN 1 .. 100000000 LOOP

SELECT LHR_SEQ01.NEXTVAL INTO V_N FROM DUAL;

END LOOP;

END;

SELECT A.SID,

A.BLOCKING_SESSION,

A.SQL_ID,

(SELECT B.SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) SQL_TEXT,

CHR(BITAND(P1, -16777216) / 16777215) ||

CHR(BITAND(P1, 16711680) / 65535) "Lock",

BITAND(P1, 65535) "Mode",

(SELECT A.OBJECT_NAME || ':' || A.OBJECT_TYPE

FROM DBA_OBJECTS A

WHERE A.OBJECT_ID = A.p2) OBJECT_NAME

FROM V$SESSION A

WHERE A.STATUS = 'ACTIVE'

AND A.EVENT = 'enq: SQ - contention';

4.3 enq: SV - contention和DFS lock handle模擬

DROP SEQUENCE LHR_SEQ01;

CREATE SEQUENCE LHR_SEQ01 START WITH 1 CACHE 2 ORDER;

DECLARE

V_N NUMBER;

BEGIN

FOR CUR IN 1 .. 100000000 LOOP

SELECT LHR_SEQ01.NEXTVAL INTO V_N FROM DUAL;

END LOOP;

END;

版本：11.2.0.4

SELECT A.INST_ID,

A.SID,

A.BLOCKING_SESSION,

A.SQL_ID,

(SELECT B.SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) SQL_TEXT,

CHR(BITAND(P1, -16777216) / 16777215) ||

CHR(BITAND(P1, 16711680) / 65535) "Lock",

BITAND(P1, 65535) "Mode",

(SELECT A.OBJECT_NAME || ':' || A.OBJECT_TYPE

FROM DBA_OBJECTS A

WHERE A.OBJECT_ID = A.P2) OBJECT_NAME,

A.EVENT

FROM GV$SESSION A

WHERE A.STATUS = 'ACTIVE'

-- AND A.EVENT = 'enq: SQ - contention'

AND A.SID IN (225, 99, 193, 194)

AND A.SQL_ID IS NOT NULL;

Oracle 10.2.0.5中：

注意，Oracle 10g表現為：DFS lock handle，而Oracle 11g中表現為enq: SV - contention。個人測試版本，10g為10.2.0.5，11g版本為11.2.0.4。

5 序列等待案例處理參考

有關隊列的更多介紹可以參考：http://blog.itpub.net/26736162/viewspace-2126079/

【故障處理】序列cache值過小導致CPU利用率過高”，連接地址為：http://blog.itpub.net/26736162/viewspace-2123996/

2016-08-24

【故障處理】序列cache值過小導致CPU利用率過高

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771414&idx=1&sn=0cbc454bc7d5a513bbca6083958e2f34&scene=21#wechat_redirect

About Me

...............................................................................................................................

● 本文作者：小麥苗，只專注於數據庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2127465/

● 本文博客園地址：http://www.cnblogs.com/lhrbest/p/6020872.html

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 聯系我請加QQ好友(642808185)，注明添加緣由

● 於 2016-10-31 12:00 ~ 2016-11-01 19:00 在中行完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解

...............................................................................................................................

手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,免費學習最實用的數據庫技術。

上一頁:java調用oracle函數，java調用oracle
下一頁:DB2 多行一列轉換成一行一列，和 between and的用法，db2between

Oracle教程

Redhat 6.2 下 oracle 11g R2的安裝詳解第1/2頁，redhat11g

Redhat 6.2 下 oracle 11g R2的安裝詳

zabbix監控oracle 12c

zabbix監控oracle 12c zabbix監控ora

OracleEBSLOV速度優化

OracleEBSLOV速度優化一、現象當我們的EBS

數據庫的連接(學習筆記)，數據庫學習筆記

數據庫的連接(學習筆記)，數據庫學習筆記Oracle安裝完成

oracle與sqlserver利用函數生成年月日加流水號，oraclesqlserver

oracle與sqlserver利用函數生成年月日加流水號，

如何只授予用戶查看存儲過程定義的權限

如何只授予用戶查看存儲過程定義的權限有個網友問我，如何授予

熱門圖文

ASP+Access的安全隱患及對策 MySQL的分表處理實現方法如何提取除最新十條記錄外的所有記錄? SQL Server BI Step by Step SSIS 6 - 獲取遠程主機的可用磁盤空間 SQL Server口令 sql server安全性一個容易忽視的Oracle安全問題 MySQL雙Master配置的方法詳解三步添加oracle主鍵自增長

欄目導航

關於Oracle數據庫 Oracle數據庫基礎 Oracle教程

【等待事件】序列等待事件總結（enq: SQ，enqsq