Oracle-監控sql語句的過載率

Oracle-監控sql語句的過載率

---

Sql的過載率, 就是相同的語句, 由於無法使用共享池裡已經儲存的執行計劃而不得不重新將程式碼載入後執行分析,建立查詢樹後再進行執行的一個過程.
極端糟糕的情況下, 過載率可能接近於1 , 就是說,每一個語句都需要載入後重新執行.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 oracle庫快取
Oracle的庫快取是記憶體的一個區域, 是共享池裡的三個組成部分之一. 庫快取由共享SQL工作區, PL/SQL包和過程, 不同的鎖和控制程式碼組成.
每當有應用程式要執行sql或pl/sql語句時, 這些程式碼必須先暫存在oracle的庫快取中. 當應用程式執行程式碼時, oracle會先搜尋庫快取看該程式碼是否已經存在於記憶體中.
如果程式碼已經寫入記憶體中, oracle就可以重新使用該已存程式碼(也稱為軟解析). 如果記憶體裡找不到該程式碼, oracle 必須將程式碼載入到記憶體中(也稱為硬解析或庫快取不命中).
系統會給一個已配置的庫快取工作區分配了一定的記憶體量, 當記憶體耗盡時, 會自動從記憶體中刪除一些不常用的程式碼, 以便騰出一定空間來裝載應用程式所需的程式碼.
如果硬解析出現的次數太多, 我們可能需要增加分配給庫快取的記憶體容量.

動態效能檢視V$librarycache中儲存自最近一次啟動oracle資料庫之後到目前的庫快取的效能情況, 我們可以檢視這個檢視檢視軟解析和硬解析的命中率情況.
欄位    資料型別    說明
NAMESPACE    VARCHAR2(15)    library cache的名稱空間
GETS    NUMBER    請求GET該名稱空間中物件的次數。
GETHITS    NUMBER    請求GET並在記憶體中找到了物件控制程式碼的次數（鎖定命中）。
GETHITRATIO    NUMBER    請求GET的命中率。
PINS    NUMBER    請求pin住該命名中物件的次數。
PINHITS    NUMBER    庫物件的所有後設資料在記憶體中被找到的次數（pin命中）。
PINHITRATIO    NUMBER    Pin命中率。
RELOADS    NUMBER    Pin請求需要從磁碟中載入物件的次數。
INVALIDATIONS    NUMBER    名稱空間中的非法物件（由於依賴的物件被修改所導致）數。
DLM_LOCK_REQUESTS    NUMBER    GET請求導致的例項鎖的數量。
DLM_PIN_REQUESTS    NUMBER    PIN請求導致的例項鎖的數量.
DLM_PIN_RELEASES    NUMBER    請求釋放PIN鎖的次數。
DLM_INVALIDATION_REQUESTS    NUMBER    GET請求非法例項鎖的次數。
DLM_INVALIDATIONS    NUMBER    從其他例項那的得到的非法pin數。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2 library cache的記憶體結構

2.1 library cache中儲存的資訊
1, 按物件型別分類
共享遊標(SQL and PL/SQL objects)
資料庫物件(tables , indexes, procedures and so on)
2, 按存在時間分類
儲存物件: 如table, index, view等(老化後,磁碟上還有, 所以叫永久儲存物件)
瞬時物件: 如遊標(老化後就不見了)


2.2 library cache中如何儲存資訊
共享池記憶體的結構, 是計算機常用的雜湊表形式的擴充套件. 常用的雜湊表形式, 總是先有一個雜湊表, 儲存物件地址(或控制程式碼), 然後,根據物件地址(或控制程式碼)訪問物件.
表1 幾個基本概念
名稱    描述
地址    只有一個位置資訊
控制程式碼    處理位置,還包括一些其他相關資訊
堆(heap)    程式開發者使用系統函式分配的記憶體
棧    開發者在程式中定義的變數就存在棧
如圖2中, 每一組雜湊值, 連結串列頭叫做一個雜湊桶.  . 簡單地說就是雜湊桶(hash bucket)指向物件控制程式碼(object handles)  ,
物件控制程式碼存有物件所佔的堆記憶體的地址.物件的堆往往不止一個, oracle習慣稱這些堆為子堆 . 通常物件控制程式碼中存有0號子堆的地址, 而0號子堆存有其他各個子堆的地址.


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3 Library cache的pin與lock
所有在library cache中的物件, 都由兩部分組成, 一個控制程式碼, 至少一個子堆.
控制程式碼中記錄的有物件的名字, 名稱空間, lock的持有者和等待者, pin的持有者和等待者, 一些標誌資訊以及堆的地址.
在library cache中尋找物件時, 先計算hash值, 在hash表中找到控制程式碼, 再經控制程式碼, 找到物件實際的記憶體地址(子堆). 在這個過程中, 有兩個重要資料項需要被鎖保護起來.
一個是物件控制程式碼, 另一個就是物件的記憶體堆. 在物件控制程式碼上加的鎖就是library cache lock, 在記憶體堆上加的鎖就是library cache pin.

