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.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1017072/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18921899/viewspace-1017072/