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

lhrbest發表於2016-08-24

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

1  BLOG文件結構圖

 

wps3FDC.tmp 

 

2  前言部分

2.1  導讀和注意事項

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

① enq: SQ - contention等待事件的解決

② 一般等待事件的解決辦法

③ DFS lock handle等待事件

④ 與序列有關的等待事件

  Tips：

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

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，可以去部落格園地址閱讀

④ 本篇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_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

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

 

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

 

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

 

 

3  故障分析及解決過程

 

3.1  故障環境介紹

 

專案

source db

db 型別

RAC

db version

10.2.0.5.0

db 儲存

ASM

OS版本及kernel版本

AIX 64位 6.1.0.0

 

3.2  故障發生現象及報錯資訊

早上同事過來跟我說昨天有一套資料庫做測試的時候，CPU利用率很高，他已經抓取了CPU和AWR，讓我幫忙分析分析，首先發生問題的時間段是19點到23點，nmon資料截圖如下：

wps3FED.tmp 

可以看到CPU的利用率是非常高的，下邊我們來看看AWR中的資料：

wps3FEE.tmp 

wps3FEF.tmp 

其它的專案就不列出了，從等待事件中可以很明顯的看出enq: SQ - contention和DFS lock handle這2個等待事件異常。Top 5 Timed Events這個部分也是AWR報告中非常重要的部分，從這裡可以看出等待時間在前五位的是什麼事件，基本上就可以判斷出效能瓶頸在什麼地方。通常，在沒有問題的資料庫中，CPU time總是列在第一個。在這裡，enq: SQ - contention等待了172254次，等待時間為69652秒，平均等待時間為69652/172254=404毫秒，等待類別為Configuration即配置上的等待問題。

3.3  故障分析

根據AWR報告的內容，我們知道只要解決了enq: SQ - contention和DFS lock handle這2個等待事件即可解決問題。那麼我們首先來了解一些關於這2個等待事件的知識。

===============================================================================

enq: SQ - contention/row cache lock/DFS lock handle這三個等待事件都與Oracle 的Sequence 有關。

 

SELECT *

  FROM V$EVENT_NAME

 WHERE NAME IN

       ('row cache lock', 'enq: SQ - contention', 'DFS lock handle');

wps4000.tmp 

使用如下的SQL我們可以查詢到鎖的名稱和請求的MODE，表的mode值參考表格：

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) "Lock",

bitand(p1, 65535) "Mode"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

Table C-1 Lock Mode Values

Mode Value

Description

1

Null mode

2

Sub-Share

3

Sub-Exclusive

4

Share

5

Share/Sub-Exclusive

6

Exclusive

 

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

wps4001.tmp 

Oracle 為了管理Sequence 使用了以下三種鎖。

① row cache lock:在呼叫SEQUNECE.NEXTVAL過程中，將資料字典資訊進行物理修改時獲取。賦予了NOCACHE屬性的SEQUENCE上發生，等待事件為row cache lock。

② SQ鎖:在記憶體上快取（CACHE）的範圍內，呼叫SEQUENCE.NEXTVAL 期間擁有此鎖。賦予了CACHE 屬性的SEQUENCE 上發生。賦予了CACHE 屬性的SEQUENCE 呼叫NEXTVAL 期間，應該以SSX 模式獲得SQ 鎖。許多會話同時為了獲取SQ 鎖而發生爭用過程中，若發生爭用，則等待enq: SQ - contention事件。enq: SQ - contention 事件的P2 值是Sequence 的OBJECT ID。因此，若利用P2 值與DBA_OBJECTS 的結合，就可以知道對哪個SEQUENCE 發生了等待現象。

③ SV鎖:RAC上節點之間順序得到保障的情況下，呼叫SEQUENCE.NEXTVAL期間擁有。賦予CACHE + ORDER屬性的SEQUENCE 上發生，等待事件為DFS lock handle，解決辦法為：儘量設定為NOORDER並增大其CACHE值。

