ERP系统中ORACLE数据库性能调整与优化.pdf

中南民族大学
硕士学位论文
ERP系统中ORACLE数据库性能调整与优化
姓名:郑鑫梅
申请学位级别:硕士
专业:计算机应用技术
指导教师:宋中山
20100501
中南民族大学硕士学位论文

摘要

随着 ORACLE 数据库系统被广泛应用到各行各业的应用领域,
对数据库系统性能优化的研究越来越重要。分析了国内外对数据库性
能优化的策略,结合已有的调优方法对发版前的 ERP 产品进行性能
测试,针对产品存在的性能问题进行诊断、分析和优化,降低了 ERP
产品的开发成本。
按照数据库性能优化原则配置数据库,包括:合理配置数据库参
数,合理规划磁盘分布和表空间的初始存储设置。使用 HP
测量 ERP 系统的性能,对比优化目标分析系统存在的问题。研究了
对性能瓶颈的诊断方法,结合 vmstat、top 命令分析操作系统性能数
据,初步判断性能瓶颈;结合 statspack 报告、动态性能视图进一步诊
断数据库性能,定位性能瓶颈。
提出了一套针对 ERP 系统的优化方法。主要优化数据库(内存
参数)和应用程序(SQL 语句、索引)。
实验证明,经过调整优化,响应时间、服务器资源占用率等指标
达到了预期目标,符合发布的要求。


关键词ORACLE 数据库,优化,内存,SQL 语句,索引
I
ERP 系统中 ORACLE 数据库性能调整与优化

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, mand 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