1、 南京邮电大学通达学院 毕 业 论 文 题 目： Oracle 数据库 SQL 查询语句优化方法研究 摘 要 数据库自从成为最常用的数据管理方式以后，逐步向数据结构日渐复杂、数据量日渐庞大、应用场合日渐广泛的方向迅速发展和完善。数据库与应用软件的紧密结合使用户对数据库性能的需求逐渐提高，尤其是在海量存储的数据库系统中变得愈加明显。除了软件和硬件条件本身的限制外，数据库中查询语句的执行效率也是影响其性能的关键因素， SQL 语句的执行消耗大部分的数据库资源，包括CPU 消耗、内存消耗、物理块读取消 耗等，因此有必要对 SQL 语句进行优化调整，对提升数据库性能有重要的意义。 本文分析了 Orac
2、le 数据库中 SQL 语句的执行过程和查询机制，研究了 SQL 语句在 Oracle 数据库中的处理方式、执行路径和影响执行计划的因素，并作为 SQL语句优化规则制定和生效的理论来源。同时结合对语句进行优化的目标得出具体的优化规则，优化规则的内容包括已经存在的优化规则、研究中最新提出的优化规则和通常认识中错误的优化规则，对优化的语句样本进行分析统计并总结出规则生效或失效的原因。 关键词 ：数据库 开销 ； 执行计划 ； SQL 优化 ； 优化规则 ABSTRCT Since database become the most useful management way of data, it
3、has developed and perfected rapidly to the direction that the data structure is more and more complex, the amount of data becomes larger and larger and the application becomes wider and wider. The tight relationship and fusion with the application system makes the users improve the requirement of it
4、 performance, especially in the database with a mass mount of data. Besides the limit of software and hardware, the efficiency of inquiring statement becomes the key factor in the performance. The running SQL statement consumes most of the resource of database, so it is necessary to adjust it and it
5、 makes sense to improve the performance of the database. This paper researches and analyses the execution procedure and mechanism of Oracle database, and learns the way of handling the SQL statement and the factor of affecting the execution plan. Then the paper makes it be the theory source of the o
6、ptimization rule. The paper puts forward optimization rules of SQL statement based on the optimization target in Oracle database. The rules contain the ones that has been already exist, the new ones raised by this paper and the wrong ones that known by people, at the same time it analyzes the reason
7、 of the rule. Key Words: Database performance; Execution plan; SQL optimization; Optimization rule; Optimization advice目 录 第一章 绪论 .1 1.1 研究背景 .1 1.2 国内外研究现状 .1 1.3 研究的内容及意义 .3 1.4 论文的组织结构 .3 第二章 相关知识技术研究 .5 2.1 结构化查询语言 .5 2.2 Oracle 命令执行方式 .5 2.3 执行计划的获取 .6 2.4 ADDM 性能诊断工具 .8 第三章 SQL 语句优化的原理 . 10 3.
8、1 Oracle 数据库执行过程 . 10 3.2 Oracle 优化 器 . 11 3.3 Oracle 数据库的查询机制 . 11 3.4 优化目标 . 12 第四章 SQL 优化规则内容和优化方法 . 14 4.1 影响执行计划的因素 . 14 4.2 SQL 语句类型及优化性分析 . 16 4.3 优化规则的内容 . 17 4.3.1 实用优化规则 . 17 4.3.2 错误的优化规则 . 18 4.4 获取 SQL 语句优化建议 . 19 第五章 SQL 语句优化实例 . 21 5.1 实验设备，软件环境介绍 . 21 5.2 优化实例 . 21 第六章 总结及展望 . 35 6.1
9、 总结 . 35 6.2 展望 . 35 致 谢 .1 1 第一章 绪论 1.1 研究背景 当数据库第一次发展成为各种类型数据常用的管理方式之后，它便以数据结构化、可管理性、共享性和数据可扩展性的特点日益发展和完善。目前的数据库系统可存储的数据结构日渐复杂、数据量日渐庞大，使用户与应用系统和软件的交互逐渐发展为与数据库的交互。数据库与应用系统和软件的交互越密切，数据库性能问题越显著。在日常维护工作中，经常会出现系统运行慢的问题，当这个问题与数据相关联时通常归结为数据库的性能问题。 数据库系统本身是一个较为复杂 的系统 ，影响其性能的因素也是多方面的。部分软件运营商通过提升数据库的 硬件平台（
10、CPU 和内存等）来达到提升数据库性能的目的，这种方式确实在一定程度上发挥了作用，但在实际应用中仅凭借这个措施无疑会增加系统开销，且性能的提升程度是有限的。在不断探索中， SQL 语句的执行效率逐渐 得到重视，实验证明，语句的执行效率在提升系统性能中占有重要地位 。性能优劣不同的语句在得到相同结果的情况下其执行效率有很多区别，这体现在系统资源消耗和响应时间上，在 Oracle 数据库中，这两个参数可以从执行计划中获得。 所谓 SQL 优化是指将执行 效率较低的 SQL 语句重写为结果等效但执行效率较高的 SQL 语句，使优化器选择最有效的执行计划消耗最少的系统资源（ CPU 代价、I/O 交换
11、量、扫描字节数等）和响应时间。系统资源消耗量和时间值与数据库环境、结构和数据量均有关，因此它们的含义是通过比较确定执行效率的优劣。 SQL 语句优化规则的制定和作用取决于执行的数据库环境和数据库本身的结构，优化规则涵盖了执行过程中的表连接方式、语句执行顺序、表扫描方式和循环次数等，应用每一条优化规则都有可能改变该语句在数据库中的执行方式，也就会产生优劣性不同的执行计划。 Oracle 为每一条执行过的 SQL 语句留下统计信息，获取这些信息进行分析总结出优化规则并找到效率低下的语句进行优化和优化验证是本课题研究中对 SQL 语句进行优化的主要方法。 本课题正是从这个角度出发，分析 Oracle
12、 数据库中语句的执行原理和过程，并以此为理论依据总结出优化规则，同时对 SQL 语句优化规则进行验证。同时以语句解析执行后的统计信息作为数据来源分析系统中存储的 SQL语句的执行性能。 1.2 国内外 研究现状 “数据库性能优化”的概念第一次被提出是在世纪年代，由美国学者 Tony Daugherty 提出的，从此数据库 的性能问题便被人们广泛关注和研究，掀开了数据库性能优化系列新的篇章。 在这之后的几十年里不乏有人尝试对这方面进行研究，在一篇名为2 Performance Tuning Basics的文章发表之后人们开始关注于“性能调整”。文中作者将性能调整归结为修改数据库的系统参数、提升数
13、据库系统的硬件配 置条件和优化系统组件。性能优化被提出后最常被使用的手段是不假思索 的升级硬件，虽然增加硬件可以在某些场合中改善性能问题，但是当引起性能问题的原因不是硬件资源时这种较为盲目的方法并不能有效的解决问题。 当人们发现仅凭靠硬件升 级 不能改善性能问题时，便开始注重于分析数据库的参数。最先的方法叫做 RBA（ Radio Based Analysis， 基于比率的分析），这种方法通过相 关参数之间的比率判断性能的优劣，例如通过系统逻辑读和物理读的比 率 判断是否需要改善。这要求优化者对性能参数比率的概念有深刻的理解，分析的结果也在比率的标准值定义上存在争议，因此并没有被使用很长时间。
14、 ORACLE 7 之后的版本引用了大量系统参数，在之前的基础上 Oracle DBA（ Database Administrator, 数据库管理员）开始分析系统等待资源的 时间。例如一个大进程中有诸多的子进程，通过获取系统视图中的资源等待时间判断影响性能的子进程并进行处理。这种方式忽略了执行时间，因此 21 世纪之后又出现了RTA (Response Time Analysis， 基于响应时间的分析）方式，这种方式结合了等待时间和执行时间，分析系统视图表时获取 service time， 减少后台 CPU 的占用时间或扩展 CPU， 同时获取 wait time 判断系统压力，根据内存占用
15、频率调整参数来减少等待时间。 据不完全统计 ,当升级硬件和修改网络、操作系统、数据库参数这些方法进行优化后加起来 只能提升系统性能的 40%左右，其余的 60%要靠对应用程序的优化，包括源代码优化和 SQL 优化。源代码优化代价较大，涉及到系统设计和开发，且与数据库性能优化关系不大，主要表现在 SQL 优化。 传统的语句优化方式是依靠经验手工重写 SQL。 Oracle DBA 或程序员通过执行计划分析，依据经验重写 SQL，然后将性能和结果进行对比。第一代优化是获取语句的执行计划然后提取其中关键字，第二代优化只能提出创建索引的建议，而现在有一种较为智能的优化方式，能生成多种有效的 SQL 形
16、式， LECCO Technology Limited 公司拥有的产品 LECCO SQL Expert 是一个典型的实例，但这种优化方式也无法明确语句优化的原理和语句优化的程度。 国内对此项研究相对来说起步略晚，尤其是在日益庞大的数据库规模面前，较为完善的性能研究也偏向于理论化，使实际化研究略显匮乏。 SQL 语句优化规则在理论范畴上的研究使得某些规则并不被认可和应用，且不应该忽略 Oracle 本身的优化工作和建议，应该尽可能的获取 Oracle 本身的信息来找到低效的语句。而Oracle 本身的信息多种多样，与优化相关的信息主要有语句生成的执行计划信息和语句统计信息，分析 和研究这些信息
17、总结出优化规则并以这些信息判断规则的有效性是本文研究 SQL 语句优化的主要实现原理和方法。 3 1.3 研究的内容 及意义 SQL 语句优化规则制定的依据就是 Oracle 数据库的执行机理和 SQL 语句的执行计划，同时涵盖 Oracle 数据库中执行语句的统计信息和系统统计信息，这些信息都以文件或系统动态性能视图的方式被存储在数据库系统中，并可以通过Oracle 系统命令进行查看。本文研究了针对 Oracle 数据库的 SQL 语句执行原理和SQL 语句优化规则， 从而获得 优化建议。 本文研究的内容包括： 1.Oracle 数据 库优化 SQL 语句的原理。本研究中 SQL 优化的主要
18、原理是 SQL语句执行的过程和查询机制，针对执行过程找出语句执行的瓶颈是本文优化规则制定的主要依据。 2.Oracle 数据库优化 SQL 语句的方法。本课题中优化的主要方法是依据生成的优化规则对语句进行优化，并获取执行计划进行执行效率的对比，证实优化规则的有效性。 3.SQL 语句优化规则内容。针对 SQL 语句的执行机制，对数据库表的访问方式、连接方式、内部循环次数、记录排序和记录返回方式等方面进行了优化规则的制定，规则中分别提出常用的规则和常见的错误的优化规则。 Oracle 数据库在整个数据库 领域中仍然占有非常重要的地位，它的使用者不计其数，并广泛存在于现行的各种应用系统和应用软件的
19、服务器和客户机平台中。Oracle 数据库的管理工作和执行机制复杂，操作系统的性能、硬件环境的优劣、数据库系统参数的配置、数据库的设计和 SQL 语句的执行都在一定程度上影响着它的性能，所以 SQL 语句优化规则的制定对性能提升有着非常重大的意义。为了改善以往的 SQL 优化研究停留在理论上的情况，通过具体实验和数据从直观上直接了解优化的程度，对系统中执行过的语句进行优劣判断并从获取的语句中寻求可优化的方 案也是数据库性能优化的一个途径。 1.4 论文的 组织结构 本文的研究内容和组织结构由以下部分组成： 1.绪论。 主要介绍了本课题研究的背景、国内外研究历史和现状、研究的内容和意义，最后说明
20、了论文的组织结构。 2.相关知识技术研究。主要介绍了本课题研究过程中涉及到的结构化查询语言。 3.SQL 语句优化的原理。这一部分主要介绍了 Oracle 数据库中 SQL 语句的执行过程和查询机制以及影响执行计划的因素。 4.SQL优化规则的内容和优化方法。主要介绍了 SQL 优化规则的具体内容 和生效原理，并介绍了几种优化方法和根据执行计 划验证的优化规则。 5.SQL 语句优化的实现。主要介绍了针对制定的优化规则，进行的语句优化的4 实例。 6.总结 与展望。这部分主要对所做的研究工作做了总结，并对研究过程中 的不足做了说明，最后指出下一步的研究方向。 5 第二章 相关知识技术研究 本文
21、中 SQL语句优化规则的研究主要涉及到的相关技术有结构化查询 语言 SQL语句的分析方式、 SQL 命令写入文件技术、 Oracle 执行命令的方式、执行计划的获取技术和性能诊断技术。本章将对不同技术从理论或应用的角度出发进行详细论述说明。 2.1 结构化 查询语言 Oracle 是典型 的关系数据库，它执行的 SQL（ Structured Query Language）语言是典型的结构化查询语言，是一种通用、功能性强的关系数据库的标准语言。 结构化查询语言有四个部分：数据库查询语言、数据定义语言、数据操纵语言、数据控制语言。数据查询语言的动词主要是 SELECT， 用来从表中获取数据，是使
22、用最多的语言。数据定义语言的动词主要是 CREATE、 DROP 和 ALTER，用以在数据库中创建、删除和修改表 等 。数据库操纵语言的动词主要是 INSERT、 UPDATE和 DELETE，用以插入、修改和删除表中的数据。 数据库控制语言的动词是 GRANT和 REVOKE，用以控制数据库的对象访问 权限 。 SQL 语句的特点有以下几点： 1.综合统一。 SQL 逐渐成为一种国际标准，它是一种综合的、功能强又简单易学的语言，可以独立的完成数据库整个生命周期中包含的全部活动。 2.高度非过程化。 SQL 语句进行数据库操作时只要明确所做的内容，而无需指明做的方法，因此不必了解存取的路径，
23、操作的具体过程由系统自动完成。 3.操作的方式是面向集合。不同于非关系数据库模型， SQL 语言采用集合的处理方式，除了操作的对象、查找的结果，一次插入操作、删除操 作和更新操作的对象都可以为元组的集合。 4.不同使用方式应用同一种语法结构。它本身是独 立的语言，同时也是一种嵌入式语言。因为它能独立地被应用于联机交互 ，也能被嵌入高级语言的程序，但其语句结构总是保持一致的。 5.语言简单，容易学习使用。 SQL 语言的功能强，但十分简洁，主要因为完成核心功能的动词只用了 9个。 影响语句执行效率的主要是数据查询语言，且在数据库操纵语言中也会涉及到查询语言的查询条件，本文中的优化对象是数据库查询
24、语言。数据库查询语言主要有三个子句： SELECT、 FROM 和 WHERE，分别说明了查询的 列、表和条件。在每一个子句中包含了不同的语句格式，如排序分组等，正因为语句格式的多样性，才提供了 SQL 语句优化的可能性。 2.2 Oracle 命令执行方式 在课题中执行计划的获取和优化建议的获得并不能通过应用客户端 执行 命6 令，而是通过将 SQL 语句嵌入 Oracle 命令中作为 sql 文件转交给 Oracle 内置的工具 SQL*Plus 来执行的，因此本节讲述在客户端中 传统 的 命令的传递方式、执行方式和返回结果的获取。 在 Oracle 中，有三种命令形式，分别是 SQL 语
25、句、 PL/SQL 块和 SQL*Plus命令。这三种命令形式都 可以被 Oracle 数据库识别并执行，但三者是有区别的。 1.SQL 语句是以数据库为操作对象的标准结构化语言，当输入 SQL 语句后，SQL*Plus 将其保存在内部缓冲区中。 2.PL/SQL 块同样是以数据库中的数据为操作对象，但由于 SQL 不具备过程控制功能，所以为了能够与其他语言一样具备面向过程的处理功能，在 SQL 中加入了诸如循环、选择等面向过程的处理功能，由此形成了 PL/SQL。所有对于 PL/SQL语句的解析交由 PL/SQL 引擎执行，其中可编写存储过程、触发器和包等数据库永久对象。 3.SQL*Plu
26、s 是 Oracle 系统为用户提供的创建和管理数据库对象、并与 Oracle服务端直接进行交互的工具，与它进行交互的是所有 Oracle 系统可识别的命令和语句，主要负责格式化查询的结果、编辑和存储执行的 SQL 命令以及设置环境选项。 从以上论述中可以看出， SQL*Plus是与 Oracle数据库 服务端交互的直接工具，某些涉及到数据库底层和内核的命令或参数可以 通过 SQL*Plus通过命令方式来获取，而 SQL 语句和 PL/SQL 并不具备执行此类命令的权限。 在本次研究中 ，我们使用 PL/SQL development 这个可视化 图形界面软 件对参数和系统信息进行统计。 2.
27、3 执行计划的获取 对 SQL 语句的优化从执行计划入手进行分析，优化规则的生成和执行效率也通过执行计划来体现，所以对 SQL 语句进行优化就要先获取到执行计划，它以数据形式展示了 Oracle 执行语句的整个过程。在 Oracle 中获取执行计划需要有几步准备工作： 1.执行的用户需要有 DBA 的权限。执行计划中的部分信息属于系统统计信息，需要赋予查看系统信息的权限即 DBA 的权限。 2.创建表 PLAN_TABLE 表 。 Oracle 默认将执行计划信息写入 PLAN_TABLE 表，只需执行 Oracle 安装目录中的 db_1RDBMSADMINutlplan.sql 文件即可。
28、 在 Oracle 数据库中主要有三 种方式获取 SQL 语句的执行计划： 1.使用数据库系统的 explain plan for 命令，将 SQL 语句执行后的执行计划和统计信息放在系统的 dbms_xplan 包中，调用包的 display 方法进行显示，这种方式的优点是可以不用真正执行 SQL 语句而直接获取到执行计划。这种方式还有一个优点是它可以作为 SQL 语句直接从客户端传到 Oracle 服务端执行，此种情况下它与一个通常 SQL 的语句并无区别。它的返 回值是一个字符串数组型的数据，并可返回前台直接进行数据处理。它的语法是“ explain plan for SQL 语句内容；”，
