oracle性能优化

Oracle性能优化一、SGA1.Sharedpool的优化应该放在优先考虑，因为一个cachemiss在sharedpool中发生比在databuffer中发生导致的成本更高，由于dictionary数据一般比librarycache中的数据在内存中保存的时间长，所以关键是librarycache的优化。Gets：（parse）在namespace中查找对象的次数；Pins：（execution）在namespace中读取或执行对象的次数；Reloads：(reparse)在执行阶段librarycachemisses的次数，导致sql需要重新解析。1）检查v$librarycache中sqlarea的gethitratio是否超过90％，如果未超过90％，应该检查应用代码，提高应用代码的效率。Selectgethitratiofromv$librarycachewherenamespace=’sqlarea’;2)v$librarycache中reloads/pins的比率应该小于1％，如果大于1％，应该增加参数shared_pool_size的值。Selectsum(pins)“executions”,sum(reloads)“cachemisses”,sum(reloads)/sum(pins)fromv$librarycache;reloads/pins1%有两种可能，一种是librarycache空间不足，一种是sql中引用的对象不合法。3）sharedpoolreservedsize一般是sharedpoolsize的10％，不能超过50％。V$shared_pool_reserved中的requestmisses＝0或没有持续增长，或者free_memory大于sharedpoolreservedsize的50%，表明sharedpoolreservedsize过大，可以压缩。4）将大的匿名pl/sql代码块转换成小的匿名pl/sql代码块调用存储过程。5）从9i开始，可以将executionplan与sql语句一起保存在librarycache中，方便进行性能诊断。从v$sql_plan中可以看到executionplans。6）保留大的对象在sharedpool中。大的对象是造成内存碎片的主要原因，为了腾出空间许多小对象需要移出内存，从而影响了用户的性能。因此需要将一些常用的大的对象保留在sharedpool中，下列对象需要保留在sharedpool中：a.经常使用的存储过程；b.经常操作的表上的已编译的触发器c.Sequence，因为Sequence移出sharedpool后可能产生号码丢失。查找没有保存在librarycache中的大对象：Select*fromv$db_object_cachewheresharable_mem10000andtypein('PACKAGE','PROCEDURE','FUNCTION','PACKAGEBODY')andkept='NO';将这些对象保存在librarycache中：Executedbms_shared_pool.keep(‘package_name’);对应脚本：dbmspool.sql7)查找是否存在过大的匿名pl/sql代码块。两种解决方案：A．转换成小的匿名块调用存储过程B．将其保留在sharedpool中查找是否存在过大的匿名pl/sql块：Selectsql_textfromv$sqlareawherecommand_type=47andlength(sql_text)500;8）Dictionarycache的优化避免出现Dictionarycache的misses，或者misses的数量保持稳定,只能通过调整shared_pool_size来间接调整dictionarycache的大小。Percentmisses应该很低：大部分应该低于2％，合计应该低于15％Selectsum(getmisses)/sum(gets)fromv$rowcache;若超过15％，增加shared_pool_size的值。2、BufferCache1）granule大小的设置，db_cache_size以字节为单位定义了defaultbufferpool的大小。如果SGA128M，granule=4M,否则granule＝16M，即需要调整sga的时候以granule为单位增加大小，并且sga的大小应该是granule的整数倍。2)根据v$db_cache_advice调整buffercache的大小SELECTsize_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_readsFROMv$db_cache_adviceWHERENAME='DEFAULT'ANDadvice_status='ON'ANDblock_size=(SELECTValueFROMv$parameterWHERENAME='db_block_size');estd_physical_read_factor=13)统计buffercache的cachehitratio90%，如果低于90％，可以用下列方案解决：增加buffercache的值；使用多个bufferpool；Cachetable；为sortingandparallelreads建独立的buffercache；SELECTNAME,valueFROMv$sysstatWHERENAMEIN('sessionlogicalreads','physicalreads','physicalreadsdirect','physicalreadsdirect(lob)');Cachehitratio=1-(physicalreads-physicalreadsdirect-physicalreadsdirect(lob))/sessionlogicalreads;Select1-(phy.value-dir.value-lob.value)/log.valuefromv$sysstatlog,v$sysstatphy,v$sysstatdir,v$sysstatLOBwherelog.name='sessionlogicalreads'andphy.name='physicalreads'anddir.name='physicalreadsdirect'andlob.name='physicalreadsdirect(lob)';影响cachehitratio的因素：全表扫描；应用设计；大表的随机访问；cachehits的不均衡分布4）表空间使用自动空间管理，消除了自由空间列表的需求，可以减少数据库的竞争3、其他SGA对象1）redologbuffer对应的参数是log_buffer，缺省值与OS相关，一般是500K。检查v$session_wait中是否存在logbufferwait,v$sysstat中是否存在redobufferallocationretriesA、检查是否存在logbufferwait：Select*fromv$session_waitwhereevent=’logbufferwait’;如果出现等待，一是可以增加logbuffer的大小，也可以通过将log文件移到访问速度更快的磁盘来解决。B、Selectname,valuefromv$sysstatwherenamein(‘redobufferallocationretries’,’redoentries’)Redobufferallocationretries接近0，小于redoentries的1％，如果一直在增长，表明进程已经不得不等待redobuffer的空间。如果Redobufferallocationretries过大，增加log_buffer的值。C、检查日志文件上是否存在磁盘IO竞争现象Selectevent,total_waits,time_waited,average_waitfromv$system_eventwhereeventlike‘logfileswitchcompletion%’;如果存在竞争，可以考虑将log文件转移到独立的、更快的存储设备上或增大log文件。D、检查点的设置是否合理检查alert.log文件中，是否存在‘checkpointnotcomplete’；Selectevent,total_waits,time_waited,average_waitfromv$system_eventwhereeventlike‘logfileswitch(check%’;如果存在等待，调整log_checkpoint_interval、log_checkpoint_timeout的设置。E、检查logarchiver的工作Selectevent,total_waits,time_waited,average_waitfromv$system_eventwhereeventlike‘logfileswitch(arch%’;如果存在等待，检查保存归档日志的存储设备是否已满，增加日志文件组，调整log_archiver_max_processes。F、DB_block_checksum=true，因此增加了性能负担。（为了保证数据的一致性，oracle的写数据的时候加一个checksum在block上，在读数据的时候对checksum进行验证）2）javapool对于大的应用，java_pool_size应=50M，对于一般的java存储过程，缺省的20M已经够用了。3）检查是否需要调整DBWnSelecttotal_waitsfromv$system_eventwhereevent=’freebufferwaits’;二、数据库配置和IO问题降低磁盘的IO分散磁盘的IO表空间使用本地管理1、将文件分散到不同的设备上1）将数据文件与日志文件分开2）减少与服务器无关的磁盘IO3）评估裸设备的使用4）分割表数据2、表空间的使用系统表空间保留给数据字典对象创建本地管理表空间以避免空间管理问题将表和索引分散到独立的表空间中使用独立的回滚表空间将大的数据库对象保存在各自独立的表空间中创建一个或多个独立的临时表空间下列数据库对象应该有单独的表空间：数据字典、回滚段、索引、临时段、表、大对象3、检查IO统计数据Selectphyrds,phywrts,d.namefromv$datafiled,v$filestatfwheref.file#=d.file#orderbyd.name;检查最有可能引起磁盘IO瓶颈的文件。4、分割文件可以通过RAID和手工进行Altertabletable_nameallocateextent(datafile‘fiile_name’size10M);但手工操作工作量很大。5、优化全表扫描操作1）检查有多少全表发生：Selectname,valuefromv$sysstatwherenamelike‘%tablescan%’;tablescans(shorttables)/tablescans(longtables)与全表扫描相关，如果tablescans(longtables)的值很高，说明大部分的tableaccess没有经过索引查找，应该检查应用或建立索引，要确保有效的索引在正确的位置上。合理的DB_FILE_MULTIBLOCK_READ_COUNT能减少tablescan需要调用的IO次数，提高性能（与OS相关）。2）查看fulltablescan操作：Selectsid,serial#,opname,target，to_char(start_time,’HH24:MI:SS’)“start”,(sofar/totalwork)*100“percent_complete”f