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://hi.baidu.com/wanhh/blog/item/ebb1202a4f57da3a5343c18b.html