Oracle中SQL优化原理分析

文章编号：1672-5913(2009)10-0184-03

摘要：对于SQL语句优化，因为会有成千上万条SQL语句访问Oracle数据库，所以这是最费时间的调整操作。所谓的SQL语句优化就是选择最有效的执行计划来执行SQL语句的过程。本文对Oracle中SQL的优化原理进行了分析。

关键词：Oracle；优化；SQL

中图分类号：G642

文献标识码：B

1数据库优化的必要性

随着数据库技术应用越来越广泛，用户日益增长，使数据量越来越大，且伴随数据挖掘、数据分析不断深人，数据处理也越来越复杂，庞大数据量加上复杂数据处理过程，使数据库服务器经常超负荷运作，甚至出现死锁现象，严重影响使用效果，若不注重SQL 查询优化策略，往往在系统投人初期使用时查询反应迅速，使用流畅，但随着数据库表中记录数据量越来越大，系统响应速度越来越慢，甚至无法正常响应，就可能导致系统可用性降低，失去实用价值。因此，科学地构造数据库结构，合理使用查询语句及查询方法，是成功开发和应用数据库系统重要环节，对于SQL语句优化，因为会有成千上万条SQL语句访问Oracle数据库，所以这是最费时间的调整操作。所谓的SQL语句优化就是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句(SELECT，INSERT，UPDATE或DELETE)中的一个重要步骤。总体上讲，就是需要识别出最常用的SQL语句，通过仔细审查SQL的执行计划以及使用Oracle提示(HINTS)调整执行计划来调整各个语句。本文就优化Oracle数据库SQL原理做一些分析。

2SQL优化目标

一般来说，Oracle的应用分为两种类型:联机事务处理(OLTP)，决策支持系统(DSS)。根据应用类型的不同，性能优化的目标有所不同：

2.1在线事务处理(OLTP=OnlineTransactionProcessing)

该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取。OTLP的主要目标是可用性、速度、并发性和可恢复性。

当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与Cluster表，因为这些结构会使插入和更新操作变慢。

此种类型的应用程序把吞吐量定义为性能指标。

2.2决策支持系统(DSS=DecisionSupportSystem)

该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型的情况是:决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统。

决策支持的关键目标是速度、精确性和可用性。

该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、Cluster表、并行查询等。

此种类型的应用程序把响应时间定义为性能指标。

用户的应用系统属于典型的决策支持系统，用户需求的关键就是尽量减少应用程序的查询响应时间。

3DML语句的处理原理

用户的应用系统的主要工作就是使用DML语句对数据库进行查询，要对SQL查询语句进行优化，就必须对DML语句的处理原理有所了解。

DML语句的处理一般分9步：

第1步：创建游标(Create a Cursor)

由程序接口调用创建一个游标(Cursor)。任何SQL语句都会创建它，特别在运行DML语句时，都是自动创建游标的，不需要开发人员干预。多数应用中，游标的创建是自动的。然而，在预编译程序Pro*C)中游标的创建，可能是隐含的，也可能显式的创建。在存储过程中也是这样的。

第2步：分析语句(Parse the Statement)

在语法分析期间，SQL语句从用户进程传送到Oracle，

SQL语句经语法分析后，SQL语句本身与分析的信息都被装入到共享SQL区。在该阶段中，可以解决许多类型的错误。

语法分析分别执行下列操作：翻译SQL语句，验证它是合法的语句，即书写正确；实现数据字典的查找，以验证是否符合表和列的定义；今在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义；验证为存取所涉及的模式对象所需的权限是否满足；决定此语句最佳的执行计划；将它装入共享SQL区；对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点。

以上任何一步出现错误，都将导致语句报错，中止执行。

只有在共享池中不存在等价SQL语句的情况下，才对SQL语句作语法分析。在这种情况下，数据库内核重新为该语句分配新的共享SQL区，并对语句进行语法分析。进行语法分析需要耗费较多的资源，所以要尽量避免进行语法分析。

语法分析阶段包含了不管此语句将执行多少次，而只需分析一次的处理要求。Oracle只对每个SQL语句翻译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语法分析，也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(Bind Variable)实现的，也就是共享SQL。

SQL查询语句与其它类型的SQL语句不同，因为在成功执行后作为结果将返回数据。其它语句只是简单地返回成功或失败，而查询则能返回一行或许多行数据。查询的结果均采用表格形式，结果行被一次一行或者批量地被检索出来。从这里可以得知批量的Fetch数据可以降低网络开销，所以批量的Fetch也是优化的技巧之一。

第3步：描述查询结果(Describe Results of a Query)

描述阶段只有在查询结果的各个列是未知时才需要；例如，当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征(数据类型，长度和名字)。

第4步：定义查询的输出数据(Define Output of a Query)

在查询的定义阶段，指定与查询出的列值对应的接收变量的位置、大小和数据类型，这样可通过接收变量就可以得到查询结果。如果必要的话，Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。

第5步：绑定变量(Bind Any Variables)

