1、Library Cache命中率 > 99%

select 1-(sum(reloads)/sum(pins)) "Library cache Hit Ratio" from v$librarycache;

alter system flush shared_pool;

alter system set shared_pool_size=设定值 scope=spfile;

2、Data Buffer（数据缓冲区）命中率 > 90%

检查方式:

select 1- (phy.value / (cur.value + con.value))  "HIT RATIO"

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

alter system set db_cache_size=设定值 scope=spfile

3、Dictionary Cache命中率: > 95%

select 1 - (sum(getmisses) / sum(gets)) "Data Dictionary Hit Ratio" from v$rowcache;

alter system flush shared_pool;

alter system set shared_pool_size=设定值 scope=spfile;

4、Log Buffer命中率: < 1%

     select (req.value * 5000) / entries.value "Ratio" from v$sysstat req, v$sysstat entries where req.name = 'redo log space requests' and entries.name = 'redo entries';

如果Ratio高于1％，应调高log_buffer的大小。

alter system set log_buffer=设定值 scope=spfile;

5、undo_retention 的值必须大于max(maxquerylen)的值

col undo_retention format a30

select value "undo_retention" from v$parameter where name='undo_retention';

select max(maxquerylen) from v$undostat Where begin_time>sysdate-(1/4);

alter system set undo_retention= 设定值 scope=spfile;

获取自数据库创建以来分配给pga使用的最大内存值

select value from v$pgastat where name='maximum PGA allocated' ;

memory_target = sga_target + max(pga_aggregate_target,'maximum PGA allocated' )

只有当建议器打开（隐含参数_smm_advice_enabled为TRUE），并且参数STATISTICS_LEVEL值不是BASIC时，视图中才会有内容。

查询隐含参数_smm_advice_enabled是否为TRUE：

SELECT  x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

FROM  SYS.x$ksppi  x,  SYS.x$ksppcv y WHERE  x.inst_id= USERENV('Instance')

AND  y.inst_id=USERENV('Instance')  AND  x.indx= y.indx AND x.ksppinm LIKE '%_smm_advice_enabled%';

alter system set “_smm_advice_enabled” =TRUE;

alter system set statistics_level=typical;

select sga_size,sga_size_factor,estd_db_time,estd_db_time_factor,estd_physical_reads from V$SGA_TARGET_ADVICE;

***物理读次数一直维持在10828，则没有性能上的提升了

SELECT   pga_target_for_estimate / 1024 / 1024 "PGA(MB)",

             pga_target_factor,

             estd_pga_cache_hit_percentage,

             estd_overalloc_count

      FROM   v$pga_target_advice;

estd_pga_cache_hit_percentage PGA的估算得到的Cache命中率的百分比

目前系统如果PGA为20M的时候，就可以达到100%的命中率****

https://blog.csdn.net/cuiruipan6325/article/details/100463286