oracle 内存分配和调优

1、一直都想总结一下oracle内存调整方面的知识，最近正好优化一个数据库内存参数，查找一些资料并且google很多下。现在记录下来，做下备份。一、概述：oracle的内存可以按照共享和私有的角度分为系统全局区和进程全局区，也就是SGA和PGA（processglobalareaorprivateglobalarea）。对于SGA区域内的内存来说，是共享的全局的，在UNIX上，必须为oracle设置共享内存段（可以是一个或者多个），因为oracle在UNIX上是多进程；而在上oracle是单进程（多个线程），所以不用设置共享内存段。PGA是属于进程（线程）私有的区域。在oracle使用共享服务器模

2、式下（MTS）,PGA中的一部分，也就是UGA会被放入共享内存large_pool_size中。发张图oracle内存架构组成，按照图上面的显示可以一目了然关键的参数和参数名称：对于SGA部分，我们通过sqlplus中查询可以看到：SQLselect*fromv$sga;NAMEVALUEFixedSize454032VariableSize109051904DatabaseBuffers385875968RedoBuffers667648FixedSize:oracle的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了SGA各部分组件的信息，可以看作引导建立SGA的区

3、域。VariableSize:包含了shared_pool_size、java_pool_size、large_pool_size等内存设置DatabaseBuffers:指数据缓冲区:在8i中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle三部分内存。在9i中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。RedoBuffers:指日志缓冲区，log_buffer。在这里要额外说明一点的是，对于v$para

4、meter、v$sgastat、v$sga查询值可能不一样。v$parameter里面的值，是指用户在初始化参数文件里面设置的值，v$sgastat是oracle实际分配的日志缓冲区大小（因为缓冲区的分配值实际上是离散的，也不是以block为最小单位进行分配的），v$sga里面查询的值，是在oracle分配了日志缓冲区后，为了保护日志缓冲区，设置了一些保护页，通常我们会发现保护页大小大约是11k（不同环境可能不一样）。二、SGA内参数及设置：2.1Log_buffer对于日志缓冲区的大小设置，通常我觉得没有过多的建议，因为参考LGWR写的触发条件之后，我们会发现通常超过3M意义不是很大。作为一

5、个正式系统，可能考虑先设置这部分为log_buffer=35M大小，然后针对具体情况再调整。log_buffer是Redolog的buffer。因此在这里必须要了解RedoLog的触发事件（LGWR）1、当redologbuffer的容量达到1/32、设定的写redolog时间间隔到达，一般为3秒钟。3、redologbuffer中重做日志容量到达1M4、在DBWn将缓冲区中的数据写入到数据文件之前5、每一次commit-提交事务。上面的结论可以换句话说1、log_buffer中的内容满1/3,缓存刷新一次。2、最长间隔3秒钟，缓存刷新一次3、log_buffer中的数据到达1M，缓存刷新一次

6、。4、每次提交一个“事务”，缓存刷新一次2.2Large_pool_size对于大缓冲池的设置，假如不使用MTS，建议在2030M足够了。这部分主要用来保存并行查询时候的一些信息，还有就是RMAN在备份的时候可能会使用到。如果设置了MTS,则由于UGA部分要移入这里，则需要具体根据serverprocess数量和相关会话内存参数的设置来综合考虑这部分大小的设置。2.3Java_pool_size假如数据库没有使用JAVA,我们通常认为保留1020M大小足够。事实上可以更少，甚至最少只需要32k，但具体跟安装数据库的时候的组件相关（比如httpserver）。2.4Shared_pool_siz

7、eShared_pool_size的开销通常应该维持在300M以内。除非系统使用了大量的存储过程、函数、包,比如oracleerp这样的应用，可能会达到500M甚至更高。于是我们假定一个1G内存的系统,可能考虑设置该参数为100M，2G的系统考虑设置为150M,8G的系统可以考虑设置为200300M2.5SGA_MAX_SIZESGA区包括了各种缓冲区和内存池，而大部分都可以通过特定的参数来指定他们的大小。但是,作为一个昂贵的资源,一个系统的物理内存大小是有限。尽管对于CPU的内存寻址来说,是无需关系实际的物理内存大小的（关于这一点,后面会做详细的介绍），但是过多的使用虚拟内存导致pagein

8、/out，会大大影响系统的性能，甚至可能会导致系统crash。所以需要有一个参数来控制SGA使用虚拟内存的最大大小，这个参数就是SGA_MAX_SIZE。当实例启动后，各个内存区只分配实例所需要的最小大小，在随后的运行过程中，再根据需要扩展他们的大小，而他们的总和大小受到了SGA_MAX_SIZE的限制。对于OLTP系统，参考：系统内存SGA_MAX_SIZE值1G400-500M2G1G4G2500M8G5G2.6PRE_PAGE_SGAoracle实例启动时，会只载入各个内存区最小的大小。而其他SGA内存只作为虚拟内存分配，只有当进程touch到相应的页时，才会置换到物理内存中。但我们也许