此时，Oracle知道了SQL语句的意思，但仍没有足够的信息用于执行该语句。Oracle需要得到在语句中列出的所有变量的值。得到这个值的过程就叫绑定变量(binding variables)

因为指定了变量名，当再次执行之前无须重新捆绑变量。而只需改变绑定变量的值，而Oracle在每次执行时，仅仅使用内存地址来查找此值。

第6步：并行执行语句(Parallelize the Statement)

Oracle可以在SELECT，INSERT，UPDATE，MERGE，DELETE语句中执行相应并行查询操作，对于某些DDL操作，如创建索引、用子查询创建表、在分区表上的操作，也可以执行并行操作。并行化可以导致多个服务器进程(Oracle Server Processes)为同一个SQL语句工作，使该SQL语句可以快速完成，但是会耗费更多的资源，所以除非很有必要，否则不要使用并行查询。

第7步：执行语句(Run the Statement)

到了现在这个时候，Oracle拥有所有需要的信息与资源，因此可以真正运行SQL语句了。如果该语句为SELECT查询或工INSERT语句，则不需要锁定任何行，因为没有数据需要被改变。然而，如果语句为UPDATE或DELETE语句，则该语句影响的所有行都被锁定，防止该用户提交或回滚之前，别的用户对这些数据进行修改。这保证了数据的一致性。

第8步：取出查询的行(Fetch Rows of a Query)

在Fetch阶段，行数据被取出来，每个后续的存取操作检索结果集中的下一行数据，直到最后一行被取出来。上面提到过，批量的Fetch是优化的技巧之一。

第9步：关闭游标(Close the Cursor)

SQL语句处理的最后一个阶段就是关闭游标。

4Oracle优化器

优化器是SQL处理引擎的核心，Oracle服务器支持两种优化器：基于规则的优化器和基于成本的优化器。

基于规则的优化器一Rule Based(Heuristic) Optimization(简称RBO)；Oracle在基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。

基于成本的优化器一Cost Based Optimization(简称CBO)：Oracle把一个成本引擎(CostEngine)集成到数据库内核中，用来估计每个执行计划需要的成本，该成本将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个成本选择出最优的执行计划。一个查询耗费的资源可以被分成3个基本组成部分：I/O成本、CPU成本、Network成本。

数据库使用何种优化器主要是由可变参数Optimizer_mode决定的。该参数可取值为：

RULE为使用RBO优化器。

CHOOSE则是根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则就使用CBO优化器，否则为RBO优化器。

ALL_Rows为CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句。

FIRST_Rows为CBO优化器使用的第二种具体的优化方法，是以数据的响应时间为主要目标，以便快速查询出开始的几行数据。

5执行计划

在执行一个DML语句之前，Oracle生成一个决定SQL语句将如何执行的查询执行计划。计划生成过程使用了各种不同信息，包括：生成的统计量、对象尺寸、索引的存在、优化器模式、HINT的出现、储存概要的存在。

执行计划一般由几个步骤组成。计划里的每个步骤从数据文件物理地读取块或从缓存区读取块，然后处理每个块中的表行或索引数据，为下一步做好准备。最后阶段是创建结果集。

6共享多SQL语句

为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，Oracle将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA的共享存储区中的内存可以被所有的数据库用户共享。因此，当再次执行一个SQL语句时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则Oracle就不需要再进行分析，直接得到该语句的执行路径。Oracle的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池和尽可能的使用绑定变量的方法执行SQL语句。

7ROWID

ROWID是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个ROWID的伪列，但是表中并不物理存储ROWID列的值。

不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则ROWID在该行的生命周期内是唯一的，即即使该行产生行迁移，行的ROWID也不会改变。

8HINTS

HINTS是Oracle提供的一种机制，用来使优化器按照HINTS生成执行计划。HINTS可以实现：

(1) 使用的优化器的类型；

(2) 基于代价的优化器的优化目标，是ALL_Rows还是FIRST_Rows；

(3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用ROWID；

(4) 表之间的连接类型；

(5) 表之间的连接顺序；

(6) 语句的并行程度。

除了RULES提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果Oracle的数据字典中没有统计数据，就会使用缺省的统计数据。所以在使用CBO或HINTS提示，最好对表和索引进行定期的分析。

9结束语

在数据库应用开发过程中，优化设计可提高数据库性能，特别是大型数据库，优化过程更为重要 当然数据库性能优化是一个复杂过程，本文只是对数据库中SQL的优化原理进行了分析并提供了一些优化建议，供实际应用时参考。

参考文献：

[1] 李丹. Oracle的性能优化[J]. 河北工程技术职业学院学报,2003(1).

[2] 王伦文,钟子发. Oracle数据库设计优化与实践[J]. 电讯技术,2001(1).

[3] 闫伟,张喜民,张军华. Oracle数据库及应用程序优化[J]. 内蒙古石油化工,2005(1).

[4] 张韬. 浅析Oracle数据库的性能优化[J]. 中国科技信息,2005(5).