調(diào)整概述調(diào)整Oracle的內(nèi)存使用調(diào)整Oracle的磁盤利用

調(diào)整概述調(diào)整Oracle的內(nèi)存使用調(diào)整Oracle的磁盤利用調(diào)整數(shù)據(jù)庫的應用數(shù)據(jù)庫性能調(diào)整調(diào)整共享池調(diào)整數(shù)據(jù)庫緩沖區(qū)調(diào)整重做日志緩沖區(qū)調(diào)整Oracle的內(nèi)存使用TheSharedGlobalArea（SGA）共享池:LibrarycacheDatadictionarycache多線索服務器所需的UGADatabasebuffercacheRedologbuffer

SharedpoolLibrarycacheDatadictionarycache

UserGlobalArea

Largepool

TheSharedPool其大小由初始化參數(shù)SHARED_POOL_SIZE所定義Librarycache包含語句的正文、分析后的代碼，以及執(zhí)行計劃Datadictionarycache包含表、列定義，以及權限信息，它們來自系統(tǒng)數(shù)據(jù)字典表UGA包含多線索服務器的用戶會話信息

SharedpoolLibrary

cacheData

dictionary

cacheSharedpoolUGATheLibraryCache用來存儲用戶共享的SQL語句和PL/SQL塊由LRU算法進行管理用來防止語句的重新分析

SharedSQL,PL/SQLareasTheLibraryCacheContextareaforSELECTstatement2ContextareaforSELECTstatement1SELECTstatement1SELECTstatement2SELECTstatement1減少不命中，達到最小的分析次數(shù)保證用戶可以共享語句防止語句因過時而導致的清除避免語句因失效而導致的重新分析LibraryCache的調(diào)整避免碎片的產(chǎn)生為大的內(nèi)存需求預留足夠的空間避免大的PL/SQL匿名塊減少MTS連接的UGA消耗LibraryCache的調(diào)整GETS:ThenumberoflookupsforobjectsofthenamespacePINS:ThenumberofreadsorexecutionsoftheobjectsofthenamespaceRELOADS:Thenumberoflibrarycachemissesontheexecutionstep,causingimplicitreparsingofthestatementandblock有關術語調(diào)整LibraryCache的診斷工具V$LIBRARYCACHE

SharedpoolLibrarycacheSharedSQL

andPL/SQLV$SQLAREAV$SQLTEXTV$DB_OBJECT_CACHEreport.txtV$SGASTATSHARED_POOL_SIZEOPEN_CURSORSSESSION_CACHED_CURSORSCURSOR_SPACE_FOR_TIMEDatadictionary

cacheUGA正在共享的游標?Findoutwhichstatementsusersarerunning:SQL>selectsql_text,users_executing,2executions,loads3fromv$sqlarea;SQL>select*fromv$sqltext2wheresql_textlike3'select*fromscott.s_deptwhereid=%';SQL>selectgethitratio

2fromv$librarycache

3wherenamespace=‘SQLAREA’;

CheckGETHITRATIOinV$LIBRARYCACHE:ExecutesPROC1>Firstpin,1loadExecutesPROC1>Secondpin,noreloadExecutesPROC1>Thirdpin,noreloadExecutesPROC1>Fourthpin,noreload原則:

LibraryCacheReloadsReloads應當:理想為0不要超過pins的1%4pinsand0reloadSQL>selectsum(pins)"Executions",sum(reloads) 2"CacheMisses",sum(reloads)/sum(pins)3fromv$librarycache;ExecutionsCacheMissessum(reloads)/sum(pins)--------------------------------------------264110.00378644 如果reloads-to-pins率大于1%時，則需要適當提高初始化參數(shù)SHARED_POOL_SIZE。原則:LibraryCacheReloadsLIBRARYGETSGETHITRATIPINSPINHITRATIRELOADSINVALIDATI----------------------------------------------------------------------SQLAREA2036 .987 12822.982 950report.txt

的輸出：失效INVALIDATIONS列表示此項目被做出失效標記的次數(shù)，失效標記可以引起重新裝入NAMESPACEPINSRELOADSINVALIDATIONS----------------------------------------------SQLAREA1797114SQL>selectnamespace,pins,reloads,invalidations