9、希望实例一启动后，所有SGA都分配到物理内存。这时就可以通过设置PREPAGESGA参数来达到目的了。这个参数的默认值为FALSE，即不将全部SGA置入物理内存中。当设置为TRUE时，实例启动会将全部SGA置入物理内存中。它可以使实例启动达到它的最大性能状态，但是，启动时间也会更长（因为为了使所有SGA都置入物理内存中，oracle进程需要touch所有的SGA页）。2.7LOCK_SGA为了保证SGA都被锁定在物理内存中，而不必页入/页出，可以通过参数LOCK_SGA来控制。这个参数默认值为FALSE，当指定为TRUE时，可以将全部SGA都锁定在物理内存中。当然，有些系统不支持内存锁定，这个

10、参数也就无效了。2.8SGA_TARGET这里要介绍的时Oracle10g中引入的一个非常重要的参数。在10g之前，SGA的各个内存区的大小都需要通过各自的参数指定，并且都无法超过参数指定大小的值，尽管他们之和可能并没有达到SGA的最大限制。止匕外，一旦分配后，各个区的内存只能给本区使用，相互之间是不能共享的。拿SGA中两个最重要的内存区BufferCache和SharedPool来说，它们两个对实例的性能影响最大，但是就有这样的矛盾存在：在内存资源有限的情况下，某些时候数据被cache的需求非常大，为了提高bufferhit，就需要增加BufferCache，但由于SGA有限，只能从其他区“

11、抢”过来如缩小SharedPool，增加BufferCache；而有时又有大块的PLSQL代码被解析驻入内存中，导致SharedPool不足，甚至出现4031错误，又需要扩大SharedPool，这时可能又需要人为干预，从BufferCache中将内存夺回来。有了这个新的特性后，SGA中的这种内存矛盾就迎刃而解了。这一特性被称为自动共享内存管理(AutomaticSharedMemoryManagementASMM)。而控制这一特性的，也就仅仅是这一个参数SGA_TARGE。设置这个参数后，你就不需要为每个内存区来指定大小了。SGA_TARGET指定了SGA可以使用的最大内存大小，而SGA中各

12、个内存的大小由Oracle自行控制，不需要人为指定。Oracle可以随时调节各个区域的大小，使之达到系统性能最佳状态的个最合理大小，并且控制他们之和在SGA_TARGET指定的值之内。一旦给SGA_TARGET指定值后(默认为0，即没有启动八5乂乂)，就自动启动了ASMM特性。三、oracle内存调优办法当项目的生产环境出现性能问题，我们如何通过判断那些参数需要调整呢？检查ORACLE实例的LibraryCache命中率：标准:一般是大于99检查方式:select1-(sum(reloads)/sum(pins)LibrarycacheHitRatiofromv$librarycache;处理

13、措施:如果LibrarycacheHitRatio的值低于99%，应调高shared_pool_size的大小。通过sqlplus连接数据库执行如下命令，调整shared_pool_size的大小：SQLaltersystemflushshared_pool;SQLaltersystemsetshared_pool_size=设定值scope=spfile;检查ORACLE实例的DataBuffer(数据缓冲区)命中率：标准:一般是大于90%检查方式:select1-(phy.value/(cur.value+con.value)HITRATIOfromv$sysstatcur,v$sysst

14、atcon,v$=dbblockgets=consistentgets=physicalreads;处理措施:如果HITRATIO的值低于90%,应调高db_cache_size的大小。通过sqlplus连接数据库执行如下命令，调整db_cache_size的大小SQLaltersystemsetdb_cache_size=设定值scope=spfile检查ORACLE实例的DictionaryCache命中率：标准:一般是大于95%检查方式:select1-(sum(getmisses)/sum(gets)DataDictionaryHitRatiofromv$rowcache;处理措施:如

15、果DataDictionaryHitRatio的值低于95%，应调高shared_pool_size的大小。通过sqlplus连接数据库执行如下命令，调整shared_pool_size的大小：SQLaltersystemflushshared_pool;SQLaltersystemsetshared_pool_size=设定值scope=spfile;检查ORACLE实例的LogBuffer命中率：标准:一般是小于1%检查方式:select(req.value*5000)/entries.valueRatiofromv$sysstatreq,v$=redologspacerequests=redoentries;处理措施:如果Ratio高于1%,应调高log_buffer的大小。通过sqlplus连接数据库执行如下命令，调整log_buffer的大小：SQLaltersystemsetlog_buffer二设定值scope=spfile;检查undo_retention:标准:undo_retention的值必须大于max(maxquerylen)的值检查方式:colundo_retentionformata30sele