OracleDatabaseApplications性能优化方案.doc

ORACLE Database&Applications性能优化方案概述本次优化方案将围绕下面一些影响数据库运行性能优化点进行：l 操作系统vmm、aio、system limits parameters，优化操作系统设置符合Oracle数据库和群集对资源的使用方式。l 数据库配置：包括Memory usage、物理结构优化、段对象属性及备份恢复检查。l 应用优化：包括优化过度消耗资源的SQL语句及提出可行的优化建议环境准备在优化应用，也就是优化SQL语句的过程中，需要对应用的优化成果进行验证，所以需要有一个类似于生产或接近生产环境的数据库进行测试。在验证和测试完成后方可在生产环境中应用优化建议。这是必要的风险规避手段。操作系统优化Oracle对环境的运行在安装配置指南中都有要求的，尽管不按要求也可以安装和运行数据库，但对数据库的稳定和高效运行会产生不利的影响，所以需要对操作系统配置进行优化。目的是为Oracle数据库的运得提供一个更健壮和稳定的环境，提高数据库的可用性和性能。操作系统优化和诊断需要root帐户。针对操作系统优化主要涉及到以下方面：l VMOVMO优化主要针对VMM参数进行优化。在Oracle环境中主要使用的是计算内存，但AIX默认会兼顾Filesystems Cache。但在数据库环境中Oracle已经有自已的内存管理机制，所以需要优化操作系统的内存使用方式，使其迎合Oracle的内存使用习惯，从而提高内存的管理效率。尽管IBM宣称在AIX中默认优先保证计算内存，但是这是需要从Filesystems Cache”偷取”内存给Compute Memory，这种机制一但发生，会导至内存的分配过程非常缓慢，严重影响数据库的性能。需要对vmo优化涉及以下参数：Typical vmo settings for Oracle : lru_file_repage=0 (default=1) (AIX 5.2 ML04 or later，AIX6.x default=0) Forces file pages to be repaged before computational pages minperm%=5 (default 20) Target for minimum % of physical memory to be used for file system cache maxperm%=15 ( default 90) Target for maxmum % of physical memory to be used for file system cache，limited maxclient%，maxclient%=maxperm%#vmo L lru_file_repage#vmo L minperm%#vmo L maxclient%#vmo L maxperm%Setting VMO Parameter:#vmo p o lru_file_repage=0#vmo p o minperm%=5#vmo p o maxclient%=13#vmo p o maxperm%=15优化和查看VMO参数需要root帐户权限。l AIO优化异步I/O的服务器数量和队列长度来提高异步I/O的响应带宽，需要对异步I/O的设置进行查看及优化：AIX 5L：#lsattr El aio0#aioo aAIX 6.x:#ioo a |grep aioExample Output：#lsattr -El aio0autoconfig available STATE to be configured at system restart Truefastpath enable State of fast path Truekprocprio 39 Server PRIORITY Truemaxreqs 16384 Maximum number of REQUESTS Truemaxservers 300 MAXIMUM number of servers per cpu Trueminservers 150 MINIMUM number of servers True主要优化maxservers和maxreqs这两个参数。上述输出是Oracne和IBM联合实验室给出的建议值。l SYS ParametersOS最大用户进程数限制：#lsattr -El sys0 -a maxuproc对于Oracle环境来说，至少建议设置10240，如果有其他的应用程序建议设置更大的值。通过下例命令来修改参数设置：#chdev -l sys0 -a maxuproc=10240或#smit chgsys数据库配置优化l Datafiles优化必须关闭数据文件的autoextendsible参数，特别对于使用祼设备做为数据文件更需要立即关闭，已防止数据文件损坏。通过检查dba_data_files视图查看数据库中是否有为autoextendsible=YES的数据文件，如有，必须立即通过:alter database datafile xxx autoextend off;关闭自动扩展开关。l 内存分配内存使用在Oracle环境中是为了降低物理I/O产生量，但又不能过份地对物理内存产生压力，所以内存分配应遵循下列原则：OS(4GB)+PGA+SGA+Processes*10Mshow parameter control_files如果没有三路，建议增加到三路。需要停止数据库l Redo&Checkpoint优化一般建议日志文件的切换在十五分钟以上进行一次，太频繁的切换会影响数据库事务的性能。通过v$log_history视图查看日志切换的历史记录，通过v$log查看日志的大小。建议如果切换的间隔小于十五分钟必须增加日志组的大小。l 备份可恢复性测试方案对象的logging检查：要保证对象是可恢复的，需要对segments的logging属性进行检查，必须将tables&table partitions和indexes&index partitions的logging设为YES。通过dba_tables，dba_tab_partitions，dba_tab_subpartitions检查表或表分区的logging属性。通过dba_indexes，dba_ind_partitions，dba_ind_subpartitions检查索引或索引分区的logging属性。一般情况下，备份尽管从日志上是正常的，但并不代表在需要时是可用的，所以需要定期对备份是否可恢复进行测试。备份可恢复性测试主要有两个目的：一、 备份介质中的备份片是否可用；二、 使用备份恢复数库时，最需要多长时间，能否满足业务最大停机时间要求；如果发现不能满足上述方案的要求，应及时调整备份策略，因应备份策略是由业务的要求决定的，而不是DBA拍脑门想出来的。测试建议的方法如下。a) 在数据库中创建测试表空间，测试表空间使用的RAW必须有明显的名字区分，如还test字样；b) 模拟业务生成持续的数据变化，产生归档日志；c) 随业务表空间一起备份到备份介质中；d) 在测试表空间中模拟数据损坏；e) 针对测试表空间模拟进行介质恢复；记录起始时间；f) 验证测试表空间数据恢复是否成功，成功则记录完成时间；通过对测试表空间的恢复来验证备份介质是否可用。但千万不能用生产数据表空间进行测试。通过测试表空间的恢复时间，以及其所占整个数据库的比例来推算在当前的数据量下整个数据库还原/恢复最少需要多长时间。如果恢复时间在业务可容忍的最大停机时间内，测试通过。否则需要优化备份略和提高备份介制的带宽来缩短还原/恢复时间。备份可恢复性测试至少一季度做一次，并且需要有测试记录。应用优化应用优化从两方面入手：l 通过AWR中反映出的TOP SQL，包括Top Elapsed time，Top Buffer gets、Top Disk Reads这几个方面进行优化；l 从AWR不能记录的，并且未重用的Cursors来发现Top SQL，需要从v$sqlarea视图来发现这些漏网的SQL语句进行优化。优化应用可能会有以下两种优化手段：l 优化SQL语句的Ac