二, 監(jiān)控sql語句的重載率

Sql的重載率, 就是相同的語句, 由于無法使用共享池里已經(jīng)保存的執(zhí)行計(jì)劃而不得不重新將代碼載入后執(zhí)行分析,建立查詢樹后再進(jìn)行執(zhí)行的一個(gè)過程.

極端糟糕的情況下, 重載率可能接近于1 , 就是說,每一個(gè)語句都需要載入后重新執(zhí)行.

2.1 oracle庫緩存

Oracle的庫緩存是內(nèi)存的一個(gè)區(qū)域, 是共享池里的三個(gè)組成部分之一. 庫緩存由共享SQL工作區(qū), PL/SQL包和過程, 不同的鎖和句柄組成. 每當(dāng)有應(yīng)用程序要執(zhí)行sql或pl/sql語句時(shí), 這些代碼必須先暫存在oracle的庫緩存中. 當(dāng)應(yīng)用程序運(yùn)行代碼時(shí), oracle會(huì)先搜索庫緩存看該代碼是否已經(jīng)存在于內(nèi)存中. 如果代碼已經(jīng)寫入內(nèi)存中, oracle就可以重新使用該已存代碼(也稱為軟解析). 如果內(nèi)存里找不到該代碼, oracle 必須將代碼載入到內(nèi)存中(也稱為硬解析或庫緩存不命中).

系統(tǒng)會(huì)給一個(gè)已配置的庫緩存工作區(qū)分配了一定的內(nèi)存量, 當(dāng)內(nèi)存耗盡時(shí), 會(huì)自動(dòng)從內(nèi)存中刪除一些不常用的代碼, 以便騰出一定空間來裝載應(yīng)用程序所需的代碼. 如果硬解析出現(xiàn)的次數(shù)太多, 我們可能需要增加分配給庫緩存的內(nèi)存容量.

動(dòng)態(tài)性能視圖V$librarycache中存儲(chǔ)自最近一次啟動(dòng)oracle數(shù)據(jù)庫之后到目前的庫緩存的性能情況, 我們可以查看這個(gè)視圖查看軟解析和硬解析的命中率情況.

字段

數(shù)據(jù)類型

說明

NAMESPACE

VARCHAR2(15)

library cache的命名空間

GETS

NUMBER

請(qǐng)求GET該命名空間中對(duì)象的次數(shù)。

GETHITS

NUMBER

請(qǐng)求GET并在內(nèi)存中找到了對(duì)象句柄的次數(shù)（鎖定命中）。

GETHITRATIO

NUMBER

請(qǐng)求GET的命中率。

PINS

NUMBER

請(qǐng)求pin住該命名中對(duì)象的次數(shù)。

PINHITS

NUMBER

庫對(duì)象的所有元數(shù)據(jù)在內(nèi)存中被找到的次數(shù)（pin命中）。

PINHITRATIO

NUMBER

Pin命中率。

RELOADS

NUMBER

Pin請(qǐng)求需要從磁盤中載入對(duì)象的次數(shù)。

INVALIDATIONS

NUMBER

命名空間中的非法對(duì)象（由于依賴的對(duì)象被修改所導(dǎo)致）數(shù)。

DLM_LOCK_REQUESTS

NUMBER

GET請(qǐng)求導(dǎo)致的實(shí)例鎖的數(shù)量。

DLM_PIN_REQUESTS

NUMBER

PIN請(qǐng)求導(dǎo)致的實(shí)例鎖的數(shù)量.

DLM_PIN_RELEASES

NUMBER

請(qǐng)求釋放PIN鎖的次數(shù)。

DLM_INVALIDATION_REQUESTS

NUMBER

GET請(qǐng)求非法實(shí)例鎖的次數(shù)。

DLM_INVALIDATIONS

NUMBER

從其他實(shí)例那的得到的非法pin數(shù)。

This view contains statistics about library cache performance and activity.

Column

Datatype

Description

NAMESPACE

VARCHAR2(15)

The library cache namespace

GETS

NUMBER

The number of times a lock was requested for objects of this namespace

GETHITS

NUMBER

The number of times an object's handle was found in memory

GETHITRATIO

NUMBER

The ratio of GETHITS to GETS

PINS

NUMBER

The number of times a PIN was requested for objects of this namespace

PINHITS

NUMBER

The number of times all of the metadata pieces of the library object were found in memory

PINHITRATIO

NUMBER

The ratio of PINHITS to PINS

RELOADS

NUMBER

Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk

INVALIDATIONS

NUMBER

The total number of times objects in this namespace were marked invalid because a dependent object was modified

DLM_LOCK_REQUESTS

NUMBER

The number of GET requests lock instance locks

DLM_PIN_REQUESTS

NUMBER

The number of PIN requests lock instance locks

DLM_PIN_RELEASES

NUMBER

The number of release requests PIN instance locks

DLM_INVALIDATION_REQUESTS

