Oracle数据缓冲区优化

Oracle数据缓冲区优化1.优化缓冲区大小、提高服务器的命中率db_cache_sizebiginteger8388608002.查看缓冲区命中率是否需要调优.select1-((physical.value-direct.value-lobs.value)/logical.value)BufferCacheHitRatiofromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogicalwherephysical.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_advicewhereblock_size='8192'andadvice_status='ON';SQLsetlinesize1000SQLselectsize_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.97571436311200160198502.1053104360120240297751.581978413087320397001.426270693980400496251.354367131735480595501.27863349434560694751.189358954568640794001.132556135206720893251.07625334783780099250149569438880109175.706735030953SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------960119100.3991197847011040129025.2305114233741120138950.192795529031200148875.150674662781280158800.150174381861360168725.150174381861440178650.150174381861520188575.150174381861600198500.15017438186SIZE_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设置太小了导致SQLaltersystemsetdb_cache_size=1120M;altersystemsetdb_cache_size=1120M*ERROR位于第1行:ORA-02097:无法修改参数，因为指定的值无效ORA-00384:没有足够的内存来增加高速缓存的大小之前的值sga_max_sizebiginteger1494715120SQLaltersystemsetSGA_MAX_SIZE=3500Mscope=spfile;系统已更改。重新启动与关闭解决SQLshutdownimmediate数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。SQLstartupORACLE例程已经启动。TotalSystemGlobalArea3675756336bytesFixedSize735024bytesVariableSize2835349504bytesDatabaseBuffers838860800bytesRedoBuffers811008bytes数据库装载完毕。数据库已经打开。查看命中率\当前只有80的满足要求SQLselectsize_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---------------------------------------------------------------------------------8099251152816019850115282402977511528320397001152840049625115284805955011528560694751152864079400115287208932511528800992501152888010917511528SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS---------------------------------------------------------------------------------96011910011528104012902511528112013895011528120014887511528128015880011528136016872511528144017865011528152018857511528160019850011528命中率降低了？SQLselect1-((physical.value-direct.value-lobs.value)/logical.value)2BufferCacheHitRatio3fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogical4wherephysical.name='physicalreads'5anddirect.name='physicalreadsdirect'6andlobs.name='physicalreadsdirect(lob)'7andlogical.name='sessionlogicalreads';BufferCacheHitRatio----------------------.906673167继续缩小SGA大小SQLaltersystemsetSGA_MAX_SIZE=3000Mscope=spfile;SQLshutdownimmediate数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。SQLstartupORACLE例程已经启动。TotalSystemGlobalArea3155661888bytesFixedSize734272bytesVariableSize2315255808bytesDatabaseBuffers838860800bytesRedoBuffers811008bytes数据库装载完毕。数据库已经打开。selectsize_for_estimate,buffers_for_estimate,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READSfromv$db_cache_advicewhereblock_size='8192'andadvice_status='ON';继续测试：等待30分钟之后测试SQLaltersystemsetdb_cache_size=1120M;select1-((physical.value-direct.value-lobs.value)/logical.value)BufferCacheHitRatiofromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogicalwherephysical.name='physicalreads'anddirect.name='physicalreadsdirect'andlobs.name='physicalreadsdirect(lob)'andlogical.name='sessionlogicalreads';---命中率逐渐的提高了---半个小时之后查询命中率是98%SQLselect1-((physical.value-direct.value-lobs.value)/logical.value)2BufferCacheHitRatio3fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogical4wherephysical.name='physicalreads'5anddirect.name='physicalreadsdirect'6andlobs.name='physicalreadsdirect(lob)'7andlogical.name='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--------------------------------------------------------DEFAULT1129581DEFAULT2249514DEFAULT3369514DEFAULT4489514DEFAULT5609514DEFAULT6729514DEFAULT7849514DEFAULT8969514DEFAULT10089514DEFAULT11209514DEFAULT12329514NAMESIZE