oracle的比率:

oracle的比率:

1、shared_pool命中率：

1.1、庫快取(Library cache)

GETS:

parse locks gets,在Library cache申請獲得該名稱空間物件的鎖的次數,gets級別

高於pins,必須先獲得鎖,然後執行pins。

GETHITS:

在庫快取中成功獲得物件鎖的次數。

GETHITRATIO:

反映在cache中成功獲得物件鎖的成功率,如果在cache中找不到所要的物件,

就會讀硬碟上的檔案並快取到cache中,按照LRU演算法換出一些最近最久不用

的資料頁,如果物件失效reload，這個值應該大於85%。這個比率低主要和應

用程式相關。

object has been locked exclusively or the application is infrequently referencing

objects。

PINS:

SQL語句執行的次數(read or change the contents of an object),indicates the

number of times that SQL statements, PL/SQL blocks and object definitions were

accessed for execution.

PINHITS:

Oracle對SQL語句以及編譯SQL語句後所得的虛擬碼(可理解為資料引擎可執

行程式碼)進行了相對應的快取。這樣在提交同樣的SQL語句時便不用再行編譯

一遍。

PINHITRATIO:

虛擬碼命中率反映日常系統事務處理語句在快取中找到對應虛擬碼的成功率,

如果找不到物件重新parse然後執行，如果編譯失效reload，這個值當然是越接近100%越好。

some reason that bring out this value is less than 85%,such as the application is

using unsharable SQL.

RELOADS:

如果執行語句由於庫快取物件失效或者編譯的無效就會導致過載。這個值越小

越好,SUM(RELOADS)/SUM(PINS)應小於 1%。如果該值過大,應該考慮增大

SHARED_POOL_SIZE。

Reloads indicate that library objects have to be reinitialized and reloaded with data

because they have been aged out or invalidated。

1.2、資料字典快取(data dictionary cache)：

GETS:

從資料字典快取請求獲得資源的次數。

GETMISSES:

沒有從資料字典快取中獲取資料的次數。

SUM(GETMISSES)/SUM(GETS):

SELECT SUM(GETMISSES)/SUM(GETS) FROM V$ROWCACHE。由於例項啟

動的時候,資料字典快取中沒有內容,因此註定了會發生GETMISSES情況,不能

指望GETMISSES為0。該比率要小於15%,如果該比率過大,考慮增加

SHARED_POOL_SIZE。

1.3、reference:

Within the shared pool, there are 2 types of data structures used for concurrency control:

locks (gets) and pins. A lock has a higher level than a pin and must be acquired before

attempting to pin an object. Locks can be thought of as parse locks while pins can be

thought of as short-term locks to read or change the contents of an object. We have

broken these out into seperate mechanisms in order to provide as much access to the

object as possible. After locking a library cache object, a process must then pin the

object before accessing it. It can be pinned in shared or exclusive mode depending on

whether the particular operation is read-only or not.

When there is a large number of gets and pins (over 1000) and the GetHitRatio and

PinHitRatio are low (less than 85%), the shared pool size needs to be increased. Also, it

is likely that the application is using unsharable SQL or infrequently referencing objects.

Reloads indicate that library objects have to be reinitialized and reloaded with data

because they have been aged out or invalidated. A high number of reloads can also

signal that the shared pool size needs to be increased.

The information in v$librarycache is primarily used to give an idea of total misses and

access attempts in the library cache. The sum(pins) indicates the number of times that

SQL statements, PL/SQL blocks and object definitions were accessed for execution.

The sum(reloads) indicates the number of times those executions resulted in library

cache misses causing Oracle to implicitly reparse a statement or reload an object

definition because it has been aged out or invalidated.

2、資料庫緩衝區(database buffer cache)命中率:

SELECT 1 - (phy.value / (cur.value + con.value)) "CACHE HIT RATIO"

FROM v$sysstat cur, v$sysstat con, v$sysstat phy

WHERE cur.name = 'db block gets' AND

con.name = 'consistent gets' AND

phy.name = 'physical reads';

反映在記憶體中獲得資料塊的百分比,要求大於90%,如果比率過小,考慮增加

DB_BLOCK_BUFFERS/SGA_TARGET/SGA_MAX_SIZE。

db block gets:對當前塊的訪問塊數,consistent gets:對讀一致性塊的訪問塊數,

physical reads:從物理檔案讀取得塊數,

db block gets+consistent gets構成了資料請求總數。

3、LRU命中率:

Least recently used,最近最少使用。Oracle用LRU機制維護資料庫緩衝區,使得最近

使用的塊存放在資料庫緩衝區,重新獲取資料的時候,可以直接從緩衝區中來獲得,

而不必進行I/O,從而提高效能。該命中率用來反映是否存在LRU latch(最近最少使

用閂)爭用。

SELECT name,1-sleeps/gets "LRU Hit%"

FROM v$latch

WHERE name='cache buffers lru chain';

‘cache buffers lru chain’：資料庫緩衝區的LRU鏈資訊。

SLEEPS：等待相應資源的次數。

GETS：獲取到相應資源的次數。

該值要求大於99%,如果該比率過低，考慮增加DB_BLOCK_LRU_LATCHES。

4.重做緩衝區分配嘗試統計資訊的比率(Redo buffer Allocation Retries Statistics Ratio):

該比率用來衡量聯機日誌緩衝區(Redo log buffer)的效能。

SELECT (RETRIES.VALUE/ENTRIES.VALUE)*100||'%'

FROM V$SYSSTAT RETRIES,V$SYSSTAT ENTRIES

WHERE RETRIES.NAME ='redo buffer allocation retries' AND

ENTRIES.NAME='redo entries';

redo buffer allocation retries:

使用者服務程式嘗試把新的聯機日誌條寫入日誌緩衝區時,如果這時後沒有可用

的緩衝區,就會試圖覆蓋日誌緩衝區的現有條目。但是當LGWR程式還沒有將

當前的條目寫入到聯機日誌檔案,使用者服務程式就必須等待,然後重新嘗試。該

統計資訊就是統計這種情況發生的次數。

redo entries:

該統計資訊反映了DML、DDL語句的重做資訊的總數。

該比率要求小於1%。如果該值過大，考慮增大redo file的size。

5.回退段等待次數/獲取次數比率：

用來診斷是否存在回退段頭(Rollback segment header)爭用的情況。

select sum(waits)*100 /sum(gets) "Ratio" from v$rollstat;

這個值越小越好，要求小於5%，如果該值過大考慮建立更多的回退段。

6.磁碟排序與記憶體排序之比:

用來反映語句排序效能情況。很多語句需要排序,例如: DISTINCT、ORDER BY、

GROUP BY、UNION等等。在硬碟中進行的排序越少,效能越好。

select (disk.value/mem.value)*100 "Ratio"

from v$sysstat mem, v$sysstat disk

where mem.name = 'sorts (memory)' and disk.name = 'sorts (disk)';

該值要求小於5%，如果值過大考慮增加SORT_AREA_SIZE。

/**************************************/

select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;

包含了 第一次 解析sql的時候，肯定不命中

select sum(pins-reloads)/sum(pins) from v$librarycache;

已經解析好的sql被 ageout 了，偏低暗示著記憶體嚴重不足

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