浅析Oracle数据库优化器

摘要：随着数据库规模的不断扩大，数据库系统的性能问题也越来越突出，数据库应用系统能否正常、高效地运行倍受关注，数据库优化技术方法的探索具有非常重要的意义。本文主要通过介绍Oracle数据库优化器的应用，了解一种Oracle数据库的SQL 分析和执行的优化工具。

关键词：数据库；优化器

在现代信息技术的带动下，数据库规模不断的扩大，用户数量的增加，数据库应用系统的响应速度下降，性能问题越来越突出。为了改善系统的性能，数据库系统的性能调整与优化对于整个系统的正常运行起着至关重要的作用。

一、Oracle数据库的查询过程

Oracle数据库应用系统通过SQL（Structured Query Language）语言与数据库进行通信，数据库管理系统通过执行用户提交SQL语句完成查询过程。

（1）解析是检查SQL语句的语法和语义，最终生成SQL语句的执行计划，并将SQL语句和执行计划存放到共享池中。（2）执行（Execute）是指服务器进程按照SQL语句执行计划执行SQL语句。（3）提取数据（fetch）是指将被选择行数据返回到客户端。

二、Oracle优化器（Optimizer）

Oracle优化器是Oracle在执行SQL之前分析语句的工具。它负责指定SQL的执行计划，也就是它负责保证 SQL执行的效率最高。这些因素直接决定SQL的执行效率，所以优化器是 SQL 执行的核心，它做出的执行计划好坏，直接决定着SQL的执行效率。

Oracle的优化器有两种优化方式：

* RBO（Rule-Based Optimization）： 基于规则的优化器

在Oracle8i 之前，Oracle使用的是RBO的优化器，它的执行机制非常简单，就是在优化器里面嵌入若干种规则，执行的 SQL语句符合哪种规则（RANK），则按照规则（RANK）制定出相应的执行计划。由于RBO 只是简单的去匹配Rank，所以它的执行计划有时并不是最佳的。因此数据库的查询性能受到影响。

* CBO（Cost-Based Optimization）： 基于代价的优化器

从Oracle8i 开始， Oracle引入了CBO，它的思路是让Oracle获取所有执行计划相关的信息，通过对这些信息做计算分析，最后得出一个代价最小的执行计划作为最终的执行计划。CBO是一种比RBO更理性化的优化器。从Oracle10g开始，Oracle已经彻底丢弃了RBO。 即使在表，索引没有被分析的时候，Oracle依然会使用CBO。此时，Oracle 会使用一种叫做动态采样的技术，在分析SQL的时候，动态的收集表，索引上的一些数据块，使用这些数据块的信息及字典表中关于这些对象的信息来计算出执行计划的代价，从而挑出最优的执行计划。

CPU 代价（Cost）的计算方式现在默认为 CPU+I/O 两者之和。可通过DBMS_XPLAN.DISPLAY_CURSOR 观察更为详细的执行计划。优化器在判断是否用这种方式时，主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多时候，过期统计信息会令优化器做出一个错误的执行计划，因此应及时更新这些信息。CBO应该自动收集，实际却不然，有时候在CBO情况下，还必须定期对大表进行分析。

三、Oracle优化器的模式

1.CHOOSE

这个值表示SQL语句既可以使用RBO优化器也可以使用CBO优化器，而决定该SQL到底使用哪个优化器的唯一因素是，所访问的对象是否存在统计信息。如果所访问的全部对象都存在统计信息，则使用CBO优化器优化SQL；如果只有部分对象存在统计信息，也仍然使用CBO优化器优化SQL，优化器会为不存在统计信息对象依据一些内在信息来生成统计信息，只是这样生成的统计信息可能不准确，而导致产生不理想的执行计划；如果全部对象都无统计信息，则使用RBO来优化该SQL语句。此模式仅在Oracle9i及之前版本中被支持，Oracle10g已经废除。Oracle8i及Oracle9i中为默认值。

2.RULE

不论是否存在统计信息，都将使用RBO优化器来优化SQL。仅在Oracle9i及之前版本中被支持，Oracle10g已经废除。

3.FIRST_ROWS_n

不论是否存在统计信息，都使用CBO优化器，并以最快的速度返回前n行记录。当CBO 的优化模式设置为FIRST_ROWS（n）时，Oracle在执行SQL时，优先考虑将结果集中的前 n条记录以最快的速度反馈回来。优化器并不需要同时将所有符合条件的结果返回，用户也不需要。这时，CBO将考虑用一种最快的返回前n条记录的执行计划，这种执行计划对于SQL的整体执行时间也不是最快的，但是在返回前数据量较少的记录上，确实最快的。

4. FIRST_ROWS

CBO尽可能快速的返回结果集的前面少数行记录。不论是否存在统计信息，都使用CBO优化器，FIRST_ROWS导致CBO使用“试探法”来产生执行计划，这种方式其成本可能会稍大一些。

5.ALL_ROWS

不论是否存在统计信息，都使用CBO优化器，且把CBO的优化目标设定为“最小的成本”。在Oracle 10g中为默认值。当CBO 模式设置为ALL_ROWS 时， Oracle会用最快的速度将SQL执行完毕，将结果集全部返回，它和FIRST_ROWS（n）的区别在于，ALL_ROWS强调以最快的速度将SQL执行完毕，并将所有的结果集反馈回来，而FIRST_ROWS（n）则侧重于返回前 n条记录的执行时间。ALL_ROWS 在OLAP 系统中使用得比较多，它用最快的速度获得 SQL执行的最后一条记录，而不是前 N 条记录。

四、在 CBO下写 SQL语句的注意事项

1.使用CBO 时，必须保证为表和相关的索引搜集足够的统计数据。

2.使用CBO 时，要注意看采用了哪种类型的表连接方式。

3.使用CBO 时，要注意被索引的字段的值的数据分布，会影响SQL语句的执行计划。

4.使用CBO 时，SQL语句“FROM” 子句后面的表，必须全部使用ANALYZE 命令分析过，如果“FROM”子句后面的是视图，则此视图的基础表，也必须全部使用ANALYZE 命令分析过；否则，ORACLE 会在执行此SQL语句之前，自动进行ANALYZE 命令分析，这会极大导致SQL语句执行极其缓慢。

也还有其他需要的注意事项，在工作中要特别留心哦。（作者单位：西安电子科技大学计算机学院）

参考文献：

[1]文平著.Oracle数据库性能优化的艺术[M].北京： 机械工业出版社，2012.7

[2][澳] Guy Harrison 著 郑勇斌，胡怡文，童家旺， 译等. Oracle性能优化求生指南[M].北京： 人民邮电出版社，2012.9

[3]杨小艳. Oracle数据库查询优化方法研究[J].计算机与现代化，2008， 152： 4-7.