1、一、名詞解釋SGA：SystemGlobalArea是OracleInstance的基本組成部分，在實例啟動時分配；系統全局域SGA主要由三部分構成：共享池、數據緩沖區、日志緩沖區。共享池：SharedPool用于緩存最近被執行的SQL語句和最近被使用的數據定義，主要包括：Librarycache(共享SQL區)和Datadictionarycache(數據字典緩沖區)。共享SQL區是存放用戶SQL命令的區域，數據字典緩沖區存放數據庫運行的動態信息。緩沖區高速緩存：DatabaseBufferCache用于緩存從數據文件中檢索出來的數據塊，可以大大提高查詢和更新數據的性能。大型池：LargeP

2、ool是SGA中一個可選的內存區域，它只用于sharedserver環境。Java池：JavaPool為Java命令的語法分析提供服務。PGA：ProcessGlobalArea是為每個連接至到Oracledatabase的用戶進程保留的內存。二、分析與調整(1)系統全局域：SGA與操作系統、內存大小、cpu、同時登錄的用戶數有關?？烧糘S系統物理內存的1/3到1/2。共享池SharedPool：查看共享池大小Sql代碼SQLshowparametershared_pool_size查看共享SQL區的使用率：Sql代碼select(sum(pins-reloads)/sum(pins)Libr

3、arycachefromv$librarycache;-動態性能表LIBRARY命中率應該在90%以上，否則需要增加共享池的大小。查看數據字典緩沖區的使用率：Sql代碼select(sum(gets-getmisses-usage-fixed)/sum(gets)Datadictionarycachefromv$rowcache;-動態性能表這個使用率也應該在90%以上，否則需要增加共享池的大小。修改共享池的大小:Sql代碼ALTERSYSTEMSETSHARED_POOL_SIZE=64M;b.緩沖區高速緩存DatabaseBufferCache:查看共享池大小Sql代碼SQLshowpar

4、ameterdb_cache_size查看數據庫數據緩沖區的使用情況：Sql代碼SELECTname,valueFROMv$sysstatorderbynameWHEREnameIN(DBBLOCKGETS,CONSISTENTGETS,PHYSICALREADS);SELECT*FROMV$SYSSTATWHERENAMEIN(parse_time_cpu,parse_time_elapsed,parse_count_hard);計算出來數據緩沖區的使用命中率=1-(physicalreads/(dbblockgets+consistentgets),這個命中率應該在90%以上，否則需要增加

5、數據緩沖區的大小。pga_aggregate_targetbiginteger536870912日志緩沖區查看日志緩沖區的使用情況:Sql代碼SELECTname,valueFROMv$sysstatWHEREnameIN(redoentries,redologspacerequests)查詢出的結果可以計算出日志緩沖區的申請失敗率：申請失敗率=requests/entries,申請失敗率應該接近于0,否則說明日志緩沖區開設太小，需要增加ORACLE數據庫的日志緩沖區。大型池：可以減輕共享池的負擔，可以為備份、恢復等操作來使用，不使用LRU算法來管理。其大小由數據庫的共享模式/db模式如果是共

6、享模式的話,要分配的大一些。指定LargePool的大?。篠ql代碼ALTERSYSTEMSETLARGE_POOL_SIZE=64MJava池：在安裝和使用Java的情況下使用。(2)PGA調整a.PGA_AGGREGATE_TARGET初始化設置PGA_AGGREGATE_TARGET的值應該基于Oracle實例可利用內存的總量來設置，這個參數可以被動態的修改。假設Oracle實例可分配4GB的物理內存，剩下的內存分配給操作系統和其它應用程序。你也許會分配80%的可用內存給Oracle實例，即3.2G?，F在必須在內存中劃分SGA和PGA區域。在OLTP(聯機事務處理)系統中，典型PGA內存

7、設置應該是總內存的較小部分(例如20%)，剩下80%分配給SGA。OLTP：PGA_AGGREGATE_TARGET=(total_mem*80%)*20%=2.5G在DSS(數據集)系統中，由于會運行一些很大的查詢，典型的PGA內存最多分配70%的內存。DSS：PGA_AGGREGATE_TARGET=(total_mem*80%)*50%在這個例子中，總內存4GB，DSS系統，你可以設置PGA_AGGREGATE_TARGET為1600MB,OLTP貝U為655MB。配置PGA自動管理不用重啟DB，直接在線修改。SQLaltersystemsetworkarea_size_policy=a

8、utoscope=both;Systemaltered.SQLaltersystemsetpga_aggregate_target=512mscope=both;Systemaltered.SQLshowparameterworkareaNAMETYPEVALUEworkarea_size_policystringAUTO-這個設置成AUTOSQLshowparameterpgaNAMETYPEVALUE536870912bytes477379584bytes26843136bytes6448128bytes11598848bytes166175744bytes393216bytes69074

9、944bytes0bytes1049600bytes0bytes530432bytes1118114895872bytes4608000bytes96.14percentSQL監控自動PGA內存管理的性能V$PGASTAT：這個視圖給出了一個實例級別的PGA內存使用和自動分配的統計。SQLSQLSQLsetlines256setpages42SELECT*FROMV$PGASTAT;NAMEVALUEUNITaggregatePGAtargetparameter當前PGA_AGGREGATE_TARGET的值aggregatePGAautotarget-當前可用于自動分配了OPGA大小，應該比

10、PGA_AGGREGATE_TARGET小globalmemorybound-自動模式下工作區域的最大大小Oracle根據工作負載自動調整。totalPGAinusetotalPGAallocated-PGA的最大分配maximumPGAallocatedtotalfreeablePGAmemory-PGA的最大空閑大小PGAmemoryfreedbacktoOStotalPGAusedforautoworkareas-PGA分配給autoworkareas的大小maximumPGAusedforautoworkareastotalPGAusedformanualworkareasmaximu

11、mPGAusedformanualworkareasoverallocationcount-實例啟動后，發生的分配次數，如果這個值大于就要考慮增加bga的值bytesprocessedextrabytesread/writtencachehitpercentage命中率16rowsselected.-V$PGA_TARGET_ADVICESQLSELECTround(PGA_TARGET_FOR_ESTIMATE/1024/1024)target_mb,ESTD_PGA_CACHE_HIT_PERCENTAGEcache_hit_perc,ESTD_OVERALLOC_COUNTFROMv$p

12、ga_target_advice;Theoutputofthisquerymightlooklikethefollowing:TARGET_MBCACHE_HIT_PERCESTD_OVERALLOC_COUNT6323367125243025030337539050058060059070059080060090060010006101500670200076030008304000850可以看出當TARGET_MB為375M是ESTD_OVERALLOC_COUNT=0,所以可以將PGA_AGGREGATE_TARGET設置成375M。附：oracleSGA與PGA區另U：SGA：是用于存

13、儲數據庫信息的內存區，該信息為數據庫進程所共享。它包含Oracle服務器的數據和控制信息，它是在Oracle服務器所駐留的計算機的實際內存中得以分配，如果實際內存不夠再往虛擬內存中寫。PGA：包含單個服務器進程或單個后臺進程的數據和控制信息，與幾個進程共享的SGA正相反,PGA是只被一個進程使用的區域，PGA在創建進程時分配,在終止進程時回收。另一篇文章中也這樣寫了相關的調整記錄如何估算PGA，SGA的大小，配置數據庫服務器的內存ORACLE給的建議是：OLTP系統PGA=(TotalMemory)*80%*20%ODSS系統PGA=(TotalMemory)*80%*50%oORACLE建議

14、一個數據庫服務器，分80%的內存給數據庫，20%的內存給操作系統，那怎么給一個數據庫服務器配內存呢?SQLselect*fromv$pgastat;NAMEVALUEUNITaggregatePGAtargetparameter104857600bytes這個值等于參數PGA_AGGREGATE_TARGET的值，如果此值為?，表示禁用了PGA自動管理。aggregatePGAautotarget75220992bytes表示PGA還能提供多少內存給自動運行模式，通常這個值接近a_aggregate_target-totalpgainuse.globalmemorybound20971520b

15、ytes工作區執行的最大值，如果這個值小于馬上增加PGA大小totalPGAinuse當前分配PGA的總大小，這個值有可能大于PGA，如果PGA設置太小.這個值接近sv$process.totalPGAallocated工作區花費的總大小maximumPGAallocatedtotalfreeablePGAmemory空閑的PGAprocesscount一個有23個processmaxprocessescountPGAmemoryfreedbacktoOStotalPGAusedforautoworkareasmaximumPGAusedforautoworkareastotalPGAused

16、formanualworkareas為0自動管理maximumPGAusedformanualworkareas自動管理overallocationcount30167040byteselectsum(pga_used_mem)from52124672bytes67066880bytes0bytes沒有了23當前250bytes8891392bytes22263808bytes0bytes-0bytes為00-如果PGA設置太小，導致PGA有時大于PGA_AGGREGATE_TARGET的值，此處為0,說明PGA沒有擴展大于TARGET的值,如果此值出現過，那么增加PGA大小。12443443

17、2bytes0bytes100percentbytesprocessedextrabytesread/writtencachehitpercentage命中率為100%，如果太小增加PGArecomputecount(total)665119rowsselectedSQLselectmax(pga_used_mem)/1024/1024Mfromv$process;當前一個process消耗最大的內存M9.12815189SQLselectmin(pga_used_mem)/1024/1024Mfromv$processwherepga_used_mem0;process消耗最少內存0.191

18、86878SQLselectmax(pga_used_mem)/1024/1024Mfromv$process;process曾經消耗的最大內存M9.12815189SQLselectsum(pga_used_mem)/1024/1024fromv$process;當前process共消耗的PGASUM(PGA_USED_MEM)/1024/102428.8192501068115如何設置PGA呢？我們可以在壓力測試階段，模擬一下系統的運行，然后運行select(selectsum(pga_used_mem)/1024/1024fromv$process)/(selectcount(*)fro

19、mv$process)fromdual;得到一個process大約占用了多少的內存，然后估算系統一共會有多少連接，比如一共有500個連接，另B么sessions=1.1*process+5=500，另B么processes=450,再乘以一個process需要消耗的內存，就能大約估算出PGA需要設置多大。EG=1.1*450=495M估算的大一點550M就0K樂最好將PGA設置的值比計算出的值大一點，PGA值設定好后，就可以根據系統的性質，如果系統為OLTOP，那么總的內存可以設置為PGA/0.16,最后也能估算出SGA的大小，建議還是多配點內存，反正便宜。下面摘抄eygle的關于一個proc

20、ess能夠分配的最大內存(串行操作)的規則：10gR1之前，對于串行操作(非并行)一個process能夠分配的最大的內存為min(5%pga_aggregate_target,100m)10gR2之后，對于串行操作(非并行)一個process能夠分配的最大內存有如下規則：女口果pga_aggregate_target=500m,那么最大的內存為20%*pga_aggregate_target.女口果500mpga_aggregate_target=1000m,那么最大內存為100m.女口果1000mpga_aggregate_target2.5G,那么最大內存為2.5G.SQLSELECTx.

21、ksppinmNAME,y.ksppstvlVALUE,x.ksppdescdescribFROMSYS.x$ksppix,SYS.x$ksppcvyWHEREx.inst_id=USERENV(Instance)ANDy.inst_id=USERENV(Instance)ANDx.indx=y.indxANDx.ksppinmLIKE%&par%VALUENAMEDESCRIB_smm_max_size20480maximumworkareasizeinautomode(serial)SQLshowparameterpgaNAMETYPEVALUEpga_aggregate_targetbi

22、ginteger100M此處我的一個process能夠分配的最大內存為20M，因為我的PGA=100M，符合上面的規則。隱含參表示一個process能夠分配最大的memory.買了piner的oracle高可用環境一書，正好趁這段時間學習一下。把看到的東西總結一下發表于此，今天先發第一章關于SGA與PGA的內容。以后會陸續將總結在此發表，與大家共享。SGA與PGA的結構如下圖：SGA:查看SGA：Sqlpshowsga或select*fromv$sga;TotalSystemGlobalArea289406976bytesFixedSize1248600bytesVariableSize176

23、161448bytesDatabaseBuffers109051904bytesRedoBuffers2945024bytesFixedSize：包括了數據庫與實例的控制信息、狀態信息、字典信息等，啟動時就被固定在SGA中，不會改變。VariableSize：包括了shardpool、largepool、javapool、streampool、游標區和其他結構DatabaseBuffers：數據庫中數據塊緩沖的地方，是SGA中最大的地方，決定數據庫性能RedoBuffers：提供RED0緩沖的地方，在OLAP中不需要太大V$sgastat記錄了SGA的一些統計信息V$sga_dynamic_c

24、omponents保存SGA中可以手動調整的區域的一些調整記錄Shardpool:Shard_pool_size決定其大小，10g以后自動管理Shard_pool中數據字典和控制區結構用戶無法直接控制，與用戶有關的只有sql緩沖區(librarycache)。將經常訪問的過程或包用DBMS_SHARED_POOL.KEEP存儲過程將該包pin在共享池中。手工清除共享池的內容：altersystemflushshard_pool;共享池相關的幾個常用的視圖：V$sqlarea記錄了所有sql的統計信息，包括執行次數、物理讀、邏輯讀、耗費時間等V$sqltext_with_newline完全顯示s

25、ql語句，通過hash_value來標示語句，piece排序V$sql_plan保存了sql的執行計劃，通過工具查看V$shared_pool_advice對共享池的預測，可以做調整SGA的參考Databuffer：在OLTP系統中要求databuffer的命中率在95%以上selectsum(pins)execution,sum(pinhits)hits,(sum(pinhits)/sum(pins)*100)pinhitration,sum(reloads)misses,(sum(pins)/(sum(pins)+sum(reloads)*100)relhitratiofromV$libr

26、arycache計算命中率的語句selectround(1-(physical.value-direct.value-lobs.value)/logical.value)*100,2)BufferCacheHitRatiofromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$===physicalreadsdirect(lob)=se

27、ssionlogicalreads;PINSNUMBERNumberoftimesaPINwasrequestedforobjectsofthisnamespacePINHITSNUMBERNumberoftimesallofthemetadatapiecesofthelibraryobjectwerefoundinmemoryRELOADSNUMBERAnyPINofanobjectthatisnotthefirstPINperformedsincetheobjecthandlewascreated,andwhichrequiresloadingtheobjectfromdiskOracle

28、把從databuffer中獲得的數據庫叫cachehit,把從磁盤獲得的腳cachemiss數據緩沖區中的數據塊通過臟列表(dirtylist)和LRU列表(LRUlist)來管理。Databuffer可細分為：defaultpool、keeppool、recyclepool對應的參數為db_cache_size、db_keep_cache_size、db_recycle_size分另U表示緩沖區大小從9i開始oracle支持不同塊大小的表空間，相應的可以為不同塊大小的表空間指定不同塊大小的數據緩沖區，不同塊大小的數據緩沖區可以用相應的db_nk_cache_size來指定，其中n可以是2、4、6、16或32V$db_cache_advice對數據緩沖區的預測，可以做調整databuffer的參考V$bh、x$bh記錄了數據塊在databuffer中緩沖的情況，通過這個視圖可以找系統中的熱點塊。通過下面語句找系統中top10熱