随着Oracle数据库在银行系统中的普遍使用，在对银行系统的非功能测试过程中往往离不开对Oracle数据库相关的测试。我行新一代系统中绝大多数都采用Oracle数据库，通过统计性能测试中发现的缺陷和分析生产事件可知，数据库SQL语句执行效率低下往往是导致上述问题的原因，因此，高质量的SQL语句是有效提高我行新一代系统性能的重要途径。目前，市场上现存的Oracle SQL调优工具往往只针对耗时最多的特定SQL语句进行优化，且没能充分考虑我行新一代系统特有的SQL优化实际问题，因此一个能够充分结合我行非功能测试实践的全量SQL语句分析优化工具才能彻底解决我们非功能测试中遇到的SQL优化难题。
本文中主要针对上述我行非功能测试中遇到的特有的Oracle SQL优化问题, 首先分析了市场上已存在的Oracle SQL调优工具的优缺点并吸取其经验，其次根据SQL语句的基本调优原则，研究并实现了一个可视化的SQL调优工具实现了对全量数据库SQL操作过程的详细分析，同时结合PL/SQL的SQL执行计划，详细地以报告的方式展现了目标SQL语句的执行过程，最后结合知识库中的优化规则给出针对性的SQL语句优化建议，进而完成Oracle SQL语句的优化并生成优化报告。
关键词：性能测试，Oracle SQL调优
目 录
1 绪论 1
1.1 研究背景 1
1.2 研究意义 1
1.3 研究内容 2
1.4 论文结构 3
2 导师制工作总结 4
2.1 厦开轮岗总结 4
2.1.1 厦开工作实践 4
2.1.2 厦开工作总结 6
2.2 测试环境处轮岗总结 6
2.2.1 测试环境处工作实践 7
2.2.2 测试环境工作总结 8
2.3 非功能测试总结 8
2.3.1 非功能测试实践 8
2.3.2 非功能测试工作总结 9
3 面向性能测试的Oracle SQL优化研究 11
3.1 Oracle SQL面临的问题 11
3.2 Oracle SQL优化原理 12
3.3 常用的Oracle SQL优化方法 13
4 Oracle SQL优化工具的实践 16
4.1 Oracle SQL优化工具概述 16
4.1.1 Oracle SQL优化平台介绍 16
4.1.2 Oracle SQL优化平台的设计流程 16
4.2 Oracle SQL优化工具各模块介绍 17
4.2.1 数据库设置模块 17
4.2.2 查看执行计划模块 18
4.2.3 SQL优化模块 21
5 总结与展望 22
5.1 工作总结 22
5.2 未来展望 23
致谢 24
1绪论
1.1研究背景
当前，数据库系统存储的数据量越来越大，可存储的数据结构越来越复杂，用户与应用系统进行数据的交互，主要是与数据库进行交互操作。数据库系统是应用系统不可缺少的一部分，数据库性能出现问题的时候，其整个应用系统的性能也会受到极大的影响。通常我们会优先考虑通过升级数据库软硬件平台的方式来提升系统性能，但这无形中会增加整个系统的开销。随着技术的不断发展，SQL 语句优化受到越来越多的人们的关注，在提升数据性能方面也起到越来越重要的作用。
在我行新一代系统的上线前期，非功能测试承担着除业务功能之外的其他技术性测试，以达到提高软件质量，加强开发过程质量控制，严把测试关，从源头控制生产运营风险的目的。在应用系统开发初期，由于开发人员使用的数据库中数据比较少，对于查询SQL语句，复杂视图的的编写等体现不出SQL语句各种写法的性能优劣，但随着数据库中数据的大量增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一，所以Oracle SQL语句的优化对于系统性能的提高常常具有至关重要的作用。
目前，市场上已经存在了很多Oracle相关的SQL自动调优工具，但是由于这些工具是面向通用的SQL语句调优问题，而且常常是针对少数特定的SQL语句进行优化，没能充分考虑我行新一代系统特有的SQL语句实际优化难题，因此在我行非功能测试中使用这些现有的Oracle调优工具虽然能起到一定的帮助，但是很多时候还是不能有效地解决我们非功能测试中遇到的SQL优化难题。
1.2研究意义
在企业应用开发中，许多初级程序员在编写程序时往往只注重程序逻辑的正确性，却忽略了SQL性能的优化，特别是在系统开发初始阶段，由于数据量小，更加体现不出SQL性能的优劣。然而一旦进入性能测试阶段，各种问题都会凸显出来，在性能调优过程中，SQL优化是重中之重。特别是在金融行业应用中，业务数据量呈指数级增长，因此SQL语句的调优尤为重要。通过对易出现问题之处进行分析，从而提高SQL语句的执行效率，提高企业开发效率。
随着数据库技术应用越来越广泛，用户日益增长，使数据量越来越大，且伴随数据挖掘、数据分析不断深人，数据处理也越来越复杂，庞大数据量加上复杂数据处理过程，使数据库服务器经常超负荷运作，甚至出现死锁现象，严重影响使用效果，若不注重SQL 查询优化策略，往往在系统投人初期使用时查询反应迅速，使用流畅，但随着数据库表中记录数据量越来越大，系统响应速度越来越慢，甚至无法正常响应，就可能导致系统可用性降低，失去实用价值。因此，科学地构造数据库结构，合理使用查询语句及查询方法，是成功开发和应用数据库系统重要环节，对于SQL 语句优化，因为会有成千上万条SQL 语句访问Oracle 数据库，所以这是最消耗时间的调整操作。所谓的SQL 语句优化就是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句(SELECT，INSERT，UPDATE 或DELETE)中的一个重要步骤。总体上讲，就是需要识别出最常用的SQL 语句，通过仔细审查SQL 的执行计划以及使用Oracle 提示(HINTS)调整执行计划来调整各个语句。
1.3研究内容
所谓SQL优化是指将执行效率较低的SQL语句重写为结果等效但执行效率较高的SQL语句，使优化器选择最有效的执行计划消耗最少的系统资源（CPU代价、I/O交换量、扫描字节数等）和响应时间。
SQL语句优化规则的制定和作用取决于执行的数据库环境和数据库本身的结构，优化规则涵盖了执行过程中的表连接方式、语句执行顺序、表扫描方式和循环次数等，应用每一条优化规则都有可能改变该语句在数据库中的执行方式，也就会产生优劣性不同的执行计划。Oracle为每一条执行过的SQL语句留下统计信息，获取这些信息进行分析总结出优化规则并找到效率低下的语句进行优化和验证是本课题研究中对SQL语句进行优化的主要方法。
本文研究实现的Oracle SQL优化主要包括以下几点：
1. 本文的Oracle SQL调优不仅仅是针对耗时最多的特定SQL语句进行分析，而是对目标时间段内或者目标SQL语句进行所有SQL操作的全量分析和调优操作，分析了Oracle数据库中SQL语句的执行过程和查询机制，研究了SQL语句在Oracle数据库中的处理方式、执行路径和影响执行计划的因素，进而来诊断和检查SQL执行语句的性能问题；
2. 本文设计实现的工具以一种可视化的方式向用户展示出SQL语句的分析结果，解决了DBA日常繁琐的手工查找解决Oracle SQL调优问题，使得SQL的优化过程更加直观和简单易懂，便于测试工作人员随时进行查看；
3. 本文立足于非功能测试过程中遇到的实际问题，建立了一个由优化方法和优化原则构成的调优知识库，在后续的非功能测试中可以根据具体实践来不断完善该知识库，逐步形成一个适用于我行新一代系统性能调优的全面的Oracle SQL调优知识库，进而找出执行效率较差的SQL语句并给以相应的优化建议
1.4论文结构
本文一共分为五个章节，各章节主要内容如下：
第一章，绪论。主要介绍论文的研究背景、研究意义、研究内容以及论文的组织结构。
第二章，导师制工作总结。主要介绍了学员本人在导师制实施一年来的学习和工作内容，分别从厦门开发中心的轮岗，测试环境处的轮岗和非功能测试处的工作三个阶段进行总结和回顾。
第三章，面向性能测试的Oracle SQL优化研究。在本章中主要分析了当前我行新一代系统性能测试中面临的Oracle SQL优化相关的问题，并总结了当前流行的Oracle SQL优化的常用方法。
第四章，Oracle SQL优化工具的实践。本章内容主要对实践的一个可视化的Oracle SQL优化工具的主要功能和模块进行介绍。
第五章，总结和展望。本章总结了学员本人在入职一年来的工作和Oracle SQL调优的研究内容，并未来本人在的非功能集成测试处的工作提出展望，包括对自己目前存在的一些不足和今后的改进措施进行阐述。
2导师制工作总结
2.1厦开轮岗总结
在2014年第四季度，我到厦门开发中心进行了为期3个月的新员工轮岗工作，在数据仓库项目组进行了学习和工作，为之后的数据线相关项目的测试打下了基础。
2.1.1厦开工作实践
在厦门开发中心轮岗期间，我的工作实践主要包括三个部分：P9平台基础架构的学习，GP数据相关知识的学习以及ETL作业的开发。
（1）P9平台架构
首先，经过厦开导师的介绍，我学习了P9平台的基本架构，其基础架构图如图1所示，对P9平台有了整体的宏观认识，并学习了数据线的工作目标是收集分析内部经营管理和外部监管统计信息需求，建立企业级数据仓库和企业级信息应用平台，支持内部经营管理和外部信息披露。
其次，通过阅读P9平台相关的一系列技术文档我对平台的的各个数据区（数据缓存区，历史数据区，贴源数据区，基础主题区，公共计算区，应用计算区，实验数据区等）进行了详细的学习，包括各个区的功能，数据流向，数据模型，数据周期和加工处理的具体内容等。
另外，工作之余我参加了厦门开发中心一些针对P9平台的培训，进一步加深对P9各数据区和各组件实现机制的理解，包括数据加载组件，数据卸载组件，数据复制组件，数据标准化组件和 数据质量检核组件等，以及各组件之间的相互关系。
（2）Greenplum数据库
在我行数据仓库中，Greenplum数据库被广泛使用，因此在厦门开发中心轮岗期间，学习GP数据库的实现原理、GP数据库常用命令和数据日常运维方法是轮岗工作中很重要的一个部分。
首先，Greenplum数据库最重要的特性是多版本并发控制（Multiversion Concurrency Control, MVCC），其中读数据的锁请求与写数据的锁请求不冲突。Greenplum数据库基于PostgreSQL，其特点包括：每个节点都可以做自己的备份和恢复，相同的客户端功能，增加支持并行处理的技术，增加支持数据仓库和BI的特性（外部表(external tables)/并行加载(parallel loading)，查询优化器增强）。
其次，我总结了GP数据的常用操作命令，包括数据库连接，创建数据库模式，建立数据表，修改分布键等，并完成工作总结一篇。对于GP数据日常运维中的常用命令，我学习了包括检查GP数据总体运行状态，集群中是否有实例宕掉，数据库日志内容，磁盘使用率等内容，初步掌握了基本的使用和维护GP数据的方法。
图1：P9平台基础架构图
（3）ETL作业开发和配置
在学习了理论知识之后，我进入到数据仓库项目具体的开发小组，从学习P9平台开发语言Perl开始，到阅读组件代码、学习组件的架构并体会设计的思想，最后可以进行具体的编码工作。在实践中进一步学习了数据仓库项目中的P9开发技术，了解了一个ETL作业是如何从代码编写开始到配置到作业自动调度平台进行运行的整个过程。
首先，ETL是数据抽取（Extract）、清洗（Cleaning）、转换（Transform）、装载（Load）的过程。是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。P9的作业的实现过程通常分为三个部分：第一，数据的加载加工；第二，加工之后要卸载数据，会生成三个文件DAT（数据），XML（格式配置文件），DDL（定义表的字段），一般是三种文件一起卸载下来；第三，将卸载下来的数据传输给其他系统
在编码实现P9作业之前，作业的接口会在详细设计方案中给出，同时设计方案中还会给出开发流程的伪代码。因此，我在P9作业开发项目组的的要目标就是把伪代码变成可执行的Perl脚本代码，然后将编码实现的Perl脚本作业配置到P9的作业自动调度平台中，实现作业的自动调度，其中代码实现过程中会使用到大量的数据库操作。
2.1.2厦开工作总结
在厦门开发中心的轮岗工作期间，我收获了很多，其中包括工作和生活两个方面。
在工作方面，我对P9平台的总体架构有了宏观的认识，还学习了GreenPlum数据库，P9各组件和数据区，以及P9数据监控平台等相关内容，从理论上对数有了据仓库的开发工作内容有了整体全面的认识，并通过ETL作业开发在实践中进一步学习了数据仓库项目中的P9开发技术。理论与实践相结合让我们对P9平台以及数据仓库项目有了深刻的理解，掌握了相关的开发技能并顺利完成相关开发工作。
在生活方面，在厦开轮岗期间结识了很多新的同事，在工作中对我们遇到的问题进行耐心地解答，在生活中为我们详细介绍周边的生活环境。另外,我们还参加了厦开的团队活动，不仅感受到了团队的和谐氛围，而且增进了同事之间的感情，方便了以后和厦开同事之间的沟通和协同工作。
2.2测试环境处轮岗总结
测试环境处是我们非功能测试工作开展过程中息息相关的单位，在测试环境处的轮岗期间，我着重学习了非功能测试中常用的操作系统命令，以及测试环境部署等内容，为以后的非功能测试工作做好准备。
2.2.1测试环境处工作实践
测试环境处的轮岗工作主要包括两个部分，分别是了解我行所有测试相关的环境总体分布图，以及学习版本部署方法和常用的操作系统命令。
（1）测试环境区域图
来到测试环境处的第一项工作就是在导师的指导下学些测试环境中的总体环境分布图，如图2所示，其中，开发，功能，非功能三个环境之间的网络是互不联通的，对于非功能测试环境，版本检验测试环境使用组件组装环境或者应用组装总装的环境之一。一般测试环境搭建好之后，会把环境交付报告发给测试经理，测试经理需要留下备份，并且按照申请时的用途来进行使用环境会保留。
图2：总行集中开发测试环境区域图
（2）版本部署
版本部署是测试环境处日常工作中一个和重要的部分，且版本部署工作通常是在非上班实践完成，比如午休实践或者下午下班时间，避免在部署过程中影响其他环境的正常工作，因此，在测试环境处轮岗期间我还学习了项目的版本部署流程和方法，包括版本部署前的准备工作，以及在部署过程中遇到问题时如何解决等。
（4）操作系统常用命令
在非功能测试中，经常需要测试人员查看测试环境是否正常，或者遇到问题时需要通过查看服务器等的状态来排查原因或者找到解决方法，因此熟练掌握各种操作系统命令是提高测试效率的一个有效的途径。基于上述需求，在测试环境处轮岗期间，我向环境处的导师学习了数据库常用操作命令以及Linux下常用命令的使用方法。其中数据库命令中学习了数据导入导出，查看库表的大小，修改用户的密码，建立表空间和用户等操作，Linux命令方法学习了vi编辑的方法，清理大文件，输入输出重定向，查看进程CPU状态等操作方法，为之后的非功能测试实践中提供了很大的帮助。
2.2.2测试环境工作总结
在测试环境处的轮岗工作中，包括理论上的学习和工作中的时间两个部分。
在理论方法，学习了我行所有测试环境的分类和功能，同时有针对性地学习了测试环境处和我非功能测试相关的版本部署，数据操作，操作系统命令等，为今后的非功能测试实施工作打下坚实的基础。
在工作实践中，熟悉了测试环境处同事的日常工作流程，和同事一起合作完成了部分版本的部署工作，以及完成了给测试部门同事分配环境，批量建立表空间和用户，批量挂载NAS和安装补丁，利用脚本安装TD数据库等工作。
2.3非功能测试总结
在非功能测试工作中，我学习了非功能测试的概念和测试流程，并参与了三项任务的非功能测试工作，下面我我将对自己在非功能测试实践中的工作进行回顾和总结。
2.3.1非功能测试实践
在非功能测试的实践工作中，我主要从两个方面进行总结：理论的学习和测试任务的实践。
（1）非功能测试理论的学习
在我行，非功能测试定义为除业务功能之外的其他技术性测试，即以黑盒测试为主的第三方测试。测试的流程首先是和项目组沟通确定测试的需求和测试范围，进而完成测试方案的编写，在测试方案准备好之后开始搭建测试环境准备测试脚本和数据预埋，等一切准备就绪开始执行测试案例，在测试过程中通过和相关领域专家讨论来进行数据分析和缺陷定位，最终测试完成后撰写测试报告。
在非功能测试工作中，我首先学习了非功能测试整体工序中包括的4个阶段的主要内容：组件组装测试，应用组装测试，应用总装测试以及版本检验测试。
组件组装非功能测试：本阶段测试主要验证组件内部集成后，典型服务及组件整体是否满足非功能特性，主要关注整个组件的性能，以组件为单元进行测试，旨在发现组件完成组装后单个服务及整体缺陷。
应用组装非功能测试：本阶段测试主要验证单一应用集成后，典型交易的端到端响应时间、应用整体的非功能特性及技术目标相关其他指标是否符合设计要求，旨在发现单一应用全流程交易服务的缺陷。
应用总装非功能测试：本阶段测试主要验证多个应用集成后，多应用协同运行的整体非功能指标是否符合设计要求，目的在于在模拟生产部署和运行场景的环境中，发现多应用协同运行中可能存在的缺陷。
版本检验非功能测试：本阶段测试主要对封版后的待投产版本在检验环境下，对前期测试各阶段的高优先级测试案例以及发现过缺陷的测试案例进行回归测试，验证版本安装工艺是否符合要求，验证应用版本的非功能特性是否发生变化，旨在避免因投产版本发生变化导致新缺陷的引入或原有缺陷的重现。
（2）测试任务的实施
在测试任务实施方面，我分别参与了三个项目，包括个人资产项目的应用组装测试，对公客户关系管理项目的组件组装测试以及对公客户关系管理项目的应用组装测试，在三个任务实施过程中分别学到了很多测试方法和技巧。
首先，在个人资产项目的应用组装测试任务中，由于是初次接触测试工作，从常用的测试工具LoadRunner开始学习，从小飞机的例子到单交易基准单交易负载测试的场景设置，从启动nmon工具对测试过程进行监控到测试结果的统计，学习了基本的测试案例的执行和nmon监控工具的使用，以及测试结果的整理，初步了解了测试执行的方法和流程，以及测试日志的填写和统计。
其次，在对公客户关系管理项目的组件组装测试任务中，完成了测试场景，测试案例和测试脚本的规范性自查，学习了混合场景中各交易配比的计算，按照测试案例中的执行步骤执行容量测试，稳定性测试以及高可用测试等，以及测试结果的采集和分析，负责在QC上进行缺陷的提出和关闭，并参与了测试报告的评审，学习了各个测试案例的执行规范和报告编写规范，进一步学习了测试执行过程中需要注意的细节问题。
最后，在对公客户关系管理项目的应用组装测试任务中，学习了测试方案和案例的编写，负责完成了组件数据的准备，测试脚本的录制和脚本的参数化，以及后续测试案例的独立执行，并学习了一些测试中遇到问题时的解决方法，最终完成了测试结果的分析和测试报告的编写，在测试报告的评审过程中发现了自己存在的一些不足，得到了评审专家的宝贵建议，并针对建议修改了报告，相信在今后的测试工作中能够吸取经验，更上一层楼。
2.3.2非功能测试工作总结
在非功能集成测试处的测试实践中，学员了解了一个测试任务在非功能测试中的整个流程内容，从测试脚本准备到数据预埋，从测试场景的配比到测试案例的执行，从测试结果的整理到测试报告的编写，一步步深入地学习和认识非功能测试的方法。经过三个非功能测试任务的实践，我对之前工作中的一些心得体会以及遇到问题的解决方案进行了小结。
首先，测试方案的确定是测试任务的首要工作，需要结合项目组提供的信息和非功能测试的要求为被测系统提供一个有效的测试方法，这是测试过程至关重要也是测试工作最难的部分。
其次，脚本的参数化以及测试数据的预埋工作中，需要我们对交易基本流程和SQL执行步骤能够有大致的了解，才能高效地和开发人员沟通并完成测试准备工作。再次，在脚本的执行过程中，要严格按照案例的执行步骤，对目标监控点进行监控并对测试结果记录，为之后的测试报告编写提供材料。
另外，如果测试中遇到问题，要多向有经验的测试前辈请教，并多向开发人员进行细节的确认，良好的沟通能力往往能将工作事半功倍。最后，充分利用我们非功能团队开发的自动化测试工具，例如nmon监控工具，容量测试自动配比工具等，可以极大地提高测试执行效率。
最后，测试是一门精细的工作，在我行非功能测试过程中，规范性的测试过程是测试顺利完成的前提条件，杂乱无章的测试流程往往造成测试效率低下，耗费大量人力物力，因此，在测试过程中，注重测试细节，遵守测试相关的规范，往往会让测试工作如鱼得水，一路畅通。
总之，测试工作是一门大学问，不仅需要扎实的专业知识还需要高效的沟通能力，不仅需要全局把握测试架构方案还需要对测试过程中的执行细节深入了解，因此测试工作是多方面能力的综合。经过在非功能测试实践中的磨炼，相信自己在今后的测试工作中一定能够飞速前进，争取早日成为一名合格的非功能测试工作者。
3面向性能测试的Oracle SQL优化研究
3.1Oracle SQL面临的问题
传统的SQL语句优化方式是依靠经验手工重写SQL，Oracle DBA或程序员通过执行计划分析，依据经验重写SQL，然后将性能和结果进行对比。第一代优化是获取语句的执行计划然后提取其中关键字，第二代优化只能提出创建索引的建议。
国内对此项研究相对来说起步略晚，尤其是在日益庞大的数据库规模面前，较为完善的性能研究也偏向于理论化，使实际化研究略显匮乏。SQL语句优化规则在理论范畴上的研究使得某些规则并不被认可和应用，且不应该忽略Oracle本身的优化工作和建议，应该尽可能的获取Oracle本身的信息来找到低效的语句。而Oracle本身的信息多种多样，与优化相关的信息主要有语句生成的执行计划信息和语句统计信息，分析和研究这些信息总结出优化规则并以这些信息判断规则的有效性是本文研究SQL语句优化的主要实现原理和方法。目前，在我行新一代系统中经常遇到的与Oracle SQL优化相关的问题主要集中在以下几个方面：
(1) 在生产上，出现的生产事故中绝大多数都和数据库有关，而和数据库相关的生产问题中百分之八十以上都是SQL语句不够优化造成的，因此SQL问题已经成为引起生产事故的致命原因；
(2) 在非功能测试中，发现的系统有效缺陷中百分之八九十是通过优化SQL来解决的。同时，在解决Oracle SQL语句不够优化的问题时，常常需要DBA手工去查找分析原因，查找过程十分繁琐，而且SQL执行的分析结果不直观，只有对Oracle数据相当熟悉的人才能看懂具体内容；
(3) 我行新一代系统在开发过程中使用统一的Java框架，使得开发的程序质量不会有太大差异，故提高数据库的SQL执行效率是提高新一代系统性能并且保证系统安全稳定运行的关键途径；
(4) 目前，市场上已经存在的很多Oracle SQL调优工具大多是面向通用的SQL语句，而且常常只针对少数特定的SQL语句进行优化，没能充分考虑我行新一代系统特有的SQL优化实际问题，因此在我行非功能测试中这些工具虽然能起到一定的作用，但是很多时候还是不能有效地解决我们非功能测试中遇到的SQL优化难题。
