Oracle数据缓冲区优化.doc

《Oracle数据缓冲区优化.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、Oracle数据缓冲区优化1.优化缓冲区大小、提高服务器的命中率db_cache_size                       biginteger8388608002.查看缓冲区命中率是否需要调优.select1-((physical.value-direct.value-lobs.value)/logical.value)    "BufferCacheHitRatio" fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogi

2、cal wherephysical.name='physicalreads' anddirect.name='physicalreadsdirect' andlobs.name='physicalreadsdirect(lob)' andlogical.name='sessionlogicalreads';当命中率>90%说明命中率很高了3。获取推荐的值selectname,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READSfromv$db_cache_

3、advice whereblock_size='8192'andadvice_status='ON';SQL>setlinesize1000SQL>selectsize_for_estimate,buffers_for_estimate,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READSfromv$db_cache_advice    whereblock_size='8192'andadvice_status='ON';SIZE_FOR_ESTIMATEBUFFERS_F

4、OR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------              80                9925                  28.9757         1436311200             160               19850       

5、            2.1053          104360120             240               29775                   1.5819           78413087             320               39700                   1.4262           70693980             400               49625                   1.3543 

6、          67131735             480               59550                    1.278           63349434             560               69475                   1.1893           58954568             640               79400                   1.1325           56135206 

7、            720               89325                   1.0762           53347837             800               99250                        1           49569438             880              109175                    .7067           35030953SIZE_FOR_ESTIMATEBUF

8、FERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------             960              119100