Oracle数据缓冲区优化

Oracle数据缓冲区优化1.优化缓冲区大小、提高服务器的命中率db_cache_sizebiginteger8388608002.查看缓冲区命中率是否需要调优.select1-((physical.value-direct.value-lobs.value)/logical.value)"BufferCacheHitRatio"fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$='physicalreads'='physicalreadsdirect'='physicalreadsdirect(lob)'='sessionlogicalreads';当命中率>90%说明命中率很高了3。获取推荐的值selectname,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READSfromv$db_cache_advicewhereblock_size='8192'andadvice_status='ON';SQL>setlinesize1000SQL>selectsize_for_estimate,buffers_for_estimate,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READSfromv$db_cache_advicewhereblock_size='8192'andadvice_status='ON';SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------80992528.975714363112001043601207841308770693980671317356334943458954568561352065334783749569438350309531602403204004805606407208008801985029775397004962559550694757940089325992501091752.10531.58191.42621.35431.2781.18931.13251.07621.7067SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------96010401120120012801360144015201600119100129025138950148875158800168725178650188575198500.3991.2305.1927.1506.1501.1501.1501.1501.150119784701114233749552903746627874381867438186743818674381867438186SIZE_FOR_ESTIMATEM为单位：当SIZE_FOR_ESTIMATE=80M的时候ESTD_PHYSICAL_READS=1436311200当SIZE_FOR_ESTIMATE=1120M的时候ESTD_PHYSICAL_READS=9552903当SIZE_FOR_ESTIMATE=1280M的时候ESTD_PHYSICAL_READS=7438186之后ESTD_PHYSICAL_READS固定了所以应该过大db_cache_size=1120M的值使得4.修改发现DB_cache_size太大了。过大SGA区域解决altersystemsetdb_cache_size=1120M--sga设置太小了导致SQL>altersystemsetdb_cache_size=1120M;altersystemsetdb_cache_size=1120M*ERROR位于第1行:ORA-02097:无法修改参数，因为指定的值无效ORA-00384:没有足够的内存来增加高速缓存的大小之前的值sga_max_sizebiginteger1494715120SQL>altersystemsetSGA_MAX_SIZE=3500Mscope=spfile;系统已更改。重新启动与关闭解决SQL>shutdownimmediate数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。SQL>startupORACLE例程已经启动。TotalSystemGlobalArea3675756336bytesFixedSize735024bytes2835349504bytes838860800bytes811008bytesVariableSizeDatabaseBuffersRedoBuffers数据库装载完毕。数据库已经打开。查看命中率\当前只有80的满足要求SQL>selectsize_for_estimate,buffers_for_estimate,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READSfromv$db_cache_advice2whereblock_size='8192'andadvice_status='ON';SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------8099251152816024032040048056064072080088019850297753970049625595506947579400893259925010917511111111111528152815281528152815281528152815281528SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------96010401120120012801191001290251389501488751588001111115281528152815281528136014401687251786501885751985001111152815281528152815201600命中率降低了？SQL>select1-((physical.value-direct.value-lobs.value)/logical.value)"BufferCacheHitRatio"23fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$='physicalreads'5='physicalreadsdirect'6='physicalreadsdirect(lob)'7='sessionlogicalreads';BufferCacheHitRatio----------------------.906673167继续缩小SGA大小SQL>altersystemsetSGA_MAX_SIZE=3000Mscope=spfile;SQL>shutdownimmediate数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。SQL>startupORACLE例程已经启动。TotalSystemGlobalArea3155661888bytesFixedSize734272bytes2315255808bytes838860800bytes811008bytesVariableSizeDatabaseBuffersRedoBuffers数据库装载完毕。数据库已经打开。selectsize_for_estimate,buffers_for_estimate,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READSfromv$db_cache_advicewhereblock_size='8192'andadvice_status='ON';继续测试：等待30分钟之后测试SQL>altersystemsetdb_cache_size=1120M;select1-((physical.value-direct.value-lobs.value)/logical.value)"BufferCacheHitRatio"fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$='physicalreads'='physicalreadsdirect'='physicalreadsdirect(lob)'='sessionlogicalreads';---命中率逐渐的提高了---半个小时之后查询命中率是98%SQL>select1-((physical.value-direct.value-lobs.value)/logical.value)2"BufferCacheHitRatio"3fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$='physicalreads'5='physicalreadsdirect'6='physicalreadsdirect(lob)'7='sessionlogicalreads';BufferCacheHitRatio----------------------.980309028-----查询推荐的值showparameterdb_block_size8192selectname,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READSfromv$db_cache_advicewhereblock_size='8192'andadvice_status='ON';NAMESIZE_FOR_ESTIMATEESTD_PHYSICAL_READS--------------------------------------------------------DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT1122243364485606727848961008958195149514951495149514951495149514DEFAULTDEFAULT1120123295149514NAMESIZE_FOR_ESTIMATEESTD_PHYSICAL_READS--------------------------------------------------------DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT134414561568168017921904201621282240951495149514951495149514951495149514显示只要112M大小的空间就可以稳定降低ESTD_PHYSICAL_READSaltersystemsetdb_cache_size=112MNAMESIZE_FOR_ESTIMATEESTD_PHYSICAL_READS--------------------------------------------------------DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT16000000324864809611212814416017600000NAMESIZE_FOR_ESTIMATEESTD_PHYSICAL_READS--------------------------------------------------------DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT1922082242