cursor:pin S wait on X故障診分析

wuweilong發表於2022-12-26

1. 故障描述
        7:15,二節點出現大量的“cursor: pin S wait on X”等待事件,資料庫效能下降,持續到7:19分恢復正常,持續時間4分鐘左右。
                下面是詳細的故障分析診斷過程。

2. 故障分析
   2.1.  故障現象

7:15,系統出現大量“cursor: pin S wait on X”等待事件,DBA未做任何操作,資料庫恢復正常。

2.2 故障分析
   2.2.1. 故障現象

從AWR報告7點-8點:15資料庫awr報告。

7:15點-7:19點

7:15點-7:19點分的awr報告可以看大量的cursor: pin S wait on X 等待

2.2.2.  什麼是cursor: pin S wait on X

Shared pool中的Hash Bucket 管理的是Object handle, 也就是後設資料。其上存放了物件的name、 namespace及相關資訊(物件是否只讀,是本機的還是遠端的等),也存放了當前正在lock和pin以及正在等待lock和pin該物件的使用者的列表等。

如果object handle存在,但是相關的object heap已經被刷出記憶體,此時object heap就要被重新reload(v$librarycache.reloads);如果相關object的定義已經被更改(v$librarycache.invalidations),此時就要重新解析相關物件。

cursor: pin S wait on X表示會話試圖以S 模式 Pin 某個 Cursor ,但是某個會話已經以 X 模式 Pinned,正在執行 Loading,也就是 Parsing。 通常cursor: pin S wait on X 不是故障的原因,它只是受害者。

2.2.3. cursor: pin S wait on X 故障原因

  • 記憶體抖動

 但記憶體抖動會加劇shared pool的latch爭用,會導致出現cursor: pin S wait on X,library cache相關等待,嚴重可能導致資料庫hang死或者當機 

  • 頻繁硬解析

硬解析較多也會導致 cursor: pin相關等待增多

 

  • 高版本

    當一個sql的版本過多,也就是子游標過多,當sql軟解析去掃描父遊標下面的子游標,鏈路太長也會導致大量的cursor: pin S wait on X等待。可以透過oracle提供的 去分析高版本的原因。

Cursor Obsolescence 遊標廢棄是一種SQL Cursor遊標管理方面的增強特性,該特性啟用後若parent cursor父遊標名下的子游標child cursor總數超過一定的數目,則該父遊標parent cursor將被廢棄,同時一個新的父遊標將被開始。 這樣做有2點好處:

l    避免程式去掃描長長的子游標列表child cursor list以找到一個合適的子游標child cursor

l    廢棄的遊標將在一定時間內被age out,其佔用的記憶體可以被重新利用

實際在版本10g中就引入了該Cursor Obsolescence遊標廢棄特性,當時child cursor 的總數閥值是1024, 但是這個閥值在11g中被移除了,這導致出現一個父遊標下大量child cursor即high version count的發生;由此引發了一系列的版本11.2.0.3之前的cursor sharing 效能問題,主要症狀是版本11.2.0.1和11.2.0.2上出現大量的Cursor: Mutex S 和library cache lock等待事件。

透過如下引數通知子游標的版本數量。

alter system set "_cursor_obsolete_threshold" =100 scope=spfile;


 

  • 錯誤解析

比如sql語法錯誤

  • DDL

DDL語句會導致相關物件的所有遊標都失效,當再次解析時會造成卡頓。

  • 收集統計資訊 

收集統計資訊(使用ANALYZE或DBMS_STATS)中引數no_invalidate 設定為false,表示遊標立即失效,將導致庫快取物件失效,並且這可能會級聯到許多不同的依賴物件(如遊標)。失效對庫快取,共享池,行快取和CPU有很大影響,因為它們可能需要同時進行許多硬解析。

 

  • 大量併發

大併發會導致cursor: pin S wait on X爭用。

  • Known bugs

 2.2.4. AWR 分析


硬解析的次數非常低,排除硬解析過高的因素。
子游標版本最多173多,說明不多,不是子游標數量導致。
解析錯誤的也非常少,說明不是由於解析錯誤導致。

     可以看出故障時間點,sga各個元件在動態調整。

2.2.5 深入分析

從trc裡分析出所有的cursor: pin S wait on X等待的阻塞源頭都是SID:737會話,發現737是oracle@pmjxpdbb (MMAN)會話,MMAN程式是Oracle 10g引入用於進行記憶體管理的程式,在進行動態記憶體調整時,這個程式要發揮其作用。
等到鏈都指向了源頭SGA: allocation forcing component growth。

SGA元件中KGH: NO ACCESS持續變大 ,KGLH0、SQLA持續變小,KGH: NO ACCESS表示緩衝區快取和共享池之間的部分傳輸,正是由於記憶體元件的調整,latch: shared pool被爭用,造成了大量的cursor: pin S wait on X等待。
3.解決方案

1、增大shared_pool_size 的最小值90G(SGA 600G*15%),或者採用手工記憶體管理的方式

根據Best Practices and Recommendations for RAC databases with SGA size over 100GB (Doc ID 1619155.1)

2、縮小buffer cache大小,可以減小gcs resources、gcs shadows元件的大小。

2、最佳化TOP SQL,尤其是全表掃描大量物理讀的sql。


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

相關文章