3.1 library cache lock
Locks除了阻止不相容的對控制程式碼的訪問, 以保護控制程式碼中資料的完整性外, 獲得locks也是在快取中定位物件的唯一方式, 即: 程式在對控制程式碼上加鎖的同時, 完成在記憶體中定位堆的操作.
在控制程式碼上獲得lock, 餅子記憶體中定位到堆後, 物件可以pin自己的堆. 如果物件相關資訊不在記憶體中, pinning一個物件將導致它和它的子堆被裝載(此種情況下,
如果是多個物件pin一個物件, 將可能造成pin等待).

Lock有三種模式
?    Share: 讀物件鎖
?    Exclusive: 修改或建立物件
?    Null: 專用於為會話持續.
注意, 永久儲存物件可以被鎖在以上任意一種方式, 瞬時物件只能被鎖在null方式.
Null鎖在執行sql宣告的解析階段被獲得, 此後一直持有. 它不阻止任何DDL, 也用屬於”易碎解析鎖”稱呼它.
以下兩種情況下null鎖被打碎:
?    當鎖所在物件有一個獨佔pin時.
?    鎖所在物件的任何依賴物件有一個獨佔pin時
Pin有兩種模式:
?    Share: 讀一個物件堆
?    Exclusive: 修改一個物件堆.
無論儲存物件還是瞬時物件, 都能被pinned在share或exclusive模式. 當修改物件時, 程式會首先以share模式pin物件, 進行錯誤和安全檢查,
然後再以exclusive模式pin物件. Pin的解除將會導致相關物件上的易碎鎖break.


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 檢視整個庫快取的執行情況

 select sum(pins) pins,
       sum(pinhits) pinhits,
       sum(reloads) reloads,
       sum(invalidations) invalidations,
       100-(sum(pinhits)/sum(pins)) *100 reparsing
 from v$librarycache;

    上述程式碼可以得到庫快取的整體效能狀況. 其中
    pin為對子堆也就是物件的實際訪問或者叫執行次數,
    pinhits為執行成功數,
    reloads為嘗試執行不在庫快取裡的程式碼的次數.
    Invalidations是指那些由於某種原因(特別是透過DDL操作),使得要執行的程式碼已經失效從而需要重新載入解析程式碼的次數.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
5 解決庫快取造成的問題
庫快取偶爾會給使用者帶來的麻煩通常源於各種鎖以及隨之而來的由鎖機制引發的以下等待事件:
庫快取載入鎖: 當有其他使用者端對同一物件使用了庫快取載入鎖時, 新來的客戶端必須等待先前的使用者將鎖釋放出來.
庫快取鎖: 比如兩個使用者端想要同時編譯某段相同的程式碼時.
庫快取pin: 這時意味著其他會話以不相容模式鎖鎖定了該子堆.
不管庫快取中出現了哪種型別的等待事件，想要確定哪些會話在等待以及在等待的是什麼資源，可以透過V$SESSION_WAIT檢視查詢進行診斷。

例如，如果想要找出那些在等待“庫快取pin”的會話，可以執行以下的查詢語句。對於一個庫快取pin來說，該查詢的關鍵部分是P1RAW欄位，
該欄位給出了阻塞特定會話的物件的控制程式碼地址。對於其他型別的等待事件，您可以參考Oracle資料庫的說明文件，找出對應於等待中的某物件或資源的P值。

SELECT sid,event,p1raw
FROM sys.v_$session_wait
WHERE event = 'library cache pin'
AND state = 'WAITING';

然後我們可以執行以下的查詢來找出正在等待哪些庫快取物件;

SELECT kglnaown AS owner, kglnaobj as Object
FROM sys.x$kglob
WHERE kglhdadr='&P1RAW';

要找出那些正在等待某個物件的使用者，可以使用DBA_WAITERS檢視並執行以下查詢。這是一個非常簡單的查詢，卻可以很巧妙的找出阻塞的會話，
也就是查詢與上面從V$SESSION_WAIT查詢中找出的會話相匹配的等待會話，然後看看返回的holding_session結果。我們還可以看到在被阻塞的會話之後
還有多少其他會話在等待中。如果有很多等待會話，那你就需要迅速採取行動了。

SELECT waiting_session, holding_session FROM dba_waiters;

現在我們已經確定了正在進行中的會話和被等待物件，以及引發問題的會話及其SQL。那麼接下來要如何解決出現的問題呢?如果等待事件持續的時間過長
那麼庫快取內部很可能發生了錯誤或故障。唯一的補救辦法就是殺死持有該鎖的所有程式。在Oracle資料庫中要達到這個目的，
可以使用alter system kill session命令。不過，這個命令是否有效還得看連線的型別。有時候需要用operating system kill命令或者關閉一系列應用程式來終止連線。
我們需要檢測庫快取中完全鎖定狀態下，哪一個方法對系統更行之有效。至少在不得不關閉系統和資料庫之前，嘗試一下強迫殺死程式的方法。
當我們使用庫快取時，只要記住它不過是在程式碼執行前，Oracle資料庫必須將這些程式碼載入其中的記憶體區。將程式碼載入到庫快取的過程可能會受到限制，
從而引起等待事件，使系統掛起。這時候我們要透過殺死會話、程式或修改程式碼的方法快速確定導致系統掛起的SQL程式，不過千萬不要忘記了庫快取只是記憶體的事實，
我們可能只是需要給引發問題的部分重新分配一些記憶體，使Oracle更有效地執行而已。