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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-242110/