Oracle數(shù)據(jù)緩沖區(qū)優(yōu)化

1、Oracle數(shù)據(jù)緩沖區(qū)優(yōu)化1 .優(yōu)化緩沖區(qū)大小、提高服務(wù)器的命中率db_cache_sizebiginteger8388608002 .查看緩沖區(qū)命中率是否需要調(diào)優(yōu).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'當(dāng)命中率>90%說(shuō)明命中率很高了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為單位：當(dāng)SIZE_FOR_ESTIMATE=80M的時(shí)候ESTD_PHYSICAL_READS=1436311200當(dāng)SIZE_FOR_ESTIMATE=1120M的時(shí)候ESTD

6、_PHYSICAL_READS=9552903當(dāng)SIZE_FOR_ESTIMATE=1280M的時(shí)候ESTD_PHYSICAL_READS=7438186之后ESTD_PHYSICAL_READS固定了所以應(yīng)該過(guò)大db_cache_size=1120M的值使得4.修改發(fā)現(xiàn)DB_cache_size太大了。過(guò)大SGA區(qū)域解決altersystemsetdb_cache_size=1120M-sga設(shè)置太小了導(dǎo)致SQL>altersystemsetdb_cache_size=1120M;altersystemsetdb_cache_size=1120M*ERROR位于第1行：ORA-0209

7、7:無(wú)法修改參數(shù)，因?yàn)橹付ǖ闹禑o(wú)效ORA-00384:沒(méi)有足夠的內(nèi)存來(lái)增加高速緩存的大小之前的值sga_max_sizebiginteger1494715120SQL>altersystemsetSGA_MAX_SIZE=3500Mscope=spfile;系統(tǒng)已更改。重新啟動(dòng)與關(guān)閉解決SQL>shutdownimmediate數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫(kù)。ORACLE例程已經(jīng)關(guān)閉。SQL>startupORACLE例程已經(jīng)啟動(dòng)。TotalSystemGlobalArea3675756336bytes735024 bytes2835349504 bytes838860800

8、 bytes811008 bytesFixedSizeVariableSizeDatabaseBuffersRedoBuffers數(shù)據(jù)庫(kù)裝載完畢。數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。查看命中率當(dāng)前只有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繼續(xù)縮小SGA大小SQL>altersystemsetSGA_MAX_SIZE=3000Mscope=spfile;SQL>shutdownimmediate數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉

12、。已經(jīng)卸載數(shù)據(jù)庫(kù)。ORACLE例程已經(jīng)關(guān)閉。SQL>startupORACLE例程已經(jīng)啟動(dòng)。TotalSystemGlobalArea3155661888bytes734272 bytes2315255808 bytes838860800 bytes811008 bytesFixedSizeVariableSizeDatabaseBuffersRedoBuffers數(shù)據(jù)庫(kù)裝載完畢。數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。selectsize_for_estimate,buffers_for_estimate,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READSfromv$d

13、b_cache_advicewhereblock_size='8192'andadvice_status='ON'繼續(xù)測(cè)試：等待30分鐘之后測(cè)試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'-命中率逐漸的提高了-半個(gè)小時(shí)之后查詢命中率是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大小的空間就可以穩(wěn)定降低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