enq: SQ – contention、cursor: p

事情已經過去一年，發生在15年1月份，某全國業務系統，實時的號碼辦理系統，收到短信告警，該系統斷開連接。數據庫出現大量enq: SQ – contention、cursor: pin S wait on X等事件，alert日志中出現大量的ORA-04031報錯。進行flush share pool后進行了相關查詢操作。

9點前后，活動會話數突然攀升，與alert日志在1月31號首次出現ORA-04031錯誤時間吻合

TO_CHAR(TRUNC(SA   COUNT(*)

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

2015-01-31 08:44         23

2015-01-31 08:45         28

2015-01-31 08:46         25

2015-01-31 08:47         27

2015-01-31 08:48         33

2015-01-31 08:49         37

2015-01-31 08:50         27

2015-01-31 08:51         23

2015-01-31 08:52         38

2015-01-31 08:53         30

2015-01-31 08:54         32

2015-01-31 08:55         35

2015-01-31 08:56         28

2015-01-31 08:57         27

2015-01-31 08:58         27

2015-01-31 08:59         41

2015-01-31 09:00        556

2015-01-31 09:01        754

2015-01-31 09:02        673

2015-01-31 09:03         45

2015-01-31 09:04        111

2015-01-31 09:05         36

Alert日志內容：

Sat Jan 31 08:47:36 2015

Thread 1 advanced to log sequence 36910 (LGWR switch)

  Current log# 2 seq# 36910 mem# 0: /dev/essdb3vg2/rdb3vg2_1_redo21

  Current log# 2 seq# 36910 mem# 1: /dev/essdb3vg3/rdb3vg3_1_redo22

Sat Jan 31 09:00:50 2015

Errors in file /oraclelog/admin/essdb3/bdump/essdb31_j008_15925.trc:

ORA-12012: error on auto execute of job 1

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select sysdate + 1 / (24 * 6...","sql area","tmp")

Sat Jan 31 09:00:51 2015

Errors in file /oraclelog/admin/essdb3/bdump/essdb31_j003_24229.trc:

ORA-12012: 自動執行作業 1644 出錯

ORA-04031: 無法分配 32 字節的共享內存 ("shared pool","update seq$ set increment$=:...","sql area","tmp")

Sat Jan 31 09:01:31 2015

Errors in file /oraclelog/admin/essdb3/bdump/essdb31_j005_24233.trc:

ORA-12012: 自動執行作業 1624 出錯

ORA-04031: 無法分配 32 字節的共享內存 ("shared pool","update seq$ set increment$=:...","sql area","tmp")

Sat Jan 31 09:01:31 2015

Errors in file /oraclelog/admin/essdb3/bdump/essdb31_j008_15925.trc:

ORA-12012: 自動執行作業 927 出錯

ORA-04031: 無法分配 32 字節的共享內存 ("shared pool","update seq$ set increment$=:...","sql area","tmp")

Sat Jan 31 09:01:43 2015

Errors in file /oraclelog/admin/essdb3/bdump/essdb31_smon_6599.trc:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","insert into sys.col_usage$ (...","sql area","tmp")

分析：

1，由于share pool無法更新字典表SEQ$，因此出現大量的sequence類等待事件是必然的，這也解釋了為什么在top5里為什么enq: SQ – contention等待時間較長。

2，通過查詢V$DBA_HIST_SQLTEXT可以發現以”SELECT ROW_.*”開頭的sql文本共592個，且每個SQL_ID對應sql文本的行數較多，所有的SQL文本分別對應不同的SQL_ID，說明sql的綁定變量存在問題，每次執行SELECT ROW_.*都需要硬解析，會占用大量的share pool。

3，在后臺的alert日志中，可以發現大量的ora-4031告警，而且大部分都為同一條語句，這種日志情況的輸出都是發生在申請free chunk的過程中，同一條語句在遍歷free list的時候，每掃描一個子池，申請不到空間，都會報一個錯。

4，Cursor：pin S wait on x該等待事件主要是由較高的硬解析和高version count造成的，高硬解析和高version count都會大量的消耗shared_pool空間，直接結果就是shared_pool空間不足。