ITPub博客

首页 > 数据库 > Oracle > Oracle - Hit/Miss Ratios

Oracle - Hit/Miss Ratios

原创 Oracle 作者:nmtcolin 时间:2008-05-06 15:03:28 0 删除 编辑

Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

  • 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 (i.e. not through the consistent get mechanism).
  • 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 > 80%, else increase DB_BLOCK_BUFFERS in init.ora

    select 	sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
    	sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
    	sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
    	round((sum(decode(name, 'consistent gets',value, 0)) + 
    	       sum(decode(name, 'db block gets',value, 0)) - 
    	       sum(decode(name, 'physical reads',value, 0))) / 
    	      (sum(decode(name, 'consistent gets',value, 0)) + 
    	       sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
    from   v$sysstat
    
    

    Data Dict Hit Ratio

    DATA DICTIONARY HIT RATIO NOTES:

  • Gets - Total number of requests for information on the data object.
  • Cache Misses - Number of data requests resulting in cache misses

  • Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

    select 	sum(GETS),
    	sum(GETMISSES),
    	round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
    from 	v$rowcache
    
    

    SQL Cache Hit Ratio

    SQL CACHE HIT RATIO NOTES:

  • Pins - The number of times a pin was requested for objects of this namespace.
  • Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

  • Hit Ratio should be > 85%

    select 	sum(PINS) Pins,
    	sum(RELOADS) Reloads,
    	round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
    from 	v$librarycache
    
    

    Library Cache Miss Ratio

    LIBRARY CACHE MISS RATIO NOTES:

  • Executions - The number of times a pin was requested for objects of this namespace.
  • Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

  • Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

    select 	sum(PINS) Executions,
    	sum(RELOADS) cache_misses,
    	sum(RELOADS) / sum(PINS) miss_ratio
    from 	v$librarycache
    
    http://vsbabu.org/oracle/sect05.html
  • [@more@]

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

    请登录后发表评论 登录
    全部评论
    radarlab 金融其实很简单!

    注册时间:2009-02-10

    • 博文量
      31
    • 访问量
      602304