基于PLSQL的批量处理应用的性能优化策略①.PDFVIP

2016 年 第 25 卷 第 5 期 计 算 机 系 统 应 用 ① 基于 PL/SQL 的批量处理应用的性能优化策略 张孝斌, 王 超 ( 中国民航信息网络股份有限公司, 北京 100102) 摘 要: Oracle 数据库系统是目前企业应用最广泛的大型关系数据库管理系统. PL/SQL 是 Oracle 对结构化查询语 言 SQL 扩展的过程性语言, 利用其设计和开发触发器、视图、存储过称、包以及函数等实现对数据库的数据处 理. 在很多企业应用架构设计中, 会采取 Oracle 端存储过程实现一些用户交互少、逻辑复杂和涉及数据量大的批 量处理, 使得数据库具备了数据存储和业务处理的双重角色. 但是在这种应用中, 随着数据量的不断增加, 如何 保证存储过程的性能非常关键. 本文结合中国民航国际客运收入管理系统海外版性能优化的成功实践, 简要介 绍了 Oracle 的OEM 工具和 AWR 报告两个发现性能问题的工具, 并从代码结构调整降 I/O、临时表和模拟多线程 三方面总结了基于 PL/SQL 的批量处理应用的性能优化策略, 为采用此类架构设计的开发人员和运维人员提供持 续优化的思路. 关键词: PL/SQL; 存储过程; 批量处理; 性能优化 Performance Optimization Strategy of Batch Process Application Based on PL/SQL ZHANG Xiao-Bin, WANG Chao (Travelsky Technology Limited, Beijing 100102, China) Abstract: As large-scale RDBMS, Oracle is used most widely in enterprise application. PL/SQL is programming language based on SQL and expanded by Oracle. We could adopt PL/SQL to develop trigger, view, stored procedure, package and function to process data. In many architecture designs of enterprise application, stored procedure is utilized to achieve batch processing which does not need more user interaction, owns complex logic and often involves a large amount of data, making the database have dual roles which are data storage and business process. With the increasing amount of data, how to guarantee performance of stored procedure is very important. In this article, based on successful practice of performance tuning to oversea version of China Aviation International Passenger Revenue Accounting Solution, the author introduces Oracle’s OEM and AWR report simply which are used to discover performance problem, and three tec