2fromv$librarycache;NAMESPACEPINSRELOADSINVALIDATIONS----------------------------------------------SQLAREA1793100SQL>ANALYZETABLEscott.s_deptCOMPUTESTATISTICS;SQL>select*fromscott.s_dept;為存儲對象定義必需的空間，例如包、視圖等為慣例使用的SQL語句定義足夠的內(nèi)存量為大的內(nèi)存需求保留一定的空間，避免不命中及碎片保持頻繁使用的對象將大的匿名塊轉換成帶有打包函數(shù)調(diào)用的小匿名塊設置LibraryCache的大小總的空間分配SQL語句:存儲對象，例如包和視圖:SQL>selectsum(sharable_mem)2fromV$DB_OBJECT_CACHE;SUM(SHARABLE_MEM)-----------------379600SQL>selectsum(sharable_mem)2fromV$SQLAREAwhereexecutions>5;SUM(SHARABLE_MEM)-----------------381067大的內(nèi)存需求

SharedpoolV$SHARED_POOL_RESERVEDSHARED_POOL_SIZESHARED_POOL_RESERVED_SIZELibrarycacheSharedSQLandPL/SQLDatadictionary

cacheUGA滿足大的連續(xù)的內(nèi)存需求在共享池內(nèi)保留未形成碎片的內(nèi)存可用的診斷工具：V$SHARED_POOL_RESERVED字典視圖提供的包和過程DBMS_SHARED_POOLABORTED_REQUEST_THRESHOLD設置初始化參數(shù)SHARED_POOL_RESERVED_SIZE共享池保留空間的調(diào)整KeepingLargeObjectsSQL>EXECUTEdbms_shared_pool.keep(package_name);

查找沒有保持在librarycache中的PL/SQL對象:SQL>select*fromv$db_object_cache2wheresharable_mem>100003and(type=‘PACKAGE’ortype=‘PACKAGEBODY’or4type=‘FUNCTION’ortype=‘PROCEDURE’)5andKEPT=‘NO’;將大的包保持在librarycache中:PL/SQL的匿名塊 查找PL/SQL匿名塊，并且將它們轉換成較小的匿名塊，通過調(diào)用打包的函數(shù)來實現(xiàn)。SQL>selectsql_textfromv$sqlarea

2wherecommand_type=473andlength(sql_text)>500;影響LibraryCache的其他參數(shù)OPEN_CURSORSCURSOR_SPACE_FOR_TIMESESSION_CACHED_CURSORS內(nèi)容：字典對象的定義術語：GETS:NumberofrequestsonobjectsGETMISSES:Numberofrequestsresultingincachemisses調(diào)整：避免DataDictionaryCache的不命中DataDictionaryCache的調(diào)整DataDictionaryCache調(diào)整的診斷工具V$ROWCACHE:PARAMETERGETSGETMISSES

SharedpoolDatadictionary

cachereport.txtSHARED_POOL_SIZELibrarycacheSharedSQLandPL/SQLUGA調(diào)整DataDictionaryCacheGETMISSES與GETS之比應當小于15%:SQL>selectparameter,gets,getmisses2fromv$rowcache;PARAMETERGETS GETMISSES--------------------------------------------dc_objects143434171dc_synonyms140432127原則:

DictionaryCache不命中NAMEGET_REQSGET_MISS-------------------------------dc_objects143434171dc_synonyms140432127report.txt的輸出: 如果不命中率較高，可以提高初始化參數(shù)SHARED_POOL_SIZE的值UGA與MTSSharedpoolStack

spaceUsersessiondataCursorstateStackspacePGA專用服務器連接:多線索服務器連接:SharedpoolUsersessiondataCursorstatePGAUGAUGAV$STATNAMEV$SESSTATV$MYSTATOPEN_CURSORSSESSION_CACHED_CURSORSUserGlobalArea的大小SQL>selectSUM(value)||'bytes'

"Totalsessionmemory"2fromV$SESSTAT,V$STATNAME3wherename='sessionugamemory'4andv$sesstat.statistic#=v$statname.statistic#;

當前連接會話所使用的UGA空間查詢:SQL>selectSUM(value)||'bytes'

"Totalsessionmemory"2fromV$MYSTAT,V$STATNAME3wherename='sessionugamemory'4andv$mystat.statistic#=v$statname.statistic#;

SQL>selectSUM(value)||'bytes'

"Totalmaxmemory"2fromV$SESSTAT,V$STATNAME3wherename='sessionugamemorymax'4andv$sesstat.statistic#=v$statname.statistic#;

全部MTS用戶所使用的空間查詢:全部MTS用戶所使用的最大UGA空間查詢:LargePoolDatabasebuffercacheRedologbuffer

SharedpoolLibrarycacheDatadictionarycache

UserGlobalArea

Largepool

