Oracle内存中的几个重要监控指标

最近在研究一些监控oracle数据库性能指标的参数，几个重要的命中率描述如下：

1.Library Cache Hit Ratio
  Library Cache Hit Ratio = sum(pinhits) / sum(pins)
  note:
  pinhits:The number of times all of the metadata pieces of the library object were found in memory
  pins:The number of times a PIN was requested for objects of this namespace

2.Dictionary Cache Hit Ratio
  SELECT parameter , sum(gets) , sum(getmisses) , 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates
  FROM V$ROWCACHE
  WHERE gets > 0
  GROUP BY parameter;
  total:
  SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
  note:
  gets:Shows the total number of requests for information on the corresponding item. For example, in the row that contains
       statistics for file descriptions, this column has the total number
       of requests for file description data.
  getmisses:Shows the number of data requests which were not satisfied by the cache, requiring an I/O.
  FIXED:Number of fixed entries in the cache

3.Buffer Pool Hit Ratios
  SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
         1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
  FROM V$BUFFER_POOL_STATISTICS;
  NOTE:
  PHYSICAL_READS:Physical reads statistic
  DB_BLOCK_GETS:Database blocks gotten statistic
  CONSISTENT_GETS:Consistent gets statistic

4.表空间碎片程度
  由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用 FSFI--Free Space Fragmentation Index （自由空间碎片索引）值来直观体现：
  FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents)))
  可以看出， FSFI 的最大可能值为 100 （一个理想的单文件表空间）。随着范围的增加， FSFI 值缓慢下降，而随着最大范围尺寸的减少， FSFI 值会迅速下降。
  下面的脚本可以用来计算 FSFI 值：
   select tablespace_name,sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))) FSFI
   from dba_free_space
   group by tablespace_name order by 1;

   碎片程度
   select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
   having count(tablespace_name)>10;
  
   alter tablespace name coalesce;
   alter table name deallocate unused;
  
   create or replace view ts_blocks_v as
   select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
   union all
   select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
  
   select * from ts_blocks_v;
  
   select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
   group by tablespace_name;
  
   查看碎片程度高的表
   SELECT segment_name table_name , COUNT(*) extents
   FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
   HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

5.查回滚段的基本信息
  select segment_name,
  to_char(initial_extent) initial_extent,next_extent,
  to_char(min_extents)  min_extents,owner,
  to_char(pct_increase)  pct_increase,
  dba_rollback_segs.status status,optsize
  from dba_rollback_segs,v$rollstat
  where dba_rollback_segs.segment_id=v$rollstat.usn;

  查回滚段的使用率
  select n.name,s.usn,s.wraps, decode(s.waits,0,1,1-s.waits/s.gets) "RBS header get ratio"
  from v$rollstat s,v$rollname n
  where s.usn=n.usn;

参考文献：
B14211-03
Performance Tuning Guide
10g Release 2 (10.2)
http://rainnyzhong.bokee.com/5700733.html
http://hi.baidu.com/wanhh/blog/item/ebb1202a4f57da3a5343c18b.html
http://publib.boulder.ibm.com/tividd/td/ITMD/SC23-4724-00/en_US/HTML/oraclepac510rg41.htm

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

转载于:http://blog.itpub.net/9252210/viewspace-604135/