根據建立Sequence時賦予的屬性，整理等待事件的結果如下：

v NOCACHE: row cache lock

v CACHE + NOORDER: enq: SQ - contention

v CACHE + ORDER(RAC): DFS lock handle

 

建立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 session id）列值是利用Sequence建立的。Oracle 在建立新的會話後，利用名為SYS.AUDSES$的Sequence 的nextval，建立AUDSID 值。SYS.AUDSES$ Sequence 的CACHE 大小的預設值設定為20。許多會話同時連線時，可以將SYS.AUDSES$ Sequence 的CACHE大小擴大至1000，以此可以解決enq: SQ - contention 等待問題。 10g下預設20,11g下預設為10000，通過如下的SQL可以查詢：

SELECT * FROM dba_sequences d WHERE d.sequence_name ='AUDSES$';

wps4002.tmp 

RAC 上建立SEQUENCE 時，在賦予了CACHE屬性的狀態下，若沒有賦予ORDER 屬性，則各節點將會把不同範圍的SEQUENCE 值CACHE 到記憶體上。比如，擁有兩個節點的RAC 環境下，建立CACHE 值為100 的SEQUENCE 時，1號節點使用1～100，2 號節點使用101～200。若兩個節點之間都通過遞增方式使用SEQUENCE，必須賦予如下ORDER 屬性。

      SQL> CREATE SEQUENCE ORDERED_SEQUENCE CACHE 100 ORDER;

如果是已賦予了CACHE+ORDER 屬性的SEQUENCE，Oracle 使用SV 鎖進行行同步。即，對賦予了ORDER 屬性的Sequence 呼叫nextval 時，應該以SSX模式擁有SV 鎖。在獲取SV 鎖過程中，如果發生爭用時，不是等待row cache lock 事件或enq: SQ - contention 事件，而是等待名為DFS lock handle 事件。正因如此，V$EVENT_NAME 檢視上不存在類似"enq:SV-contention"的事件。DFS lock handle 事件是在OPS 或RAC 環境下，除了高速緩衝區同步之外，還有行高速緩衝區或庫高速緩衝區的為了同步獲取鎖的過程中等待的事件。若要保障多個節點之間Sequence順序，應該在全域性範圍內獲得鎖，在此過程中會發生DFS lock handle 等待。在獲取SV 鎖的過程中發生的DFS lock handle等待事件的P1 、P2 值與enq: SQ - contention 等待事件相同（ P1=mode+namespace、P2=object#）。因此從P1 值能確認是否是SV 鎖，通過P2值可以確認對哪些Sequence 發生過等待。SV 鎖爭用問題發生時的解決方法與SQ 鎖的情況相同，就是將CACHE 值進行適當調整，這也是唯一的方法。

在RAC 等多節點環境下，Sequence 的CACHE 值給效能帶來的影響比單節點環境更嚴重。因此，儘量賦予CACHE+NOORDER 屬性，並要給予足夠大的CACHE值。如果需要保障順序，必須賦予CACHE+ORDER 屬性。但這時為了保障順序，例項之間不斷髮生資料的交換。因此，與賦予了NOORODER屬性的時候相比效能稍差。

有一點必須要注意，沒有賦予CACHE屬性時，不管ORDER 屬性使用與否或RAC 環境與否，一直等待row cache lock 事件。row cache lock是可以在全域性範圍內使用的鎖，單例項環境或多例項環境同樣可以發生。

沒有賦予CACHE屬性時，不管ORDER屬性是否或RAC環境是否，一直等待ROW CACHE事件，ROW CACHE LOCK是否可以在全域性範圍內使用的鎖，單例項環境或多例項環境同時可以發生。

Oracle Sequence預設是NOORDER，如果設定為ORDER；在單例項環境沒有影響，在RAC環境此時，多例項實際快取相同的序列，此時在多個例項併發取該序列的時候，會有短暫的資源競爭來在多例項之間進行同步。因次效能相比noorder要差，所以RAC環境非必須的情況下不要使用ORDER，尤其要避免NOCACHE ORDER組合。