SGA區(qū)中單獨的一部分內(nèi)存，用途如下：I/O服務器進程，例如DBWR_IO_SLAVESOracle的備份和還原操作多線索服務器的會話內(nèi)存并行查詢的消息可以避免有關操作的性能開銷設置初始化參數(shù)LARGE_POOL_SIZE調(diào)整共享池調(diào)整數(shù)據(jù)庫緩沖區(qū)調(diào)整重做日志緩沖區(qū)調(diào)整Oracle的內(nèi)存使用DataBufferCacheSGADBbuffercacheDirtylist....LRUlist....DBWnServer

DB_BLOCK_SIZE*

DB_BLOCK_BUFFERSServerreadsintothebuffercache

DBWnwritesfromthebuffercacheRead-consistentcopiesFree,dirty,orpinnedstateDatafilesDatafiles管理DataBufferCacheSGADBbuffercache12Dirtylist....LRUlist....DBWn3455ServerLGWRDatafilesDatafiles管理DataBufferCacheSGADBbuffercacheDirtylist....LRUlist....DBWn6Server77LGWRDatafiles8910Datafiles服務器進程盡可能在內(nèi)存中查找到數(shù)據(jù)對于OLTP類型的應用，最好能夠達到90%的命中率調(diào)整DataBufferCache的目標提高DataBufferCache的大小使用多個緩沖池緩存表對于排序或并發(fā)讀操作，繞開BufferCache調(diào)整DataBufferCache的技術V$SYSSTATreport.txt診斷工具V$SESSTATSGALRUlistDirtylistDBbuffercacheKeepbufferpoolRecyclebufferpoolDefaultbufferpoolV$BUFFER_POOLDB_BLOCK_SIZEDB_BLOCK_BUFFERSDB_BLOCK_LRU_LATCHESBUFFER_POOL_KEEPBUFFER_POOL_RECYCLEV$SYSTEM_EVENTV$BHV$SESSION_WAITV$CACHEV$BUFFER_POOL_STATISTICS計算DataBufferCache的命中率SQL>SELECT1-(phy.value/(cur.value+con.value))2"CACHEHITRATIO"3FROMv$sysstatcur,v$sysstatcon,v$sysstatphy

6WHERE='dbblockgets'7AND='consistentgets'8AND='physicalreads';CACHEHITRATIO---------------.908160337Statistic Total PerTransact PerLogon PerSecond--------------- ------ ------------ --------- ----------consistentgets 121754 1117.07 6764.11 50.73dbblockgets 20628 189.25 1146 8.6physicalreads 104695 960.5 5816.94 43.62來自V$SYSSTAT:來自report.txt:命中率與數(shù)據(jù)訪問的方法有關：全表掃描數(shù)據(jù)或應用設計對大表的隨機訪問高速緩存命中的分布不均勻增加高速緩存的大小高速緩存的命中率小于0.9沒有不適當?shù)姆猪撊毕菀郧霸谠黾雍笥行ataBufferCache的命中率UsingMultipleBufferPoolsSGADBbuffercachesLRUlistsKEEPpoolRECYCLEpoolDEFAULTpool定義多個緩沖池...DB_BLOCK_BUFFERS=20000DB_BLOCK_LRU_LATCHES=6BUFFER_POOL_KEEP=(BUFFERS:14000,LRU_LATCHES:1)BUFFER_POOL_RECYCLE=(BUFFERS:2000,LRU_LATCHES:3)...緩沖池的數(shù)據(jù)塊取自DB_BLOCK_BUFFERS鎖存器的定義來自初始化參數(shù)DB_BLOCK_LRU_LATCHES每一個鎖存器至少有50個數(shù)據(jù)塊DBA可以定義一個、兩個，或者三個緩沖池定義多個緩沖池使能多個緩沖池CREATEINDEXcust_idx

STORAGE(BUFFER_POOLKEEP);ALTERTABLEcustomerSTORAGE(BUFFER_POOLRECYCLE);ALTERINDEXcust_name_idx

REBUILDSTORAGE(BUFFER_POOLKEEP);Keep緩沖池調(diào)整目標:

在內(nèi)存中保持數(shù)據(jù)塊大小:

容納全部或幾乎全部的數(shù)據(jù)塊工具:

ANALYZE...ESTIMATESTATISTICSSQL>ANALYZETABLEcodesESTIMATESTATISTICS;Tableanalyzed.SQL>SELECTtable_name,blocks2FROMdba_tables3WHEREowner='HR'ANDtable_name='CODES';TABLE_NAMEBLOCKS--------------------CODES14Recycle緩沖池

調(diào)整目標:

當事務完成時，有關的數(shù)據(jù)塊從內(nèi)存中清除大小:僅容納活動的數(shù)據(jù)塊工具:V$CACHESQL>@catparr...SQL>SELECTowner#,name,count(*)blocks2FROMv$cache3GROUPBYowner#,name;OWNER#NAMEBLOCKS--------------------------5CUSTOMER147...Recycle緩沖池

工具:

V$SESS_IOSQL>SELECTio.block_gets,2 io.consistent_gets,3 io.physical_reads4FROMv$sess_io io,5 v$session s6WHEREs.audsid=USERENV('SESSIONID')7*ANDio.sid=s.sid;BLOCK_GETSCONSISTENT_GETSPHYSICAL_READS---------------------------------------2187232711344多個緩沖池命中率的計算SQL>@catperf...SQL>SELECTname, 1-(physical_reads/(db_block_gets+ consistent_gets))"HIT_RATIO"2FROM sys.v$buffer_pool_statistics3WHEREdb_block_gets+consistent_gets>0;NAME HIT_RATIO------------------ ----------KEEP .983520845RECYCLE .503866235DEFAULT .790350047KEEP緩沖池反復使用的數(shù)據(jù)塊段的大小小于緩沖池缺省大小的10%RECYCLE緩沖池事務完成后的數(shù)據(jù)塊不再使用段的大小是緩沖池缺省大小的2倍多個緩沖池與BufferPools有關的字典視圖SQL>SELECT*2FROMv$buffer_pool3WHEREid<>0;IDNAMELO_SETIDHI_SETIDSET_COUNTBUFFERSLO_BNUMHI_BNUM-------------------------------------------------------1KEEP331140000139992RECYCLE463200014000159993DEFAULT12240001600019999其它性能指標SQL>SELECTevent,total_waits2FROMv$system_event3WHEREeventin4 ('freebufferwaits','bufferbusywaits');EVENTTOTAL_WAITS---------------------------------freebufferwaits337bufferbusywaits3466來自V$SYSTEM_EVENT:SQL>SELECTname,value2FROMv$sysstat

3WHEREname='freebufferinspected';NAMEVALUE-----------------------------------freebufferinspected183來自V$SYSSTAT:表的緩存在全表掃描期間緩存表：使用CACHE子句創(chuàng)建表使用CACHE子句修改表在查詢中使用CACHE提示原則：避免過多的緩存表LRU鎖存器LRU鎖存器控制BufferCache使用的LRU(最近最少使用列表)Oracle的LRU鎖存器的缺省個數(shù)的設置是CPU個數(shù)的一半，至少為1每一個LRU至少可以控制50個緩沖區(qū)LRU鎖存器的調(diào)整目標具有足夠的LRU鎖存器，可以保證服務器進程之間的沖突最小CPU個數(shù)與LRU鎖存器個數(shù)要均衡為每一個鎖存器設置一個DBWn進程LRU鎖存器沖突的診斷V$LATCHNAMEV$SYSTEM_EVENTV$SESSION_EVENTV$BUFFER_POOL_STATISTICSsleeps

gets

name=‘lachebufferslruchain’SGA

DatabuffercacheLRUlatchServer

processServer

processV$LATCHDB_BLOCK_LRU_LATCHESLRU鎖存器沖突的解決如果LRU鎖存器的命中率低于99%：通過設置初始化參數(shù)DB_BLOCK_LRU_LATCHES來提高LRU鎖存器的個數(shù)鎖存器的最大個數(shù)要低于:CPUsx2x3buffers/50FreeLists一個freelist用來維護一個對象的可用數(shù)據(jù)塊的列表，供數(shù)據(jù)插入時使用一個對象的freelist的個數(shù)不能動態(tài)進行設置設置多個freelist，對單CPU系統(tǒng)來說，沒有太大的價值調(diào)整目標是保證一個對象具有足夠的freelist，減少沖突FreeList沖突的診斷V$WAITSTATclasscounttime“datablocks”SGADatabuffercacheV$SYSTEM_EVENTeventtotal_waits“bufferbusywaits”Server

processServer

processV$SESSION_WAITeventp1“FILE”p2“LOCK”p3“ID”DBA_SEGMENTSsegment_namesegment_typefreelistsheader_fileheader_blockFREELISTSFreeList沖突的解決1. 查詢V$SESSION_WAIT視圖2. 確定對象并從確定對象并從DBA_SEGMENTS

得到該段的freelists3. 重新創(chuàng)建有問題的對象調(diào)整共享池調(diào)整數(shù)據(jù)庫緩沖區(qū)調(diào)整重做日志緩沖區(qū)調(diào)整Oracle的內(nèi)存使用RedoLogBufferDatabasebufferc