oracle 内存建议

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