NUMBER

The number of GET requests for invalidation instance locks

DLM_INVALIDATIONS

NUMBER

The number of invalidation pings received from other instances

2.2 library cache的內(nèi)存結(jié)構(gòu)

2.2.1 library cache中存儲(chǔ)的信息

1, 按對(duì)象類型分類

共享游標(biāo)(SQL and PL/SQL objects)

數(shù)據(jù)庫對(duì)象(tables , indexes, procedures and so on)

2, 按存在時(shí)間分類

存儲(chǔ)對(duì)象: 如table, index, view等(老化后,磁盤上還有, 所以叫永久存儲(chǔ)對(duì)象)

瞬時(shí)對(duì)象: 如游標(biāo)(老化后就不見了)

2.2.2 library cache中如何存儲(chǔ)信息

圖2 library cache的內(nèi)存結(jié)構(gòu)

共享池內(nèi)存的結(jié)構(gòu), 是計(jì)算機(jī)常用的哈希表形式的擴(kuò)展. 常用的哈希表形式, 總是先有一個(gè)哈希表, 保存對(duì)象地址(或句柄), 然后,根據(jù)對(duì)象地址(或句柄)訪問對(duì)象.

表1 幾個(gè)基本概念

名稱

描述

地址

只有一個(gè)位置信息

句柄

處理位置,還包括一些其他相關(guān)信息

堆(heap)

程序開發(fā)者使用系統(tǒng)函數(shù)分配的內(nèi)存

棧

開發(fā)者在程序中定義的變量就存在棧

如圖2中, 每一組哈希值, 鏈表頭叫做一個(gè)哈希桶. . 簡單地說就是哈希桶(hash bucket)指向?qū)ο缶浔?object handles) , 對(duì)象句柄存有對(duì)象所占的堆內(nèi)存的地址.對(duì)象的堆往往不止一個(gè), oracle習(xí)慣稱這些堆為子堆 . 通常對(duì)象句柄中存有0號(hào)子堆的地址, 而0號(hào)子堆存有其他各個(gè)子堆的地址.

2.3 Library cache的pin與lock

所有在library cache中的對(duì)象, 都由兩部分組成, 一個(gè)句柄, 至少一個(gè)子堆.

句柄中記錄的有對(duì)象的名字, 命名空間, lock的持有者和等待者, pin的持有者和等待者, 一些標(biāo)志信息以及堆的地址.

在library cache中尋找對(duì)象時(shí), 先計(jì)算hash值, 在hash表中找到句柄, 再經(jīng)句柄, 找到對(duì)象實(shí)際的內(nèi)存地址(子堆). 在這個(gè)過程中, 有兩個(gè)重要數(shù)據(jù)項(xiàng)需要被鎖保護(hù)起來. 一個(gè)是對(duì)象句柄, 另一個(gè)就是對(duì)象的內(nèi)存堆. 在對(duì)象句柄上加的鎖就是library cache lock, 在內(nèi)存堆上加的鎖就是library cache pin.

2.3.1 library cache lock

Locks除了阻止不相容的對(duì)句柄的訪問, 以保護(hù)句柄中數(shù)據(jù)的完整性外, 獲得locks也是在緩存中定位對(duì)象的唯一方式, 即: 進(jìn)程在對(duì)句柄上加鎖的同時(shí), 完成在內(nèi)存中定位堆的操作. 在句柄上獲得lock, 餅子內(nèi)存中定位到堆后, 對(duì)象可以pin自己的堆. 如果對(duì)象相關(guān)信息不在內(nèi)存中, pinning一個(gè)對(duì)象將導(dǎo)致它和它的子堆被裝載(此種情況下, 如果是多個(gè)對(duì)象pin一個(gè)對(duì)象, 將可能造成pin等待).

Lock有三種模式

l???????? Share: 讀對(duì)象鎖

l???????? Exclusive: 修改或創(chuàng)建對(duì)象

l???????? Null: 專用于為會(huì)話持續(xù).

注意, 永久存儲(chǔ)對(duì)象可以被鎖在以上任意一種方式, 瞬時(shí)對(duì)象只能被鎖在null方式.

Null鎖在執(zhí)行sql聲明的解析階段被獲得, 此后一直持有. 它不阻止任何DDL, 也用屬于”易碎解析鎖”稱呼它.

以下兩種情況下null鎖被打碎:

l???????? 當(dāng)鎖所在對(duì)象有一個(gè)獨(dú)占pin時(shí).

l???????? 鎖所在對(duì)象的任何依賴對(duì)象有一個(gè)獨(dú)占pin時(shí)

Pin有兩種模式:

l???????? Share: 讀一個(gè)對(duì)象堆

l???????? Exclusive: 修改一個(gè)對(duì)象堆.

