（计算机应用技术专业论文）erp系统中oracle数据库性能调整与优化.pdf

中南民族大学硕士学位论文 i 摘 要 随着 oracle 数据库系统被广泛应用到各行各业的应用领域， 对数据库系统性能优化的研究越来越重要。 分析了国内外对数据库性 能优化的策略，结合已有的调优方法对发版前的 erp 产品进行性能 测试，针对产品存在的性能问题进行诊断、分析和优化，降低了 erp 产品的开发成本。 按照数据库性能优化原则配置数据库，包括：合理配置数据库参 数， 合理规划磁盘分布和表空间的初始存储设置。 使用hp loadrunner9.5 测量 erp 系统的性能，对比优化目标分析系统存在的问题。研究了 对性能瓶颈的诊断方法，结合 vmstat、top 命令分析操作系统性能数 据，初步判断性能瓶颈；结合 statspack报告、动态性能视图进一步诊 断数据库性能，定位性能瓶颈。 提出了一套针对 erp 系统的优化方法。主要优化数据库（内存 参数）和应用程序（sql 语句、索引）。 实验证明，经过调整优化，响应时间、服务器资源占用率等指标 达到了预期目标，符合发布的要求。 关键词: oracle 数据库，优化，内存，sql 语句，索引 erp 系统中 oracle 数据库性能调整与优化 ii abstract with oracle database system is widely used in all walks of life applications, the database performance optimization of the system more and more important. analysis of domestic and international strategies of optimizing database performance, combined with the existing tuning methods on pre- made version of erp products for performance testing, performance issues for products in the diagnosis, analysis and optimization, reducing the erp product development cost. in accordance with the principles of database performance optimization built a database environment, including: rational allocation of database parameters, distribution and rational planning disk space for the initial storage settings table. performance testing tools measure the performance of erp systems, compared to objective analysis system optimization problems. study the performance bottleneck diagnostic method. combined with vmstat, top command of the operating system performance data, the initial judge performance bottlenecks; combination statspack report, the dynamic performance view to further diagnose database performance, positioning performance bottlenecks. a set of optimization methods for erp system. main optimizing the database (memory parameters) and applications (sql statements, indexes). experimental results show that after adjustment and optimization, response time, server resources occupancy rate, etc., to achieve the desired goals, meet the requirements of release keywords: oracle database， optimization， memory， sql statement， index 中南民族大学 学位论文原创性声明 本人郑重声明：所呈交的论文是本人在导师的指导下独立进行研究所 取得的研究成果。除了文中特别加以标注引用的内容外，本论文不包含任 何其他个人或集体已经发表或撰写的成果作品。对本文的研究做出重要贡 献的个人和集体，均已在文中以明确方式标明。本人完全意识到本声明的 法律后果由本人承担。 作者签名： 日期： 年 月 日 学位论文版权使用授权书 本学位论文作者完全了解学校有关保留、使用学位论文的规定，同意 学校保留并向国家有关部门或机构送交论文的复印件和电子版，允许论文 被查阅和借阅。本人授权中南民族大学可以将本学位论文的全部或部分内 容编入有关数据库进行检索，可以采用影印、缩印或扫描等复制手段保存 和汇编本学位论文。 本学位论文属于 1、保密，在_年解密后适用本授权书。 2、不保密。 （请在以上相应方框内打“” ） 作者签名： 日期： 年 月 日 导师签名： 日期： 年 月 日 中南民族大学硕士学位论文 1 第 1 章 绪 论 1.1 研究背景 浪潮erp- ps10管理系统是采用先进管理思想和开发工具向企业推出的一套 企业资源计划全面解决方案。 该系统包括总账、 固定资产等财务管理、 物流管理、 生产管理和人力资源管理等几大子系统， 企业通过组合部分子系统或单独使用子 系统，来处理企业内部局域网和广域网的业务，既对内满足局域网的信息处理、 对外满足广域网的 web查询以及分销和异地库存业务。系统通过将企业工作流 程中各子系统数据相互关联和调用， 最后收集各流程中相关子系统的数据提供给 决策层智能的管理服务，保证企业稳定、快速发展。 浪潮 erp- ps10 管理系统发布前力求做到稳定、高效，根据以前版本上线后 的运行情况， 得知系统在运行一段时间后随着并发用户的访问和用户数据量的增 多会出现响应时间和吞吐量下降、某些事务死锁等性能问题，分析发现对数据库 的配置、设计不佳或使用了效率差的程序是这些性能问题出现的主要原因。为了 尽早发现程序潜在的性能问题，在产品开发和调试阶段应对后台 oracle 数据 库进行性能测试， 及早发现程序中存在的并发死锁问题及在并发和大数据量下的 效率问题，并针对这些问题进行修复，以降低开发成本。 1.2 研究目标和内容 本文的研究目标是：分析 oracle 数据库系统性能优化原理，结合浪潮 erp- ps10 管理系统的测试环境，研究并提出一套可行的性能测试、诊断及优化 方法。 本文的研究内容如下： (1) 研究浪潮 erp- ps10 管理系统的应用体系架构、网络拓扑、工作原理及 数据库的应用管理。 (2) 研究 oracle 数据库性能优化技术。 (3) 针对上线前的浪潮 erp- ps10 信息管理系统，研究内部性能优化的实施 erp 系统中 oracle 数据库性能调整与优化 2 步骤。 (4) 使用性能测试工具和性能管理工具， 研究对该 erp 系统进行性能测试和 性能分析的过程， ，总结系统中存在的主要性能问题及原因。 (5) 针对 erp 系统中数据库中几个关键的性能问题进行优化， 并检验优化效 果。 1.3 本文组织结构 本文共分为 7 章，每章的内容如下： 第 1 章 阐述了本课题的研究背景、研究目标、内容和本文组织结构。 第 2 章 介绍浪潮 erp- ps10 管理系统的基本架构、功能、数据来源及管理 和本次实验采用的系统配置。 第 3 章 介绍 oracle数据库性能优化技术、性能评价指标、优化框架和方 法，并形成一个优化策略，指导优化实践。 第 4 章 按照数据库性能优化原则搭建数据库环境，建立优化目标，进行性 能测试。 第 5 章 使用各种性能测量工具检测系统性能，诊断并定位性能瓶颈，提出 性能调整方案，实施优化工作。 第 6 章 对系统再次测试，验证优化成果。 第 7 章 总结 中南民族大学硕士学位论文 3 第 2 章 erp 系统及实施部署 本章描述了浪潮 erp- ps10 管理系统的基本架构及应用，并简单介绍了本次 实验采用的系统配置。 2.1 系统架构及工作原理 浪潮 erp- ps10 管理系统采用 c/s 体系结构，由客户应用程序、中间件和数 据库服务器程序 3 部分组成， 数据库服务器通过中间层软件直接给客户端提供服 务，在小型的单机模式下可省略安装中间层，由客户端直接操作数据库。服务器 支持 ms sql server、sybase、oracle等大型数据库管理系统，独立地存储 和管理数据； 中间层服务给应用程序提供更好的服务器应用接口，由系统授权、 环境配置、智能更新监听器、预警消息、账套管理、用户维护工具等各种为管理 员提供服务的程序构成；本地客户端由浪潮 erp- ps10 软件的财务、物流、生产 和人力资源管理几大子系统组成。 这种由前台程序操作数据库的架构使服务器和 网络的压力比较大。系统架构如图 2.1 所示。 决策层 战略管理、全面预算、商务智能 财务 物流 生产 人力资源 o a 行业应用 管理层 总账管理 报表管理 。 采购管理 销售管理 。 生产数据 生产规划 。 员工规划 培训管理 。 行政事务 系统门户 。 银行接口 g s p 管理 。 平台 界面设计、预警平台、元数据。 安全系统 数据库 运行 环境 系统运行平台 图 2.1 erp 系统架构 管理层和决策层是企业信息化建设的 2 个阶段， 管理层的管理对象是企业工 作流程， 它要求在对流程中的基础数据进行管理的同时对企业内部各部门之间的 业务进行协调和优化，决策层再糅合管理层的各种数据，得出更优化的数据控制 erp 系统中 oracle 数据库性能调整与优化 4 模型和决策支持信息，以推动企业的发展。后台数据库系统层由运行在各种操作 系统平台（如 windows、linux、unix）上的大型关系型网络数据库系统组成， 对数据进行管理并提供安全保障。后台数据库系统层提供的服务包括：用户的口 令、各种功能、数据权限的检验，数据的增删改、查询的支持，备份历史数据管 理这些所有的支持，以及支持数据仓库管理。这些对数据库的操作通过前后台的 相互协作使企业的业务操作紧密结合，连为一体。该 erp 系统主要在白天运行， 要求完成对需求的及时响应，应符合 oltp 联机事务处理的需求。 2.2 网络拓扑 浪潮 erp- ps10 信息管理系统最普遍的两种网络应用分为单机和局域网模 式，单机模式为一台本地机器同时安装数据库服务器和客户应用程序，局域网模 式由企业局域网内的 n 台机器组成，选择一台做为数据库服务器，或另加一台 做为应用服务器，其他的多台机器做客户端。两种网络模式下数据库服务器端安 装大型关系型网络数据库，客户端安装浪潮 erp- ps10 信息管理系统客户软件。 erp 系统局域网拓扑结构如图 2.2 所示。 图 2.2 erp 系统局域网拓扑结构 中南民族大学硕士学位论文 5 2.3 产品部署及配置 本实验的专署服务器端不再安装其他应用软件，前台 pc 机上安装中间层和 客户端软件，实验采用的系统详细配置如表 2.1 所示。 表 2.1 系统配置表 数据库服务器 客户机 cpu: intel(r)pentium(r)4cpu2.80ghz,2.7 9ghz intel(r)pentium(r)4cpu2.80ghz， 2.79ghz 内存: 1g 256m 磁盘： 15g 80g 操作系统 redhat enterprise linux 4 windows xp professional sp3 其它软件 系统 oracle9.2.0.4.0 loadrunner9.5；浪潮erp- ps管理软 件客户端；其他 erp 系统中 oracle 数据库性能调整与优化 6 第 3 章 oracle 数据库性能优化技术 本章分析性能优化的目标和框架， 提炼有效的性能分析方法， 介绍 oracle 性能优化各个层面的调优技术，为后续 erp 系统调优的理论基础。 3.1 优化的目标 不同的系统有不同的优化目标， 如多事务短操作的联机事务处理 oltp 系统 要求尽可能短的响应时间，而经常进行大批量事务的 dss 系统要求高吞吐量， 因此在优化前应根据产品的特点和用户的要求建立一个合理的、可行的、可量化 的目标1- 3。本文对 erp 产品制定优化方案之前专门针对用户性能需求进行了调 研，以产品以往版本的基本数据量、并发用户及性能状况为依据，确定本次产品 发版需要达到的性能目标，这些都是建立在用户需求之上的。只要达到了这一目 标即可停止优化，不必再去浪费精力做无休止的调整。 3.2 优化框架和方法 提高 oracle数据库整体的性能应该在以下 4 个层次上进行调整和优化， ： （1）操作系统和硬件平台层，在该层应关注虚拟内存交换、 i/o 阻塞和 cpu 超载现象，必要时可选择增加 cpu 的个数、使用 raid 技术和增大系统内存； （2）数据库配置层，该层涉及数据库初始参数的设置和数据库采用哪种连 接模式（如共享服务器模式或专用服务器模式） ； （3）数据库设计层，该层涉及各种表空间、表、索引等数据库对象的存储 和分配； （4）sql 语句运用层，在该层应关注是否使用合理的索引、连接顺序、查 询条件和绑定变量5,6。 在对处于软件生命周期的不同阶段的产品优化时应重点关注不同的层次。 例 如，已开发完还未上线的产品应着重着重关注第 2 层和第 4 层7，上线后的产品 中南民族大学硕士学位论文 7 还应配置合适的操作系统和硬件平台层等。 oracle 10g以后的版本中企业管理 器采用了网页浏览器的形式，并且实现了自动收集各种 cpu、内存、磁盘 i/o 等 服务器资源占用率、运行程序语句时的各种消耗等性能数据的功能，dba 可以 配合使用企业管理器和这些统计数据对 oracle 系统进行调整5,6。 oracle 数据库性能优化的方法在各种资料中有不同的介绍，大体可划分 为两类：基于命中率和基于等待事件。本文采用了一种目前被广泛使用的可靠的 调整方法，从一个点出发，在基于等待事件的基础上层层深入，逐渐扩散到 oracle 技术的各个层面，在各个问题的交接处查找出确切的问题。对系统的 优化是反复的过程，包括以下几个步骤7： （1）建立可行的预期调整目标； （2）测试数据库性能，看是否符合预期性能目标，如果不符合转向下一步； （3）检查操作系统的性能指标，初步判断操作系统性能瓶颈； （4）检查数据库层面等待事件，分析产生这些等待事件的来源； （5）在这些问题的结合处定位并找出最本质的性能问题并采取相应的优化 措施； （6）再次测试系统的性能，看问题是否解决； （7）重复前面调整的几个步骤直至系统性能符合预期目标。 3.3 数据库性能优化的主要方面 根据 o r a c l e数据库性能优化的框架可以得出，数据库优化需要涉及操作系 统、数据库和应用程序等多个面的调整，下面主要研究对内存、cpu、i/o、sql 语句和数据库对象进行调整的常用手段。 3.3.1 内存优化与调整 提高数据库性能需要设置合适的内存尺寸，在大量并发用户数下，如果 oracle 内存尺寸不够会降低程序的处理效率，延缓数据库的响应时间。 oracle 内存包括系统全局区 (sga)和程序全局区 (pga)，系统全局区占用大 部分内存，对性能的影响较大8,9。 erp 系统中 oracle 数据库性能调整与优化 8 3.3.1.1 sga 调整 （1）数据缓冲区调整 数据缓冲区占用 sga 内存最多，分为 lru 列表和检查点队列两个组件，储 存由磁盘文件读入的数据，由服务器进程处理后返回给用户。服务器从中检索所 需数据块时，有如下几个步骤： a) 服务器进程依次扫描数据缓冲区的 lru 列表上的数据块，如果检测到 了这个数据块，则把它放入该列表的最常用端； b) 如果没有检测到这个数据块，服务器进程开始扫描磁盘，并将找到的数 据块读入 lru 列表上的一个空闲的缓冲区中。为保证有空闲的缓冲区， 服务器进程在之前扫描 lru 列表的同时把列表上脏数据块放入检查点 队列，这样 lru 列表上就多了一个空闲缓冲区，保证了数据块的顺利 读入； c) 当检查点队列的长度超过了一个预定的值， 服务器进程唤醒 dbwn进程 将该队列上的脏数据块写入磁盘；当服务器进程检索 lru 列表的次数 超过一个预定的值而仍未找到一个空闲缓冲区时， 也会唤醒 dbwn写磁 盘进程； d) 服务器进程找到空闲缓冲区后，将磁盘上读入的数据块放入 lru 列表 得中间。如果该数据块不符合读一致性，服务器进程转而去回滚段中查 找10。 为减少系统磁盘 i / o 开销，应调整数据缓冲区的尺寸，使服务器进程尽量在 缓冲区中找到所需的数据，尽量减少等待数据块或空闲缓冲区的时间。动态性能 视图 v$sysstat记录着数据块缓冲区相关等待事件，buffer busy waits 表示缓冲区 中所需的数据正被其他进程占用，free buffer inspected 表示寻找空闲缓冲区，free buffer waits表示等待 dbwn修改数据写磁盘操作的完成，以留出空闲缓冲区。 如果这些等待事件很多 ，应查找问 题来源。例如关联动态性能视图 v$system_event、 v$event_name、v$session_wait 和 dba_segment，定位各进程争 用的的文件或块号，单独对某个对象进行调整。另外动态性能视图 v$sysstat 中 的 db block gets、 physical reads 和 consistent gets 反映了多个池逻辑读和物理读的 次数，如果逻辑读的命中率低于 9 0 % ，应结合动态性能视图 v$db_cache_advice 中南民族大学硕士学位论文 9 增大数据缓冲区尺寸。此外，有些小表频繁被使用，为了使服务器进程尽可能的 在缓冲区中找到他们，通常把这些表设置为 cache table，这样他们会被置于 l r u 列表的前端，延长了在缓冲区中的驻留时间，从而优化了性能。 （2） 共享池调整11- 15 sql 语句的执行分为 3 个阶段：解析阶段通过编译语句产生对象信息、分 析树和执行计划； 执行阶段按照执行路径执行语句， 做修改操作或定位查询记录； 取数据阶段只针对查询语句，取出执行阶段找到的记录并响应给客户。这些执行 操作基本上在共享池中完成的，共享池包括库缓冲区、数据字典缓冲区和用户全 局区 3 个组件，库缓冲区存储 sql 语句、pl/sql代码、分析树和执行计划，数 据字典缓冲区存储执行 sql 语句所需的数据字典对象定义信息，用户全局区局 限于共享服务器模式，目前应用产品很少使用这种模式。共享池内存分配算法保 证了数据字典数据比库缓冲区数据在内存停留时间更长，命中率更高，应优先调 整库缓冲区。 调整库缓冲区： 据分析， sql语句的分析与编译占整个语句执行成本的60%， 如果库缓冲区中保留着相同 sql 的解析信息，oracle 不必对此重新编译而直 接执行，这种方式叫做软解析，软解析可极大减少语句的执行成本，改善系统性 能。为了充分进行软解析，在开发或调优过程中应遵守几个原则：sql 语句要 有规范（如字母大小写、符号一致），尽可能的使用绑定变量和存储过程，用户 连上后不要随意更换优化器，开发人员在业务高峰期避免使用 ddl 更新表，大 并发用户数下 sql 语句加前缀，从而减少数据字典缓冲区的条数或多用户使用 同一用户名。动态性能视图 v$librarycache 记录了库缓冲区的状态信息，reload 表示库缓冲区中解析信息被 l r u 算法淘汰（如未使用绑定变量或内存过小）执行 失败的次数， p i n s 表示执行次数， 如果 reload/pins1%， 且动态性能视图 v$sgastat 共享池的空闲内存值较小，应考虑增大共享池。此外，如果系统中有大对象 （pl/sql块），可在库缓冲区内设置保留池，将这些对象固定存储在保留池中。 动态性能视图 v$shared_pool_reserved 记录了保留池的状态信息，结合 v$sgastat 视图，若 request_failures0 且持续增加、 request_misses0，说明保留池过小； 若 request_failures0 且持续增加、free memory=shared_pool_reserved_size/2，说 明共享池过小； 若 request_failures=0、free memory=shared_pool_reserved_size/2， erp 系统中 oracle 数据库性能调整与优化 10 说明保留池过大，应调节这两个参数使共享池各部件发挥最佳作用。 调整数据字典缓冲区：重点关注命中率。动态性能视图 v$rowcache 的 gets 和 getmisses 分别表示读取数据字典信息的次数和在缓冲区没有找到的次数，查 询这两个值并计算命中率， 命中率一般大于 85%， 如果小于这个值应分析原因并 考虑增大共享池。 （3）重做日志缓冲区调整 当用 update 等命令改变后台数据时，在重做日志缓冲区中会产生相应的修 改信息，积累到一定数量或每隔 3 秒钟 lgwr 进程将将这些修改信息写入联机 重做日志文件，该文件可循环写，每几个文件组成一个组，写满一组即切换至下 一组，同时触发检查点唤醒 dbwr 进程将修改信息写入磁盘操作，采用归档日 志管理的数据库系统还会触发 arcn 进程将日志信息读入联机归档日志文件操 作。重做日志缓冲区的工作过程如图 3.1 所示。 图 3.1 重做日志工作原理 重做日志缓冲区对于数据库性能的影响没有前两个缓冲区大，但也不容忽 视。如果尺寸过小，脏数据将频繁的由内存写入联机重做日志文件，从而增加系 统的 i/o 负载，诊断时可查看相关等待事件，调整缓冲区或联机重做日志文件的 配置。 中南民族大学硕士学位论文 11 3.3.1.2 pga 调整 pga（程序全局区）存储用户会话信息、游标和排序信息。sql 的两大操 作是查询和排序，用户执行创建索引、analyze、sort 等操作时产生很多排 序信息，这些排序数据主要存储在 pga 的排序区和用户临时表空间中，如果排 序区尺寸过小，一部分排序数据被写入磁盘用户临时表空间中，在磁盘上处理这 些数据会增大i/o 开销16,17。 动态性能视图v$sysstat的 sorts(disk)和sorts(memory) 分别表示磁盘排序和内存排序的次数，如果二者比例小于 9 5 % ，应增大排序区， 在采用p g a 自动内存管理的系统中增大pga_aggregate_target参数或手工管理的系 统中增大 sort_area_size 的值。 3.3.2 磁盘优化与调整 i/o 的消耗对 oracle 数据库的性能起着决定性作用， 服务器进程都是通过 i/o 读写磁盘上的数据库文件，进而为用户进程提供服务。磁盘驱动器的布局对 i/o 的效率起重要作用，毕竟用户进程想要访问的数据库文件都存放在磁盘上， 磁盘驱动器的布局如果配置合理，i/o 进程就可以在相同时间访问不同的地址， 对数据的访问时间也会大为缩短，服务器的性能得到提高。由以上分析可知，对 i/o 的配置就是解决 i/o 进程之间的资源争用现象，所以对 i / 0配置优化的方法 是使磁盘驱动器的数据文件 i/o 访问量达到平均化，这也是解决 i / 0 争用的最好 方法。通过以上原理设计了配置数据库的方法： a) 经常使用的对象产生 i/o 争用的机会较多，应把它们对应的文件单独存 放，例如我们把一个 i/o 进程要经常访问的用户数据文件、用户索引文 件或某个表空间中的几个段存储在不同的磁盘上； b) 根据前面的介绍，在内存中修改过的数据不是直接写入数据文件，而是 先写入重做日志文件中，重做日志文件分为几个组，写满一组时切换至 下一组，最后一组写完后再返回至第一组，按顺序循环写入。重做日志 文件的写进程与重做日志缓冲区大小、检查点的频率和写归档重做日志 文件的速度有关，如果缓冲区过小会频繁的写入磁盘重做日志文件，增 大 i/o 负荷，如果下一组内存的脏数据还未更新至后台数据文件或重做 erp 系统中 oracle 数据库性能调整与优化 12 日志文件的数据还未读入归档重做日志文件，则切换组时会发生等待 i/o 事件，因此为减少 i/o 和切换组时 i/o 进程的竞争，重做日志缓冲区 的内存要足够大，重做日志文件、数据文件和归档文件应分开放在不同 磁盘上，另外还可增大重做日志文件成员的尺寸减少组切换的频率或增 大组的个数降低 i/o 等待的频率； c) oracle的文件和操作系统的其他文件要存储在不同的磁盘上， 这样可 以减小 i/o 争用的概率，不过最好是组建专用 o r a c l e 服务器，这是最优 的方法； d) 最好使用目前较流行的廉价磁盘冗余阵列（raid），它能自动分离不同 类型、访问频率的数据库文件，减小 i/o 进程之间的竞争，优化数据库 性能； e) 使用异步 i/o 方式来减小 i/o 进程之间的竞争，因为它可以在同一时间 处理不同的 i/o 访问， 大多数操作系统都有此功能， 或是采用从进程 （如 dbwn slave）实现此功能19- 22。 查找i/o 瓶颈的思路： 通过u n i x 提供的 iostat等工具来检测i / o 竞争的原因， 可检测出磁盘中每次访问最多的文件，从而找到 i / o 竞争的原因，判读是数据库 查询语句的原因还是磁盘分配文件的原因，加以分析，重新分配磁盘文件存储和 优化查询语句布。 3.3.3 cpu 优化与调整 提高数据库的性能主要依赖于调整内存的大小和 sql 语句等软件配置，但 cpu 同样会对数据库系统产生影响，扩大 cpu 的个数能处理更多的数据库服务 进程，使数据库的性能提高，当然，还可以通过调整程序来增加 cpu 的执行效 率，在分析 sql 语句占用 cpu 资源情况时，应关注那些占用大量 cpu 解析时 间、占用大量 cpu 资源和读磁盘最多得语句并优化。 3.3.4 sql 语句的优化 sql 语句是体现在数据库方面的应用程序，使用高效的 sql 语句对迅速提 升 oracle 数据库的性能起着不可估量的作用。在一些情况下，修改效率差的 中南民族大学硕士学位论文 13 sql 语句比更换先进的硬件配置可以更优地解决系统性能问题。执行 sql 语句 需要选择适合的优化器，不同的优化器使 oracle 按照不同的计算方法选择 sql 执行路径，例如基于规则的优化器是传统的执行模式，选择执行路径的依 据是各种规则；基于成本的优化器在较新的版本中逐渐被广泛使用，选择执行路 径的依据是统计的数据字典中各种对象和系统资源信息， 可见虽然这 2 种模式的 目的是减少语句的执行成本， 但却由于访问了不同的执行路径对性能的优化效果 不同。 调整可疑的 sql 语句时， 通常使用 autotrace、 explain 工具或 sql trace的 10046 事件来跟踪并查看 sql 的执行路径及各种资源的消耗情况。 开 发过程中要遵守以下几个原则23- 27： （1）对几个表查询时 from子句的顺序，按照由内及外的访问顺序应把可 筛选出较少记录的表放在前面，执行时最先查找出这个表的几个记录，再和其他 表的记录相连接。例如语句： select clm1,t2.clm2 from tbl1,tbl2 where tbl1.clm3=5 and tbl1.clm1=tbl2.clm1; （2）当所查询的表很大时，在 w h e r e子句的列上建索引，执行时首先查找 数量较少的索引块，然后根据 rowid 检索表中具有相同 rowid的记录，从而 避免了全表扫描可能带来的性能问题。例如 tbll表在 tbl1 列上有索引对应语句 select clm1 from tbll where clm2=5; （3）使用 union、order by等条件的 sql 语句会对查询完的数据进行 排序，增大 pga 或 temp 的负担，优化这些语句使可在使用这些条件的列上加 有序索引。 （4）如果经常运行条件子句变量值不同的 sql 语句，为充分利用库缓冲区 的 sql 解析信息，应将这些变量改为统一的绑定变量。例如语句 select * from tbl where clm1=:cml1; （5）在系统不繁忙或在大量对象更改后定时或及时统计数据信息，使基于 成本的优化器准确、最佳的执行 sql 语句。 3.3.5 数据对象优化 表和索引是两个重要的数据库对象，应经常监控他们的存储情况，避免对象 由于使用过多的空余空间造成数据的分散分布，增加对这些数据检索的代价。 erp 系统中 oracle 数据库性能调整与优化 14 (1) 当表中删除大量的记录或某些参数更改时，会产生一些空余空间，可将 表存入另一个表空间上， 使表在维护原来的权限和约束性条件的同时空余空间得 到了合并28,29。 (2) oltp 系统是多事务短操作的系统，应将响应时间放在第一位，在该系 统中通常配合使用 b 树、压缩和倒序索引来提高检索数据的速度，使系统的性能 得到改善30。但随着时间的推移对 b 树索引多次删除记录后，存放这些已删除 索引的空间并没有释放掉，造成索引树的层次变高，一些情况下利用这些索引检 索数据甚至比全表扫描操作消耗更多的 i/o。动态性能视图 index_stats 记录了索 引的状态信息，必要时应重组或规整删除率小于 20%的索引31,32。除重组外还可 多次运行索引相关的 sql 语句，跟踪索引的使用状况，将不经常用到的索引删 掉，降低系统的开销。 （3）如果表空间使用字典存储方式，则一个任务执行时如果表空间大小不 够，首先要自动扩展区间，同时修改数据字典中对象的存储信息，通过运行批量 的 sql 语句完成修改操作，从而增加系统负荷和延迟响应速度 33,34。为避免这 种现象，可预先把使用这种存储管理的表空间初始尺寸和扩展尺寸设置大一些。 如果表空间已定义，可在表空间自动扩展区间前事先查询数据字典 dba_tables 计 算出每个表空闲块占表空间中总块的比例，如果这个值小于 0.1%，应手工分配 该表的区。 3.4 小结 本章着重探讨集大量经验由点及面的性能调整方法，随后对 cpu、内存、 磁盘 i/o、sql 执行效率、索引等各层面的性能调整技术进行了深入阐述，在后 续的 erp 系统调优中作者将遵循和运用上述方法和技术展开实践和研究。 中南民族大学硕士学位论文 15 第 4 章 调优前实施 数据库的早期规划对后期性能有重要影响，本章遵循优化的原则，合理配置 数据库参数及各种对象。 4.1 数据库早期规划 本次调整的产品为开发完还未上线的浪潮 erp- ps10 管理系统，因此本次调 优的目的是调整有问题的应用程序，配置数据库时应合理规划磁盘布局以减少 i/o 竞争，合理分配 sga 和 pga 以避免内存过小问题，合理进行对表空间等对 象的存储管理以避免动态分配空间导致系统性能降低和碎片化消耗过多的资源， 为后续应用程序瓶颈的查找创造良好的条件。 4.1.1 文件部署 从第 3 章可以得到，应把热点文件分开放到不同的盘上避免 i/o 争用，应用 程序有大量的更新、增加和删除表的 sql 操作语句，在维护这些表的同时要维 护相应的索引，为避免 i/o 争用应将数据文件和用户文件放在不同磁盘上35。同 时该系统大批量数据dml操作生成的修改信息会写入回滚段用于事务处理恢复 和联机重做日志文件用于数据库恢复，应尽量将他们单独分在不同的盘上36。 重 做日志文件的切换频率关系到系统的恢复难度和性能，二者都要兼顾， 根据以往 经验，设置联机重做日志大小为 200m，分为 3 组，每组 2 个成员。所有用户共 用一个临时表空间，为了满足大量的排序操作，也应放单独的磁盘上。目前服务 器有一个主分区 sad1 和 7 个逻辑分区 sda5、 sda6、 sda7、 sda8、 sda9、 sda10、 sda11， 划分后的物理结构如下表 4.1 所示。 表 4.1 磁盘初始规划 磁盘 文件 sda5 用户数据文件 gs_oradb_001.dbf sda6 用户索引文件 gs_oradb_idx_001.dbf erp 系统中 oracle 数据库性能调整与优化 16 sda7 回滚段 undo_01_db01.dbf sda8 组一：联机重做日志文件 redolog_denver_01.rdo、redolog_denver_02.rdo sda9 组二：联机重做日志文件 redolog_denver_03.rdo、redolog_denver_04.rdo sda10 组三：联机重做日志文件 redolog_denver_05.rdo、redolog_denver_06.rdo sda11 用户临时表空间 4.1.2 内存参数设置 erp 系统通常在大量并发用户数下运行，为了减少对性能的影响，应保证 oracle 有足够的内存来处理大量的数据操作，但也不能过大导致操作系统占 用的内存太小而产生频繁的换页甚至交换，严重影响系统性能，因此内存分配应 把握一个度37- 40。服务器做为测试专用的 oracle 数据库服务器，基本不会运 行其他程序，根据联机事务处理系统的特点和经验值，从服务器内存 1g 中划分 800m 给 oracle 内存，pga 采用自动内存管理，调整 sga_max_size 和 pga_aggregate_target 按约 8：2 的比例分别为 600m 和 200m。其它参数暂时采用 默认值。 4.1.3 表空间存储参数 浪潮 erp- ps10 管理系统通常按不同的应用如会计期间段建立一个帐套，生 成帐套管理员用户，同时自动生成用户数据文件和索引文件，由帐套管理员用户 指定初始尺寸、扩展尺寸和存放位置，每个帐套管理员用户下可创建多个普通用 户，由帐套管理员为每个普通用户分配功能权限和数据权限，所有普通用户共用 帐套管理员用户下的对象和数据信息。 所测系统在凭证增加和查询时需对某些表进行大量的插入操作， 在字典管理 表空间模式下，如果初始空间设置过小会造成频繁的动态扩展区间，区间的分配 要通过运行很多 sql 语句完成对数据字典的更新，区间分配完后才能进行下一 步任务，从而增加系统负荷和延迟响应速度41- 43。为避免这种情况，在这里应采 用表空间本地管理方式或创建帐套时预先把表空间的初始尺寸和扩展尺寸设置 大一些。 中南民族大学硕士学位论文 17 4.2 确定优化目标 根据用户性能需求的调研， 明确浪潮 erp- ps 管理系统以往版本的用户基 本数据量、并发用户及性能状况，并以此为依据，确定本次产品发版性能测试的 数据量及并发用户规模以及需要达到的响应时间目标。 希望通过测试和调整达到 以下目标：产品在大规模数据量情况下，最终 100 并发用户混合运行所有子系统 某些功能点场景下，没有死锁堵塞问题，服务器资源占用合理，同时响应时间能 达到预期的目标值。 鉴于系统结构的复杂性， 本文以公共和总账管理子系统为例， 详细的目标如下： （1）并发过程中数据库无死锁堵塞问题。 （2）并发过程中，数据库服务器 cpu 占用低于 85%,内存、磁盘无瓶颈。 （3）单功能点并发用户数及平均响应时间指标如表 4.2 所示。 表 4.2 各功能点响应时间指标 模块 性能测试点 并发用户数(个) 平均响应时间(s) 公共 登录 20 5 科目余额表 10 15 账务 科目余额表联查三栏账 10 15 4.3 测试数据库性能 4.3.1 测试策略 采用 hp 公司的专用性能测试工具 hp loadrunner9.5 模拟现实业务的场景， 选择 oracle(2- tier)协议对 oracle9i数据库进行测试。采用并发测试，集中 加载用户，忽略思考时间（不停的向服务器发送请求），每个用户反复执行 10 次。依据指标中客户操作较集中的登录和科目余额表查询两个功能点展开测试， 并对测试过程中发现的严重性能问题（响应时间长、产生死锁、服务器占用资源 严重）给予解决。 erp 系统中 oracle 数据库性能调整与优化 18 4.3.2 测试网络拓扑 测试网络拓扑结构如图 4.1 所示。 控制台 lan 数据库服务器 虚拟用户 虚拟用户 虚拟用户 图 4.1 测试网络拓扑图 4.3.3 测试步骤 （1）使用专用性能测试工具 loadrunner针对每个功能点，根据各功能点性 能指标执行相应的业务操作，录制产生脚本，生成虚拟用户。 （2）修改及调整脚本，使之可以实现模拟多个实际用户的并发操作。 （3）通过中央控制台进行各个场景的并发测试，并监控系统运行状况。 （4）收集结果数据并进行分析产生报告。 4.3.4 测试功能点的响应时间 （1）20 并发用户运行“登录”功能点场景下响应时间如图 4.2 所示。 图 4.2 登录响应时间图 从上面的响应时间图可以看出登录子系统性能很好，平均响应时间 2.55s， 能够达到预定时间目标 5s。loadrunner 控制台的事务运行记录中无事务死锁现 象，另外操作系统 cpu 占用率很低，无 i/o 和内存瓶颈。登录子系统达到了性 能优化指标。 （2）10 并发用户运行“科目余额表查询”功能点场景下响应时间如图 4.3 所示。 中南民族大学硕士学位论文 19 图 4.3 科目余额表响应时间图 从上面的时间响应图可以看出， 科目余额表查询和联查三栏账子功能点受用 户数量影响较大，平均响应时间分别为 21.812s 和 22.536s，不能达到预定的时间 目标 15s。 4.3.5 系统存在的问题 根据前面对测试结果的分析，登录子系统达到了性能优化指标，暂不需对该 系统做优化；科目余额表查询功能点响应时间达不到性能指标，应着重对该功能 点所在的总账管理子系统进行分析和查找性能瓶颈。 4.4 小结 按照性能优化的步骤，本章进行实验的前几个步骤：搭建应用环境建立性 能目标测量并对比当前性能，确定是否需要调优。着重研究在安装 erp 系统 时按照优化规则部署数据库，包括合理部署磁盘空间、根据系统的特点按经验值 分配内存和表空间的存储参数，减少了测试和调整应用程序时额外的维护工作， 为下一章的应用程序优化和实例参数优化创造良好的条件。 测试结果表明响应时 间达不到性能指标，需要对该功能子系统进行诊断和优化。 erp 系统中 oracle 数据库性能调整与优化 20 第 5 章 erp 系统中数据库性能诊断与优化 本章主要介绍了对 erp 系统性能进行分析，定位性能瓶颈，这是优化工作最 关键的一步。在此基础上，确定了一套优化方案并实施开展工作。 5.1 性能分析 5.1.1 操作系统性能分析 linux系统性能监视工具可查看服务器不同资源的使用情况，压力上来后每 隔 3 秒运行一次 vmstat命令，各资源使用情况如图 5.1 所示，监测到有一段时间 内 cpu 的空闲率（id）接近为零，可见 cpu 利用率持续很高。另外执行队列中 等待使用 cpu 的进程数(r)很多，根据经验这个值超过系统 cpu 个数的两倍，提 示 cpu 已经超载。 图 5.1 vmstat 命令输出 运行 top 命令，进一步查看各进程的资源使用情况，如图 5.2 所示。 中南民族大学硕士学位论文 21 图 5.2 top 命令输出 由上图可以看出没有个别进程消耗大量资源的情况，但是这些 oracle 进 程几乎消耗了所有 cpu 资源，这种情况下一般由低效的 sql 语句引起，需要在 数据库层面进一步定位和分析具体原因。 5.1.2 数据库性能分析 5.1.2.1 低效的 sql 语句 在数据库层面，可从等待事件入手查找性能问题，查看 statspack 报告 top 5 timed event 段如图 5.3 所示，排在前两位的是 db file sequential read 和 db file scattered read 事件，db file scattered read 指读取多块分散的数据块产生与全表扫 描有关的等待， db file sequential read 指读取单个数据块产生与索引扫描有关的等 待，这 2 个事件比较多，说明进行了太多的全表扫描或不合理的索引扫描，成为 产生 cpu 瓶颈的重要原因。 图 5.3 statspack 报告前 5 位等待事件图 在会话级， 动态性能视图 v$session反映了系统的实时会话信息，查询这个 erp 系统中 oracle 数据库性能调整与优化 22 表找出发生 db file sequential read 和 db file scattered read 等待事件最多的会话， 多次执行下列语句： select sid, event, state, wait_time