但是如果使用了Cache，如果此時DB 崩潰了，那麼sequence會從cache之後重新開始，在cache中沒有使用的sequence會被跳過。即sequence不連續。所以只有在多節點高峰併發量很大的情況且對連續性要求不高的情況下，才使用：noorder + cache。

DFS lock handle

The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.

Wait Time: The session waits in a loop until it has obtained the lock handle from the DLM. Inside the loop there is a wait of 0.5 seconds.

Parameter

Description

name

See "name and type"

mode

See "mode"

id1

See "id1"

id2

See "id2"

The session needs to get the lock handle.

該等待事件的發生，若不是SV鎖的話，多半為bug引起。

id1

The first identifier (id1) of the enqueue or global lock takes its value from P2 or P2RAW. The meaning of the identifier depends on the name (P1).

id2

The second identifier (id2) of the enqueue or global lock takes its value from P3 or P3RAW. The meaning of the identifier depends on the name (P1).

mode

The mode is usually stored in the low order bytes of P1 or P1RAW and indicates the mode of the enqueue or global lock request.This parameter has one of the following values:

Table C-1 Lock Mode Values

Mode Value

Description

1

Null mode

2

Sub-Share

3

Sub-Exclusive

4

Share

5

Share/Sub-Exclusive

6

Exclusive

 

Use the following SQL statement to retrieve the name of the lock and the mode of the lock request:

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) "Lock",

bitand(p1, 65535) "Mode"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

name and type

The name or "type" of the enqueue or globallock can be determined by looking at the two high order bytes of P1 or P1RAW. The name is always two characters. Use the following SQL statement to retrieve the lock name.

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1,16711680)/65535) "Lock"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

 

===============================================================================

有了以上的知識，我們知道，目前只需要找到產生等待的序列名稱，然後設定其CACHE為比較大的一個值即可解決問題。

3.4  故障解決過程

3.4.1  enq: SQ - contention等待事件

我們查詢出現問題時間段的ASH檢視DBA_HIST_ACTIVE_SESS_HISTORY來找到我們需要的序列名稱。

可以有多種查詢方法：

SELECT D.SQL_ID, COUNT(1)

  FROM DBA_HIST_ACTIVE_SESS_HISTORY D

 WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000', 'YYYYMMDDHH24MISS') AND

       TO_DATE('20160823230000', 'YYYYMMDDHH24MISS')

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

 GROUP BY D.SQL_ID;

wps4012.tmp 

可以看到SQL_ID為3jhvjgj7kbpmt的SQL最多，我們檢視具體SQL內容：

SELECT * FROM V$SQL A WHERE A.SQL_ID IN ('3jhvjgj7kbpmt') ;

wps4013.tmp 

由此可以知道，產生等待的序列名稱為ONLNID,另外，我們也可以從DBA_HIST_ACTIVE_SESS_HISTORY檢視的P2值獲取到序列的名稱，如下：

 SELECT D.EVENT,

        D.P1TEXT,

        D.P1,

        D.P2TEXT,

        D.P2,

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

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

        BITAND(P1, 65535) "Mode",

        D.BLOCKING_SESSION,

        D.BLOCKING_SESSION_STATUS,

        D.BLOCKING_SESSION_SERIAL#,

        D.SQL_ID,

        TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDDHH24MISS') SAMPLE_TIME,

        D.*

   FROM DBA_HIST_ACTIVE_SESS_HISTORY D

  WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000', 'YYYYMMDDHH24MISS') AND

        TO_DATE('20160823230000', 'YYYYMMDDHH24MISS')

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

wps4014.tmp 

由以上的查詢結果可知，序列的object_id為47989，由此也可以知道序列名稱如下，另外，lock為SQ代表的是序列的cache鎖（Sequence Cache），mode為6代表Exclusive排他鎖。