無論存儲(chǔ)對(duì)象還是瞬時(shí)對(duì)象, 都能被pinned在share或exclusive模式. 當(dāng)修改對(duì)象時(shí), 進(jìn)程會(huì)首先以share模式pin對(duì)象, 進(jìn)行錯(cuò)誤和安全檢查, 然后再以exclusive模式pin對(duì)象. Pin的解除將會(huì)導(dǎo)致相關(guān)對(duì)象上的易碎鎖break.

2.4 查看整個(gè)庫緩存的運(yùn)行情況

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

??? 上述代碼可以得到庫緩存的整體性能狀況. 其中pin為對(duì)子堆也就是對(duì)象的實(shí)際訪問或者叫執(zhí)行次數(shù), pinhits為執(zhí)行成功數(shù), reloads為嘗試執(zhí)行不在庫緩存里的代碼的次數(shù). Invalidations是指那些由于某種原因(特別是通過DDL操作),使得要執(zhí)行的代碼已經(jīng)失效從而需要重新載入解析代碼的次數(shù).

2.5 解決庫緩存造成的問題

庫緩存偶爾會(huì)給用戶帶來的麻煩通常源于各種鎖以及隨之而來的由鎖機(jī)制引發(fā)的以下等待事件:

庫緩存加載鎖: 當(dāng)有其他用戶端對(duì)同一對(duì)象使用了庫緩存加載鎖時(shí), 新來的客戶端必須等待先前的用戶將鎖釋放出來.

庫緩存鎖: 比如兩個(gè)用戶端想要同時(shí)編譯某段相同的代碼時(shí).

庫緩存pin: 這時(shí)意味著其他會(huì)話以不兼容模式鎖鎖定了該子堆.

不管庫緩存中出現(xiàn)了哪種類型的等待事件，想要確定哪些會(huì)話在等待以及在等待的是什么資源，可以通過V$SESSION_WAIT視圖查詢進(jìn)行診斷。例如，如果想要找出那些在等待“庫緩存pin”的會(huì)話，可以執(zhí)行以下的查詢語句。對(duì)于一個(gè)庫緩存pin來說，該查詢的關(guān)鍵部分是P1RAW字段，該字段給出了阻塞特定會(huì)話的對(duì)象的句柄地址。對(duì)于其他類型的等待事件，您可以參考Oracle數(shù)據(jù)庫的說明文檔，找出對(duì)應(yīng)于等待中的某對(duì)象或資源的P值。

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

然后我們可以執(zhí)行以下的查詢來找出正在等待哪些庫緩存對(duì)象;

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

要找出那些正在等待某個(gè)對(duì)象的用戶，可以使用DBA_WAITERS視圖并執(zhí)行以下查詢。這是一個(gè)非常簡單的查詢，卻可以很巧妙的找出阻塞的會(huì)話，也就是查找與上面從V$SESSION_WAIT查詢中找出的會(huì)話相匹配的等待會(huì)話，然后看看返回的holding_session結(jié)果。我們還可以看到在被阻塞的會(huì)話之后還有多少其他會(huì)話在等待中。如果有很多等待會(huì)話，那你就需要迅速采取行動(dòng)了。

SELECT?waiting_session,?holding_session?FROM?dba_waiters;

現(xiàn)在我們已經(jīng)確定了正在進(jìn)行中的會(huì)話和被等待對(duì)象，以及引發(fā)問題的會(huì)話及其SQL。那么接下來要如何解決出現(xiàn)的問題呢?如果等待事件持續(xù)的時(shí)間過長，那么庫緩存內(nèi)部很可能發(fā)生了錯(cuò)誤或故障。唯一的補(bǔ)救辦法就是殺死持有該鎖的所有進(jìn)程。在Oracle數(shù)據(jù)庫中要達(dá)到這個(gè)目的，可以使用alter system kill session命令。不過，這個(gè)命令是否有效還得看連接的類型。有時(shí)候需要用operating system kill命令或者關(guān)閉一系列應(yīng)用程序來終止連接。我們需要檢測庫緩存中完全鎖定狀態(tài)下，哪一個(gè)方法對(duì)系統(tǒng)更行之有效。至少在不得不關(guān)閉系統(tǒng)和數(shù)據(jù)庫之前，嘗試一下強(qiáng)迫殺死進(jìn)程的方法。

當(dāng)我們使用庫緩存時(shí)，只要記住它不過是在代碼執(zhí)行前，Oracle數(shù)據(jù)庫必須將這些代碼載入其中的內(nèi)存區(qū)。將代碼載入到庫緩存的過程可能會(huì)受到限制，從而引起等待事件，使系統(tǒng)掛起。這時(shí)候我們要通過殺死會(huì)話、進(jìn)程或修改代碼的方法快速確定導(dǎo)致系統(tǒng)掛起的SQL進(jìn)程，不過千萬不要忘記了庫緩存只是內(nèi)存的事實(shí)，我們可能只是需要給引發(fā)問題的部分重新分配一些內(nèi)存，使Oracle更有效地運(yùn)行而已。