oracle的比率(1)

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.