基于ORACLE的查询优化

基于ORACLE的查询优化

一、DBMS查询规则

查询优化是在查询执行引擎生成一个执行策略的过程中，尽量使查询的总开销和总时间达到最小。

任何关系数据库都有一套解决查询的规则，而各种关系数据库查询的过程稍有所区别，但是基本的操作思想和过程是一致的。DBMS的查询规则一般分为以下几个过程：语法分析与翻译处理；查询优化处理；执行。

图1 DBMS查询规则图

1.语法分析与翻译处理

查询处理开始之前，系统必须在扩展关系代数的基础上将

查询语句翻译成可使用的形式，为此这一阶段完成两个主要的功能：一是检查查询语句语法的正确性；二是把查询语句分析成关系数据库能够理解的各个组成部分，构造该查询的语法分析树，并将其翻译成关系代数表达式。

2.查询优化处理

查询优化是为查询选择最有效的查询执行计划的过程。查询优化一方面在关系代数级进行优化，要做的是力图找出与给定表达式等价且执行效率最高的一个表达式。为了在诸多查询执行计划中做出选择，优化器必须估计每个查询执行计划的代价。在没有真正执行查询计划之前，准确计算出查询计划的代价通常是不可能的。因此，优化器要利用各关系的统计信息，来对计划做出最佳估计。

3.执行

执行就是将优化器所选择的执行计划执行，执行引擎从相应的数据库文件中筛选数据，将结果输出。

优化器在选取优化规则时，大多数DBMS(如ORACLE、SYBASE、SQL SERVER等)都是采用基于代价的优化方法，优化器从数据字典中获取许多统计信息，根据不同的算法估计出不同查询规划的代价，然后选择一个较优的规划。

对于ORACLE，在ORACLE7之前，主要是使用基于规则的优化器RULE BASED OPTIMIZATION (简称RBO)。从ORACLE8开始，ORACLE把一个代价引擎(COST ENGINE)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划，即基于代价的优化器COST BASED OPTIMIZATION(简称CBO)。

要注意的是：虽然CBO的功能随着ORACLE新版本的推出，功能越来越强，但它不是能包治百病的神药，否则就不再需要DBA了。

由于一系列因素都会影响语句的执行，优化器综合权衡各个因素，在众多的执行计划中选择认为是最佳的执行计划。然而，应用设计人员通常比优化器更知道关于特定应用的数据特点。无论优化器多么智能，在某些情况下开发人员能选择出比优化器选择的最优执行计划还要好的执行计划，这是需要人工干预数据库优化的主要原因。事实表明，在某些情况下，确实需要DBA对某些语句进行手工优化。

二SQL语句优化步骤

数据库系统的生命周期可分为设计、开发和成品三个阶段。

在这三个阶段都可以提高查询速度。由于设计阶段进行数据库性能优化的成本最低，收益最大，因此在设计阶段就应该选择合理的数据库设计方法。而在数据库系统的开发和成品阶段则需对应用程序进行优化：通常应用程序可分为两个方面——源代码和SQL语句。源代码的优化由于涉及程序逻辑的改变，因此优化源代码要在时间成本和风险成本上投入较高代价，因此对SQL语句的优化尤为重要[3]。

调整现有应用程序中SQL语句的最好方法是采取下列步骤[4]:

1.熟悉应用程序。管理员不仅需要熟悉特定的SQL语句，还需要熟悉应用程序的功能和所做的操作。

2.使用SQL TRACE工具分析特定SQL语句执行的操作，所使用的RDBMS的功能和应用程序对这些功能的使用情况。

3.在SQLTRACE中使用EXPLAIN PLAN命令分析优化器执行这些SQL语句的方式。

下面具体的介绍一下三个步骤：

1.熟悉应用程序

管理员不仅需要了解SQL语句，还需要了解这些SQL语句的执行效果。首先，需要将不同的SQL语句形成表格并确定每条

SQL语句对数据库中每个表的访问次数。这个表以可视化方式告诉管理员用户对数据库中哪些表的访问最为频繁。

这个表为管理员确定哪些SQL语句影响了哪些表提供了一个很好的快速参考。管理员还可进一步深化这种方法，并将该表中的SQL语句分成不问的类型，比如SELECT语句、INSERT语句、UPDATE语句和DELETE语句等等。如果应用程序是在脱离实际应用背景的情况下开发的，那么这种方法可能是不现实的。

2.使用SQL TRACE分析SQL语句

通过在SQL语句上执行SQL TRACE工具，管理员可收集到与所有SQL语句执行操作有关的大量极具价值的信息。SQL TRACE 提供了如下一些非常有价值的信息：

（1）解析、执行和返回数据的次数

（2）CPU时间和执行时间

（3）物理读和逻辑读操作次数

（4）处理的记录数量

（5）库缓冲区错误次数

管理员可用这些信息确定哪些SQL语句效率较高，哪些SQL 语句效率较低。这些信息可能会为管理员指出调整SQL语句的方向。为提高效率，可能不得不修改这些SQL语句。通过使用

EXPLAIN PLAN命令，管理员可能会发现从其他方面提高SQL语句效率的方法。

3.使用EXPLAIN PLAN命令分析SQL语句

通过将EXPLAIN PLAN命令的执行结果作为SQL TRACE报表的一部分，管理员可获得关于ORACLE如何执行SQL语句的更为详细的信息。通过EXPLAIN PLAN命令获得的信息(以及通过SQL TRACE获得的信息)能帮助管理员判断SQL语句的执行效率。

4.SQL语句的执行效果

除了检查SQL语句之外，管理员还应当检查SQL语句的执行效果。很多情况下，如果某应用程序被成百上千的用户同时使用，那么—些本身并不重要的细节可能变得非常重要。这样的结果能导致与某个表甚至某一特定记录相关的瓶颈。