1. 監控SGA的命中率 (data buffer)

SELECT   a.VALUE + b.VALUE "logical_reads",
         c.VALUE "phys_reads",
         ROUND (100 * ( (a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE + b.VALUE))
            "BUFFER HIT RATIO"
  FROM   v$sysstat a, v$sysstat b, v$sysstat c
 WHERE   a.statistic# = 38 AND b.statistic# = 39 AND c.statistic# = 40;

2.監控SGA中字典緩沖區的命中率

  SELECT   parameter,
           gets,
           Getmisses,
           getmisses / (gets + getmisses) * 100 "miss ratio",
           (1 - (SUM (getmisses) / (SUM (gets) + SUM (getmisses)))) * 100
              "Hit ratio"
    FROM   v$rowcache
   WHERE   gets + getmisses <> 0
GROUP BY   parameter, gets, getmisses;

3. 監控SGA中共享緩存區的命中率,應該小於1%

SELECT   SUM (pins) "Total Pins",
         SUM (reloads) "Total Reloads",
         SUM (reloads) / SUM (pins) * 100 libcache
  FROM   v$librarycache;

SELECT   SUM (pinhits - reloads) / SUM (pins) "hit radio",
         SUM (reloads) / SUM (pins) "reload percent"
  FROM   v$librarycache;

4. 監控SGA中重做日志緩沖區的命中率,應該小於1%

SELECT   name,
         gets,
         misses,
         immediate_gets,
         immediate_misses,
         DECODE (gets, 0, 0, misses / gets * 100) ratio1,
         DECODE (
            immediate_gets + immediate_misses,
            0,
            0,
            immediate_misses / (immediate_gets + immediate_misses) * 100
         )
            ratio2
  FROM   v$latch
 WHERE   name IN ('redo allocation', 'redo copy');

SELECT   SUM (pinhits) / SUM (pins) * 100 "shared pool hit radio",
         SUM (reloads) / SUM (pins) "reload percent"
  FROM   v$librarycache;

5. 監控字典緩沖區

SELECT   (SUM (PINS - RELOADS)) / SUM (PINS) "LIB CACHE" FROM V$LIBRARYCACHE;

SELECT   (SUM (GETS - GETMISSES - USAGE - FIXED)) / SUM (GETS) "ROW CACHE"
  FROM   V$ROWCACHE;

SELECT   SUM (PINS) "EXECUTIONS",
         SUM (RELOADS) "CACHE MISSES WHILE EXECUTING"
  FROM   V$LIBRARYCACHE;

後者除以前者,此比率小於1%,接近0%為好

6. 看內存緩沖區使用效率的指數是命中率HITS:
Hits=Logical_reads/(logical_reads+physical_reads) 其中:logical_reads=db_block_gets+consistent_reads

SELECT   cur.VALUE db,
         con.VALUE con,
         phy.VALUE phy,
         (cur.VALUE + con.VALUE) / (cur.VALUE + con.VALUE + phy.VALUE) * 100
            HITS
  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';