SELECT * FROM DBA_OBJECTS D WHERE D.object_id='47989';

wps4015.tmp 

知道了序列名稱後，我們就可以查詢序列的屬性了：

SELECT * FROM DBA_SEQUENCES D WHERE D.sequence_name='ONLNID' ;

wps4026.tmp 

可以看到，該序列是NOORDER屬性，CACHE值為預設的20，對於併發值很高的系統而言，該預設值太低，所以需要調整到1000，我們執行SQL:ALTER SEQUENCE NFXS.ONLNID CACHE 1000; 調整其cache值即可解決該問題。

 

3.4.2  DFS lock handle等待事件

我們查詢出現問題時間段的ASH檢視DBA_HIST_ACTIVE_SESS_HISTORY來找到我們需要的序列名稱。

可以有多種查詢方法：

SELECT D.SQL_ID, COUNT(1)

  FROM DBA_HIST_ACTIVE_SESS_HISTORY D

 WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000', 'YYYYMMDDHH24MISS') AND

       TO_DATE('20160823230000', 'YYYYMMDDHH24MISS')

   AND D.EVENT = 'DFS lock handle'

 GROUP BY D.SQL_ID;

wps4027.tmp 

可以看到SQL_ID為"67vjwqswg2zvy"的SQL最多，我們檢視具體SQL內容：

SELECT * FROM V$SQL A WHERE A.SQL_ID IN ('67vjwqswg2zvy') ; 

wps4028.tmp 

SQL的內容為：

SELECT formatid, globalid, branchid FROM                    SYS.DBA_PENDING_TRANSACTIONS                  ORDER BY formatid, globalid, branchid

很奇怪，這是個系統檢視，為啥會有DFS lock handle的等待事件產生呢？

 SELECT D.EVENT,

        D.P1TEXT,

        D.P1,

        D.P2TEXT,

        D.P2,

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

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

        BITAND(P1, 65535) "Mode",

        D.BLOCKING_SESSION,

        D.BLOCKING_SESSION_STATUS,

        D.BLOCKING_SESSION_SERIAL#,

        D.SQL_ID,

        TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDDHH24MISS') SAMPLE_TIME,

        D.*

   FROM DBA_HIST_ACTIVE_SESS_HISTORY D

  WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000', 'YYYYMMDDHH24MISS') AND

        TO_DATE('20160823230000', 'YYYYMMDDHH24MISS')

    AND D.EVENT = 'DFS lock handle'; 

wps4039.tmp 

由以上的查詢結果可知，lock為CI代表的是交叉例項功能呼叫例項，而並不是我們期望的SV鎖，mode為5代表Share/Sub-Exclusive。

SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE ='CI';

wps403A.tmp 

查了metalink可知，該問題是由bug引起。該庫的版本為10.2.0.5的基礎版本，並沒有打任何的PSU。

3.5  健康檢查

wps403B.tmp

AWR分析完成後，我又收集了一下該庫的健康檢查情況，看看是否有其它方面的問題。

wps404B.tmp 

wps404C.tmp 

可以看出這個庫並沒有任何的PSU的資訊，然後我們直接檢視檢查的結果：

wps404D.tmp 

wps404E.tmp 

可以看到資料庫有上邊的幾個問題，其中就有cache小於20的問題，我們點選連線可以看到：

wps405F.tmp 

另外，在告警日誌中，我們也可以看到，如下的資訊，說明prcesses引數設定過小。

wps4060.tmp 

 

  About Me

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

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

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新，推薦pdf檔案閱讀

● QQ群：230161599 微信群：私聊

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2123996/ 部落格園地址：http://www.cnblogs.com/lhrbest/articles/5804363.html

● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

● 小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

● 聯絡我請加QQ好友(642808185)，註明新增緣由

● 於 2016-08-24 09:00~2016-08-24 19:00 在中行完成

● 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

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

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

wps4070.tmp

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2123996/，如需轉載，請註明出處，否則將追究法律責任。

相關文章