Oracle数据缓冲区优化

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

2、e='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&

3、gt;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_READS80992528.975714363112001

4、60198502.1053104360120240297751.581978413087320397001.426270693980400496251.354367131735480595501.27863349434560694751.189358954568640794001.132556135206720893251.07625334783780099250149569438880109175.706735030953SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS96011

5、9100.3991197847011040129025.2305114233741120138950.192795529031200148875.150674662781280158800.150174381861360168725.150174381861440178650.150174381861520188575.150174381861600198500IZEFORESTIMATEM为单位：当SIZE_FOR_ESTIMATE=80M的时候ESTD_PHYSICAL_READS=1436311200当SIZE_FOR_ESTIMATE=1120M的时候ESTD

6、_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-0209

7、7:无法修改参数，因为指定的值无效ORA-00384:没有足够的内存来增加高速缓存的大小之前的值sga_max_sizebiginteger1494715120SQL>altersystemsetSGA_MAX_SIZE=3500Mscope=spfile;系统已更改。重新启动与关闭解决SQL>shutdownimmediate数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。SQL>startupORACLE例程已经启动。TotalSystemGlobalArea3675756336bytes735024 bytes2835349504 bytes838860800

8、 bytes811008 bytesFixedSizeVariableSizeDatabaseBuffersRedoBuffers数据库装载完毕。数据库已经打开。查看命中率当前只有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_

9、FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS8099251152816019850115282402977511528320397001152840049625115284805955011528560694751152864079400115287208932511528800992501152888010917511528SIZE_FOR_ESTIMATEBUFFERS_FOR_ESTIMATEESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS960119100115281040129

10、02511528112013895011528120014887511528128015880011528136016872511528144017865011528152018857511528160019850011528命中率降低了？SQL>select1-(physical.value-direct.value-lobs.value)/logical.value)2 "BufferCacheHitRatio"3 fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogical4 where

11、='physicalreads'5 ='physicalreadsdirect'6 ='physicalreadsdirect(lob)'7 ='sessionlogicalreads'BufferCacheHitRatio.906673167继续缩小SGA大小SQL>altersystemsetSGA_MAX_SIZE=3000Mscope=spfile;SQL>shutdownimmediate数据库已经关闭

12、。已经卸载数据库。ORACLE例程已经关闭。SQL>startupORACLE例程已经启动。TotalSystemGlobalArea3155661888bytes734272 bytes2315255808 bytes838860800 bytes811008 bytesFixedSizeVariableSizeDatabaseBuffersRedoBuffers数据库装载完毕。数据库已经打开。selectsize_for_estimate,buffers_for_estimate,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READSfromv$d

13、b_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$sysstatlogicalwherephys

14、='physicalreads'='physicalreadsdirect'='physicalreadsdirect(lob)'='sessionlogicalreads'-命中率逐渐的提高了-半个小时之后查询命中率是98%SQL>select1-(physical.value-direct.value-lobs.value)/logical.value)2 "BufferCacheHitRatio3 fromv$

15、sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogical4 ='physicalreads'andreadsdirect'6='physicalreadsdirect(lob)'7='sessionlogicalreads'BufferCacheHitRatio.980309028查询推荐的值showparameterdb_block_size8192selectname,size_

16、for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READSfromv$db_cache_advicewhereblock_size='8192'andadvice_status='ON'NAMESIZEFORESTIMATEESTDPHYSICALREADSDEFAULT1129581DEFAULT2249514DEFAULT3369514DEFAULT4489514DEFAULT5609514DEFAULT6729514DEFAULT7849514DEFAULT8969514DEFAULT10089514DEFAULT

17、11209514DEFAULT12329514NAMESIZEFORESTIMATEESTDPHYSICALREADSDEFAULT13449514DEFAULT14569514DEFAULT15689514DEFAULT16809514DEFAULT17929514DEFAULT19049514DEFAULT20169514DEFAULT21289514DEFAULT22409514显不只要112M大小的空间就可以稳定降低ESTD_PHYSICAL_READSaltersystemsetdb_cache_size=112MNAMESIZE_FOR_ESTIMATEESTD_PHYSICAL_

18、READSDEFAULT160DEFAULT320DEFAULT480DEFAULT640DEFAULT800DEFAULT960DEFAULT1120DEFAULT1280DEFAULT1440DEFAULT1600DEFAULT1760NAMESIZE_FOR_ESTIMATEESTD_PHYSICAL_READSDEFAULT1920DEFAULT2080DEFAULT2240DEFAULT2400DEFAULT2560DEFAULT2720DEFAULT2880DEFAULT3040DEFAULT3200-查看命中率select1-(physical.value-direct.value-lobs.value)/logical.value)"BufferCacheHitRatio"fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$='physicalreads'='physicalreadsdirect'='physicalreadsdirect(lob)andlogical.nam