ORACLE SGA调优

/*1)Library Cache的命中率:
.计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数

数据：
*/
/*
二.共享池有三个组件组成,Library Cache(库高速缓存区),
Data Dictionary Cache(数据目录高速缓存区),User Global Area(用户全局区)
*/
SELECT SUM(pinhits) / sum(pins) FROM V$LIBRARYCACHE;

/*
alter system set shared_pool_size = 10;*/

/*
2.计算共享池内存使用率:
其中: &TSP_IN_M是你的总的共享池的SIZE(M)
共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。
查询空闲的共享池内存:

? 共享池 是哪个
*/

SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'
  FROM V$SGASTAT
 WHERE NAME = 'free memory'
   AND POOL = 'shared pool';

/*
3.db buffer cache命中率:
alter system set DB_CACHE_SIZE = ?;
计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)
通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE
数据：
*/

SELECT NAME,
       PHYSICAL_READS,
       DB_BLOCK_GETS,
       CONSISTENT_GETS,
       1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
  FROM V$BUFFER_POOL_STATISTICS
 WHERE NAME = 'DEFAULT';

/*
4)数据缓冲区命中率：

这里命中率的计算应该是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

？数据缓冲区怎么加大 db_block_buffers

*/

SELECT NAME, VALUE
  FROM V$SYSSTAT
 WHERE NAME IN
       ('session logical reads', 'physical reads', 'physical reads direct',
        'physical reads direct (lob)', 'db block gets', 'consistent gets');

/*select 100 -
       (884445 - (550 + 134160)) / (431318737 + 7720764 - (550 + 134160)) * 100
  from dual;
*/

/*5.共享池的命中率：*/
select sum(pinhits - reloads) / sum(pins) * 100 "hit radio"
  from v$librarycache;

SELECT * FROM v$sysstat t WHERE NAME = 'sorts (memory)';
--查询内存排序数
SELECT * FROM v$sysstat t WHERE NAME = 'sorts (disk)';
--查询磁盘排序数

/*
8)共享区字典缓存区命中率
计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)
命中率应大于0.85
*/

select sum(gets - getmisses - usage - fixed) / sum(gets) from v$rowcache;

/*
9)数据高速缓存区命中率
计算公式：1-(physical reads / (db block gets + consistent gets))
命中率应大于0.90最好
*/

select name, value
  from v$sysstat
 where name in ('physical reads', 'db block gets', 'consistent gets');

/*
10)共享区库缓存区命中率
计算公式：SUM(pins - reloads) / SUM(pins)
命中率应大于0.99
*/

select sum(pins-reloads)/sum(pins)  from v$librarycache;