年10月15號12點30分，CRM資料庫一節點資料庫響應極其慢，導致後臺應用無法繼續。登陸檢查資料庫，發現大量的enq: US – contention及enq: SQ – contention等待事件，導致資料庫程式嚴重積壓，會話響應緩慢。

為緩解系統壓力，首先重建UNDO表空間以緩解enq: US – contention等待事件。在重建並替換完UNDO後，重啟資料庫，效果不明顯，大量的回滾事務依舊存在，13點00分，在替換UNDO效果不明顯的情況下，我們新增隱含引數：“_UNDO_AUTOTUNE“=FALSE關閉UNDO的自動調整特性。此時資料庫響應正常，業務系統正常。後續我們對本次故障進行資訊收集並分析故障原因。

時間點	故障處理關鍵點
10-15 12:30	CRM 一節點業務執行緩慢，資料庫大量等待事件
10-15 12:40	排查並分析原因，替換UNDO表空間
10-15 12:50	新增隱含引數，禁用UNDO自動調整
10-15 13:00	對後續的問題進行收集並詳細分析
10-15 14:20	寫故障分析報告

資料庫問題的現象如下：

1 、2014年10月15日12點30分，接聯通電話，告知CRM核心資料庫一節點例項異常導致資料庫響應緩慢，業務執行異常。

2 、遠端撥號檢查資料庫異常原因，分析資料庫緩慢原因

檢查資料庫一節點等待事件：

SID EVENT SQL_ID

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

1549 enq: SQ - contention

1570 enq: US - contention 0jdpgs42gzwtt

1576 enq: US - contention 3h05q2u88zup7

1590 enq: US - contention dwfnn4vz3k999

1591 enq: US - contention dwfnn4vz3k999

1592 enq: US - contention d0zwwfwy1ka03

1594 enq: SQ - contention

1598 enq: US - contention 1mappfj3bf4ss

1609 enq: SQ - contention

1628 enq: SQ - contention

1637 enq: US - contention 52skk1qhxmcf0

SID EVENT SQL_ID

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

1639 enq: SQ - contention

1646 enq: SQ - contention

1661 enq: US - contention 2jgg7yzhnbqyn

1674 enq: US - contention f185h57q40zxz

1679 enq: US - contention 6ffnkxws367vr

1681 enq: US - contention gsj20qadyguh1

1684 enq: US - contention fy0bjb6spurx4

1691 enq: US - contention dwfnn4vz3k999

1698 enq: SQ - contention

1702 enq: SQ - contention

1715 enq: TX - row lock contention 743bfk8w1qp7v

SID EVENT SQL_ID

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

1717 enq: US - contention cudgcw4fqy8zz

1730 enq: SQ - contention

1757 enq: US - contention af2j8jyb1zfzu

1760 enq: US - contention bcf9yan5h2m3c

1763 enq: SQ - contention

1769 enq: US - contention dwfnn4vz3k999

1770 enq: SQ - contention

1771 enq: US - contention 70j4acnazq6c6

1773 enq: US - contention dwfnn4vz3k999

1775 enq: US - contention fy0bjb6spurx4

3 、以上等待事件大量的集中在enq: US – contention並且相關的SQL都不盡一致。

為緩解問題，首先新增並替換UNDO資料檔案：

create undo tablespace UNDOTBS3 datafile '+DATA/crmdb/datafile/undotbs03.dbf' size 4G autoextend on;

alter tablespace UNDOTBS3 add datafile '+DATA/crmdb/datafile/undotbs3_02.dbf' size 4G autoextend on;

alter tablespace UNDOTBS3 add datafile '+DATA/crmdb/datafile/undotbs3_03.dbf' size 4G autoextend on;

alter tablespace UNDOTBS3 add datafile '+DATA/crmdb/datafile/undotbs3_04.dbf' size 4G autoextend on;

4 、新增完後整體效果並不明顯，進一步分析該問題，並確認ORACLE MOS文件：

How to correct performance issues with enq: US - contention related to undo segments (Doc ID 1332738.1)

根據文件給出的建議，我們進一步關閉了UNDO的自動調整新特性：

調整完後，檢查發現該等待事件已經消失。進一步收集故障時間段的AWR等報告，並針對enq: SQ – contention等待事件進行分析

5 、根據enq: SQ – contention等待事件，我們檢查最近一段時間資料庫SEQUENCE的CACHE:

SQL> SELECT OWNER,OBJECT_NAME,CREATED FROM DBA_OBJECTS WHERE OBJECT_TYPE='SEQUENCE' AND CREATED > SYSDATE-3;

OWNER OBJECT_NAME CREATED

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

ZJUCRM1O SEQ_BMS_OTHERFEE_ID 2014-10-14 12:55:16

ZJUCRM1O SEQ_UCS_ACCT_RELA_ID 2014-10-14 16:07:51

ZJIOM RES_EQPT_PROJECT_RES_INFO$SEQ 2014-10-14 21:24:00

ZJIOM RES_EQPT_PROJECT_USER_INFO$SEQ 2014-10-14 21:24:06

檢查以上幾個SEQUENCE對應的CACHE_SIZE大小：

SQL> SELECT SEQUENCE_OWNER,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_NAME='SEQ_UCS_ACCT_RELA_ID';

SEQUENCE_OWNER CACHE_SIZE

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

ZJUCRM1O 20

SQL> SELECT SEQUENCE_OWNER,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_NAME='SEQ_BMS_OTHERFEE_ID';

SEQUENCE_OWNER CACHE_SIZE

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

ZJUCRM1O 20

SQL> SELECT SEQUENCE_OWNER,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_NAME='RES_EQPT_PROJECT_USER_INFO$SEQ';

SEQUENCE_OWNER CACHE_SIZE

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

ZJIOM 20

SQL> SELECT SEQUENCE_OWNER,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_NAME='RES_EQPT_PROJECT_RES_INFO$SEQ';

SEQUENCE_OWNER CACHE_SIZE

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

ZJIOM 20

以上SEQUENCE的CACHE都採用了預設的大小—即20，該值遠遠無法達到業務所需要的需求，建議調整以上序列的CACHE大小。

1. 檢查業務系統使用者的SEQUENCE的CACHE大小，對CACHE設定不足的SEQUENCE進行修改

2. 保持UNDO的自動調整設定，觀察並後續考慮調整回來。

一次資料庫響應慢分析

相關文章