Oracle -----BUFFER CACHE---- 常用查询

--查看buffer cache 命中率
select 1 - (sum(decode(name, 'physical reads', value, 0)) /
        (sum(decode(name, 'db block gets', value, 0)) +
        (sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"
   from v$sysstat;

  select name,
         physical_reads,
         (consistent_gets + db_block_gets) logic_reads,
         1 - (physical_reads) / (consistent_gets + db_block_gets) hit_radio
    from v$buffer_pool_statistics;

--查看buffer cache建议  
     select size_for_estimate,
            estd_physical_read_factor,
            to_char(estd_physical_reads, 99999999999999999999999) as"estd_physical_reads"
       from v$db_cache_advice
     where name = 'DEFAULT';
  
--查看buffer cache建议   --适用于指定SGA的目的
     COL pool FORMAT a10;
     SELECT (SELECT ROUND(value / 1024 / 1024, 0)
               FROM v$parameter
              WHERE name = 'db_cache_size') "Current Cache(Mb)",
            name "Pool",
            size_for_estimate "Projected Cache(Mb)",
            ROUND(100 - estd_physical_read_factor, 0) "Cache Hit Ratio%"
       FROM v$db_cache_advice
     WHERE block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
     ORDER BY 3;

--查看cache
     show parameter cache

--各种读取的统计
---Database read buffer cache hit ratio =
---1 – (physical reads / (db block gets + consistent gets))
  SELECT to_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN
  ('physical reads', 'db block gets', 'consistent gets');

  SELECT 'Database Buffer Cache Hit Ratio ' "Ratio"
          , ROUND((1-
          ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')
          / ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')
          + (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets')
          ))) * 100)||'%' "Percentage"
          FROM DUAL;