我们执行查询或相关操作时，数据回传的响应时间是我们评定性能好坏的标准，当然这些要根据具体情况来定，通常如果我们所需要的数据就在memory中存着，拿来就用，那么一定会比再从Disk中读出来快很多倍。如果我们想知道当前各session查询的数据在memory中的命中程度可以用下面的SQL来实现

select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS + BLOCK_GETS - PHYSICAL_READS) /
(CONSISTENT_GETS + BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS + BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS + BLOCK_GETS - PHYSICAL_READS) /
(CONSISTENT_GETS + BLOCK_GETS))

Username - Name of the user

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

DB Blk Gets - The number of blocks accessed via single block gets

Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.

The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

Hit ratio should be > 90%

ITPub博客

SQL--如何计算每个user存取数据在memory的命中情况

最新文章