Oracle数据库优化之系统全局区

Oracle数据库优化之系统全局区(SGA)调整操作系统的内存需求 减少页的换入换出; 将SGA置于主存之中使用SGA的主要目的是为了在内存中存储数据，以利于快速访问。通过设置初始化参数PRE_PAGE_SGA=YES，在数据库启动时，可以将整个SGA读入内存，这样会减少在启动后ORACLE达到全部性能的总的时间。使用如下命令可以查看SGA所分配的内存以及其内部的结构：SVRMGR SHOW SGATotal System Global Area 107720688 bytesFixed Size 69616 bytesVariable Size 90701824 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytes 为单个用户分配足够的内存调整redo log缓冲区参数LOG_BUFFER指定了REDO LOG的缓冲区的保留大小。LOG写进程(LGWR)在该缓冲区被填充时总是运行的，在新的LOG进入缓冲区时，原来的LOG应已经写入磁盘。调整私有sql和pl/sql区 标识不必要的语法分析调用1. 在sql跟踪工具有效时运行应用2. 查看视图V$SQLAREA viewSVRMGR select sql_text,parse_calls,executions from v$sqlarea ;如果parse_calls值接近execution值，可能就是不断地对sql语句进行语法分析3. 执行如下查询：select *from v$statname where name in (parse_count(hard),execute_count);其结果类似于:statistic#, name-100 parse_count90 execute_count然后执行如下查询：select * from v$sesstat where statistics# in(90,100) order by value , sid ; 减少不必要的语法分析调用初始化参数open_cursors的最大值依赖于操作系统，最少值为5调整共享池数据字典或库快存的没有命中，其开销大大多于缓冲快存的没有命中，因此，首先应该为共享池分配足够的空间。使用如下语句可以确定库快存和数据字典快存的命中率：select(sum(pins-reloads)/sum(pins)“Lib Cache”from v$librarycache ;select (sum(gets-getmisses-usage-fixed)/sum(gets)“Row Cache”from v$librarycache;共享池中的自由内存可以查看：select * from v$sgastat where name=free memory;当然，如果共享池满了并不一定存在问题，只要上面所说的比率接近于1，就不需要增加共享池大小;如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.95，那么需要增加共享池的大小。 调整Library Cache1. 检查库快存的活动select sum(pins) “Executions”,sum(reloads)”Cache Misses while Executing” from v$librarycache ;“Executions”列指明sql语句，pl/sql块和实体定义被访问执行了的次数，”Cache Misses while Executing”指明其中没有命中的次数。2. 减少库快存的非命中1、 分配更多的库快存(可以增加初始化参数shared pool size的值;为了利用增加的共享sql区，增加初始化参数open cursors的值); 2、 尽可能使用标准的sql语句(sql语句或pl/sql块必须一致，包括字符和空格;sql语句或pl/sql块中对schema实体的引用必须解析到同 一schema的同一对象;sql语句中试用的变量的名字和数据类型必须匹配;sql语句必须使用相同的优化方法和优化目标);尽可能使用标准的sql语句，策略： 语句中尽量使用变量而不要使用常量 确保应用用户不会改变优化方法和目标 标准化的变量命名和空格转换 尽可能使用存储过程3、 使用cursor_space_for_time加速共享sql区的访问：该参数指定是否共享sql区可以释放，如果为false(默认值)，一个共享sql区就可以被释放;如果为true，一个共享的sql区只存在所有与其相关的游标关闭后才可以被释放。如果库快存在语句执行时有非命中，不要将其设置为true，否则对新的sql语句将没有空间。 调整Data Dictionary Cache1、 监视数据字典快存的活动select sum(gets) “Data Dictionary Gets”, sum(getmisses) “Data Dictionary Cache Get Misses” from v$rowcache ;2、 减少数据字典快存的非命中对频繁访问的数据字典快存没命中和命中比应少于10%-15%。要增加数据字典快存可用的内存数，需要增加初始参数shared_pool_size的值。 调整多线程服务器的共享池查询动态表v$sesstat可以确定会话信息的大小： select sum(value)|bytes”Total memory for all sessions” from v$sesstat, v$statname where name=session uga memort and v$sesstat statistic#=v$statname.statistic#;显示信息如下：Total memory for all sessions-157125 bytes结果指明当前分配给所有会话的内存。 调整共享池的保留空间shared_pool_reserved_size为大的存储保留的shared_pool_size总量;shared_pool_reserved_min_alloc-控制保留内存的分配;要创建一个保留列表，shared_pool_reserved_size必须大于shared_pool_reserved_min_alloc。优化共享池：用以下语句检测命中率：select sum(pins)pinned,sum(reloads)reloaded from v$librarycachepinned reloaded- -2474 17命中率=pinned/ reloaded若命中率低可增大INIT.ORA中SHARED_POOL_SIZE的值。调整快速缓存 利用缓存的命中率评价快速缓存的活动select name,value from v$sysstat where name in (db block gets,consistent_gets,physical reads);db block gets, consistent_gets其和为数据请求总数，physical reads为数据请求导致访问磁盘数据文件的总数。增加db_block_buffers应该遵循如下规则： 缓存命中率低于0.9; 前面增加的db_block_buffers有效 提高缓冲区命中率如果缓冲命中率低，如小于60%或70%，那么需要增加缓冲区中的缓存数。方法是增大db_block_buffers的值。表v$recent_bucket的每一行反映在缓存中增加一个缓冲区的相应性能值，其中rownum列比可能增加到缓存的缓冲区数小1;count列通过增加缓冲区数rownum+1到缓存，增加的缓冲区命中。查询v$recent_bucket视图：select sum(count) ach from v$recent_bucket where rownum=90 ;计算命中率(ACM为增加的非命中次数)：Hit Ratio=1-(physical reads + ACM/(db block gets+consistent gets);也可以将100个缓冲区分为4个25个缓冲区段： select 25*TRUNC(ROWNUM/25)+1|to|25*(TRUNC(ROWNUM/25+1)”Interval”,sum(count)”Buffer Cache Hits” from v$CURRENT_BUCKET where rownum0 group by TRUNC(ROWNUM/25);其中INTERVAL为缓冲区段，BUFFER CACHE HITS为对应段的缓存命中率。优化数据库缓冲区高速缓存:select name,value from v$sysstat where name in (db block gets,consistent gets,physical reads) ;NAME VALUE- -db block gets 3700consistent gets 17603physical reads 523计算公式：Hit Ratio = 1-(physical reads / ( db block gets + consisten gets)若命中率低可以加大INITXXX.ORA中DB_BLOCK_BUFFER的值。调整多缓冲池 多缓冲池特性概述可以使用keep缓冲池来维护缓存中的实体，使用recycle缓冲池来防止实体占用缓存中不必要的空间;可以将大的数据段置于独立的recycle缓存中，将小的数据段置于独立的keep缓存中。 何时使用多缓冲池1. 查找oracle内部实体数据段的数目：select data_object_id,object_type from user_objects where object_name= ;2. 查找对应该段名的缓存中的缓冲区数：select count(*) buffers from x$bh where obj= ;3. 查找实例中的缓冲区总数：select value “total buffers ” from v$parameter where name=db_block_buffers ;4. 计算当前有指定段使用的缓存所占的比率:%cache used by segment_name=buffers(step 2)/buffers(step 3); 使用多缓冲池调节缓存减少I/O操作;隔离缓存中的实体;限制实体在缓存中的一个部分 使多缓冲池生效定义新缓冲池：使用初始参数BUFFER_POOL_NAME来定义缓冲池，每个缓冲池可以指定两个属性：缓冲池中的缓冲区数;分配该缓冲池的LRU latches数。用于定义缓冲池的初始参数有：BUFFER_POOL_KEEP-定义keep缓冲池BUFFER_POOL_RECYCLE-定义recycle缓冲池DB_BLOCK_BUFFERS-定义数据库实例的缓冲区数DB_BLOCK_LRU_LATCHES-定义数据库实例的LRU latches数Oracle8定义了三个缓冲池：keep，recycle和default。其中default缓冲池是已经存在的。 使用多缓冲池为一个实体建立一个默认缓冲池，语法为：BUFFER_POOLKEEP | RECYCLE | DEFAULT 如何为各缓冲池设置大小1. KEEP缓冲池使用keep缓冲池的目的是将实体保留在内存，避免I/O操作select physical_reads,block_gets,consistent_gets from v$buffer_pool statistics where name=KEEP;计算缓冲池命中率的公式：Hit Ratio=1-physical_reads/(block_gets+consistent_gets)2. Recycle缓冲池使用recycle缓冲池的目的是为了清除内存中的不再使用的数据块，如果“free buffer waits”统计数总是很高，可以确定recycle缓冲池过小：确定recycle缓冲池大小的办法：使recycle缓冲池失效运行系通到稳定状态，查看default缓冲池中由本来需要置于recycle缓冲池中数据段的缓冲区数，除以4，该结果就可以用作recycle缓冲池的大小。3. 确定数据段置于keep还是recycle缓冲池对于至少两倍DEFAULT缓冲池大小，并且占用系统整个I/O的一定百分比的数据段，那么最好放置于recycle缓冲池;对于大小小于DEFAULT缓冲池的10%，并且占用至少系统整个I/O的1%的数据段，那么最好放置于keep缓冲池;如果对表空间超过一个段时，可以通过查询V$SESSION_WAIT确定每个段的I/O操作。 如何识别和减少LRU Latch竞争LRU latches规划了那些缓存中最近最少使用的缓冲区列表，使用DB_BLOCK_LRU_LATCHES参数可以设置数据库实例中的latches总数。可以通过如下语句确定系统中是否有latch竞争：select child#,sleeps/gets ratio from v$latch_children where name=cache buffers lru chain ;每个LRU latch的非命中率应少于1%，任何大于1%的latch说明存在竞争，通过如下语句查找出来：select name from v$buffer_pool_statistics where lo_setid=child_latch_numbers ;通过增加系统的LATCHES总数和相关缓冲池的LATCHES数可以减少LRU latch竞争。所允许的LATCHES最大值应少于：number_of_cpus*2*3和number_of_buffers/50调整排序区如果经常进行大的排序，应增大参数SORT_AREA_SIZE的值，将参数SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE联合使用，更有利于使用大的排序区。重新分配内存在重新定义了oracle内存结构、调整了库快存、数据字典快存和快速缓存后，如果减少了这些结构中某一个结构的内存使用，就可能需要将内存分配到其他结构中;在改变了oracle内存结构以后，oracle的内存需求也会改变。减少整个内存使用采用如下方法：增加可用内存总数;减少内存使用优化数据字典高速缓存：select sum(gets)Read Requests,sum(getmisses)Reads not in Memory from v$rowcache ;Read Requests Reads not in Memory- -4764 145命中率=Read Requests/ Reads not in Memory若低于85% 应增加 SHARED_POOL_SIZE优化游标：可用以下语句检测游标的命中率：select * from v$session_cursor_cache ;select * from v$system_cursor_cache ;优化游标应综合考虑，若打开的游标过多则应用程序需要的内存增大，可能发生共享池内存被换出到虚存从而影响性能。 以下三个INIT.ORA中的参数可影响游标的性能：CLOSED_CACHED_OPEN_CURSORS 指示oracle当执行commit或rollback语句时，是否显式的关闭游标。如果游标需要反复使用可以设为false，否则应为true从而减少内存占用。CURSOR_SPACE_FOR_TIME 若为true指示oracle只要在sql共享区中有打开的游标就一直保存该区域，若其值为false oracle可将其换出内存，即使游标仍打开。只有共享池能保存所有打开的游标时才将其设为true。OPEN CURSORS设定一个会话可同时打开的游标数。注释：以上增加SGA分配的调整以SGA不被换出实存为限，否则SGA部分换出实存反而降低Oracle性能。