oracle实例内存调整

一、名词解释SGA：SystemGlobalArea是OracleInstanee的基本组成部分，在实例启动时分配；系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。共享池：SharedPool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Librarycache(共享SQL区)和Datadictionarycache(数据字典缓冲区)。共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。(1) 缓冲区高速缓存：DatabaseBufferCache用于缓存从数据文件中检索岀来的数据块，可以大大提高查询和更新数据的性能。(2) 大型池：LargePool是SGA中一个可选的内存区域，它只用于sharedserver环境。(5)Java池：JavaPool为Java命令的语法分析提供服务。(6)PGA：ProcessGlobalArea是为每个连接到Oracledatabase的用户进程保留的内存。二、分析与调整(1)系统全局域：SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/3到1/2。a. 共享池SharedPool:查看共享池大小Sql代码SQLshowparametershared_pool_size查看共享SQL区的使用率：Sql代码select(sum(pins-reloads)/sum(pins)Librarycachefromv$librarycache;-动态性能表JLIBRARY命中率应该在90%以上，否则需要增加共享池的大小。查看数据字典缓冲区的使用率：Sql代码select(sum(gets-getmisses-usage-fixed)/sum(gets)Datadictionarycachefromv$rowcache;-动态性能表这个使用率也应该在90%以上，否则需要增加共享池的大小。修改共享池的大小：b. Sql代码|ALTERSYSTEMSETSHARED_POOL_SIZE=64M;缓冲区高速缓存DatabaseBufferCache:查看共享池大小Sql代码SQLshowparameterdb_cache_size查看数据库数据缓冲区的使用情况：Sql代码SELECTname,valueFROMv$sysstatorderbynameWHEREnameIN(DBBLOCKGETS，“CONSISTENTGETS，“PHYSICALREADS);SELECT*FROMV$SYSSTATWHERENAMEIN(parse_time_cpu,parse_time_elapsed,parse_count_hard);reads/(dbblockgets+consistentgets),这个命中率应该计算岀来数据缓冲区的使用命中率=1-(physical在90%以上，否则需要增加数据缓冲区的大小。日志缓冲区查看日志缓冲区的使用情况：Sql代码|SELECTname,valueFROMv$sysstatWHEREnameIN(redoentries，redologspacerequests)查询岀的结果可以计算岀日志缓冲区的申请失败率：申请失败率=requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。c. 大型池：可以减轻共享池的负担，可以为备份、恢复等操作来使用，不使用LRU算法来管理。其大小由数据库的共享模式/db模式如果是共享模式的话，要分配的大一些。指定LargePool的大小：d. Sql代码ALTERSYSTEMSETLARGE_POOL_SIZE=64MJava池:在安装和使用Java的情况下使用。a. (2)PGA调整PGA_AGGREGATE_TARGET初始化设置PGA_AGGREGATE_TARGET的值应该基于Oracle实例可利用内存的总量来设置，这个参数可以被动态的修改。假设Oracle实例可分配4GB的物理内存，剩下的内存分配给操作系统和其它应用程序。你也许会分配80%的可用内存给Oracle实例，即3.2G。现在必须在内存中划分SGA和PGA区域。在OLTP(联机事务处理)系统中，典型PGA内存设置应该是总内存的较小部分(例如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则为655MB。配置PGA自动管理不用重启DB，直接在线修改。SQLaltersystemsetworkarea_size_policy=autoscope=both;Systemaltered.SQLaltersystemsetpga_aggregate_target=512mscope=both;Systemaltered.SQLshowparameterworkareaNAMETYPEVALUEworkarea_size_policystringAUTO-这个设置成AUTOSQLshowparameterpgaNAMETYPEVALUEpga_aggregate_targetbiginteger536870912SQL监控自动PGA内存管理的性能PGA内存使用和自动分配的统计V$PGASTAT:这个视图给岀了一个实例级别的SQLsetlines256SQLsetpages42SQLSELECT*FROMV$PGASTAT;NAMEVALUEUNITaggregatePGAtargetparameter-当前PGA_AGGREGATE_TARGET的值aggregatePGAautotarget-当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET小globalmemorybound-自动模式下工作区域的最大大小，Oracle根据工作负载自动调整。totalPGAinusetotalPGAallocated-PGA的最大分配maximumPGAallocatedtotalfreeablePGA-PGA的最大空闲大小PGAmemoryfreedbacktotalPGAusedfor536870912bytes477379584bytes26843136bytes6448128bytes11598848bytesmemory166175744bytes393216bytes-PGA分配给autoworkareasmaximumPGAusedtotalPGAusedmaximumPGAusedoverallocation-实例启动后，bytesprocessedextrabytestoOSautoworkareas的大小forautoworkareasformanualworkareasformanualworkareascount发生的分配次数，如果这个值大于0，就要考虑增加pga的值read/writtencachehitpercentage-命中率16rowsselected.-V$PGATARGETADVICE69074944bytes0bytes1049600bytes0bytes|_530432bytes1118114895872bytes4608000bytes96.14percentSQLSELECTround(PGA_TARGET_FOR_ESTIMATE/1024/1024)target_mbESTD_PGA_CACHE_HIT_PERCENTAGEcache_hit_perc,ESTD_OVERALLOC_COUNTFROMv$pga_target_advice;TheoutputofthisquerymightlooklikethefollowingTARGETMBCACHEHITPERCESTDOVERALLOCCOUNT6323367_r125243025030337539050058060059070059080060090060010006101500670200076030008304000850可以看岀当TARGET_MB为375M是ESTD_OVERALLOC_COUNT=0，所以可以将PGA_AGGREGATE_TARGET设置成375M附：oracleSGA与PGA区别：SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle服务器的数据和控制信息，它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。PGA:包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA正相反，PGA是只被一个进程使用的区域，PGA在创建进程时分配，在终止进程时回收。另一篇文章中也这样写了相关的调整记录如何估算PGA，SGA的大小，配置数据库服务器的内存ORACLE给的建议是：OLTP系统PGA=(TotalDSS系统PGA=(TotalMemory)*80%*50%。ORACLE建议一个数据库服务器，分80%的内存给数据库，20%的内存给操作系统，那怎么给一个数据库服务器配内存呢?SQLselect*fromv$pgastatNAMEMemory)*80%*20%。VALUEUNITaggregatePGAtargetparameter104857600bytes-这个值等于参数PGA_AGGREGATE_TARGET的值，如果此值为0，表示禁用了PGA自动管理。aggregatePGAautotarget75220992bytes-表示PGA还能提供多少内存给自动运行模式，通常这个值接近pga_aggregate_target-totalpgainuse.globalmemorybound20971520bytes-工作区执行的最大值，如果这个值小于1M，马上增加PGA大小totalPGAinuse30167040bytes当前分配PGA的总大小，这个值有可能大于PGA，如果PGA设置太小.这个值接近selectsum(pga_used_mem)fromv$process.totalPGAallocated-工作区花费的总大小maximumPGAallocatedtotalfreeablePGAmemory空闲的PGAprocesscount一个有23个processmaxprocessescountPGAmemoryfreedbacktoOStotalPGAusedforautoworkareasmaximumPGAusedforautoworkareastotalPGAusedformanualworkareas为0自动管理maximumPGAusedformanualworkareas自动管理overallocationcount0-如果PGA设置太小，导致PGA有时大于PGA_AGGREGATE_TARGET的值，此处为0，说明PGA没有扩展大于TARGET的值，如果此值岀现过，那么增加PGA大小。bytesprocessedextrabytesread/writtencachehitpercentage-命中率为100%，如果太小增加(total)52124672bytes67066880bytes0bytes没有了23当前PGArecomputecount19rowsselectedSQLselectmax(-当前一个processpga_used_mem)/1024/1024Mfromv$process消耗最大的内存9.12815189SQLselect消耗最少内存min(pgausedmem)/1024/1024Mfromv$process250bytes8891392bytes22263808bytes0bytes0bytes124434432bytes0bytes100percent6651wherepgausedmem0;-process0.19186878SQLselectmax(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(*)fromv$process)fromdual;得到一个process大约占用了多少的内存，然后估算系统一共会有多少连接，比如一共有500个连接，那么sessions=1.1*process+5=500，那么processes=450,再乘以一个process需要消耗的内存，就能大约估算岀PGA需要设置多大。EG=1.1*450=495M估算的大一点550M就0K乐如果系统为OLTOP，那么总的内存可以最好将PGA设置的值比计算岀的值大一点，PGA值设定好后，就可以根据系统的性质设置为PGA/0.16,最后也能估算岀SGA的大小，建议还是多配点内存，反正便宜。下面摘抄eygle的关于一个process能够分配的最大内存(串行操作)的规则：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.ksppinmNAME,y.ksppstvlVALUE,x.ksppdescdescribFROMSYS.x$ksppix,SYS.x$ksppcvyWHEREx.inst_id=USERENV(Instanee)VALUEANDy.inst_id=USERENV(Instanee)ANDx.indx=y.indxANDx.ksppinmLIKE%&par%NAMEDESCRIB_smm_max_size20480maximumworkareasizeinautomode(serial)SQLshowparameterpga1NAMETYPEVALUEpga_aggregate_targetbiginteger100M此处我的一个process能够分配的最大内存为20M,因为我的PGA=100M,符合上面的规则。隐含参数_smm_max_size表示一个process能够分配最大的memory.以后会陆续将总结在此发表，与大家共享SGA与PGA的结构如下图:SGA:查看SGA：Sqlpshowsga或select*fromv$sga;TotalSystemGlobalAreaFixedSizeVariableSizeDatabaseBuffersRedoBuffers289406976bytes1248600bytes176161448bytes109051904bytes2945024bytesFixedSize:包括了数据库与实例的控制信息、状态信息、字典信息等，启动时就被固定在SGA中,不会改变。VariableSize:包括了shardpool、largepool、javapool、streampool、游标区和其他结构DatabaseBuffers:数据库中数据块缓冲的地方，是SGA中最大的地方，决定数据库性能RedoBuffers:提供REDO缓冲的地方，在OLAP中不需要太大V$sgastat记录了SGA的一些统计信息V$sga_dynamic_components保存SGA中可以手动调整的区域的一些调整记录Shardpool:Shard_pool_size决定其大小，10g以后自动管理Shard_pool中数据字典和控制区结构用户无法直接控制，与用户有关的只有sql缓冲区(librarycache)将经常访问的过程或包用DBMS_SHARED_POOL.KEEP存储过程将该包pin在共享池中。手工清除共享池的内容：altersystemflushshard_pool;共享池相关的几个常用的视图:V$sqlarea记录了所有sql的统计信息，包括执行次数、物理读、逻辑读、耗费时间等V$sqltext_with_newlineV$sql_plan保存了sqlV$shared_pool_advice完全显示sql语句，通过hash_value来标示语句,的执行计划，通过工具查看对共享池的预测，可以做调整SGA的参考piece排序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$librarycache计算命中率的语句selectround(1-(physical.value-direct.value-lobs.value)/logical.value)*CacheHitRatiofromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogicalwherephysical.name=physicalreadsanddirect.name=physicalreadsdirectandlobs.name=physicalreadsdirect(lob)andlogical.name=sessionlogicalreads;100,2)BufferPINSNUMBERNumberoftimesaPINwasrequestedforobjectsofthisnamespacePINHITSNUMBERNumberoftimesallofthemetadatapiecesofthelibraryobjectwerefoundinmemoryRELOADSNUMBERAnyPINofanobjectthatisnotthefirstPINperformedsineetheobjecthandlewascreated,andwhichrequiresloadingtheobjectfromdiskOracle把从databuffer中获得的数据库叫cachehit，把从磁盘获得的脚cachemiss数据缓冲区中的数据块通过脏列表(dirtylist)和LRU列表(LRUlist)来管理。Databuffer可细分为：defaultpool、keeppool、recyclepool对应的参数为db_cache_size、db_keep_cache_size、db_recycle_size分另V表示缓冲区大小从9i开始oracle支持不同块大小的表空间，相应的可以为不同块大小的表空间指定不同块大小的数据缓冲区，不同块大小的数据缓冲区可以用相应的db_nk_cache_size来指定，其中n可以是2、4、6、16或32V$db_cache_advice对数据缓冲区的预测，可以做调整databuffer的参考V$bh、x$bh记录了数据块在databuffer中缓冲的情况，通过这个视图可以找系统中的热点块。通过下面语句找系统中top10热点快所在的热点对象：SelectWhere(select(selectWhere/*+rule*/owner,object_namefromdba_objectsdata_object_idinobjfromobjfromx$bhorderbytchdesc)rownum11);PGA:用来保存于用户进程相关的内存段。从9i开始使用PGA自动管理，pga_aggregate_target参数指定session一共使用的最大PGA内存的上限。Workarea_size_policy参数用于开关PGA内存自动管理功能，auto/manual在OLTP环境中，自动PGA管理只要设置到一定的值，如2G左右就能满足系统的要求。自动内存管理：从9i开始，sga_max_size参数设置SGA的内存大小，不能动态修改从10g开始，指定了sga_target参数后，所有的SGA组件如：sharedpool、databuffer、largepool都不用手工指定了，Oracle会自动管理。这一特性就是自动共享内存管理ASMM如果设置了sga_target=0,就自动关闭自动共享内存管理功能。Sga_target大小不能超过sga_max_size的大小。.设置好这两个参数后手动管理SGA：Altersystemsetsga_target=2000m;Altersystemsetdb_cache_size=1000m;Altersystemsetshared_pool=200m;Altersystemsetsga_target=0关闭自动共享内存管理ASMM11G以后sga+pga整个内存可以自动管理AMM,相关参数memory_max_targetmemory_target就不用关心SGA和PGA了11g手动内存管理:Altersystemsetmemory_target=3000m;Altersystemsetsga_target=2000m;Altersystemsetpga_aggregate_target=1000m;Altersystemsetmemory_target=0;-关闭自动内存管理AMMSGA+PGA最好不要超过总内存的70%