ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SGA_TUNNING

SGA_TUNNING

原创 Linux操作系统 作者:golden_zhou 时间:2011-03-10 10:30:47 0 删除 编辑

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';

 

 

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

上一篇: SGA_TUNNING
下一篇: IO_TUNNING
请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    301944