Oracle数据库的优化

摘要 本文提出了一种优化Oracle数据库的方法。Oracle中SQL语句的执行过程可分为解析（Parse）、执行（Execute）和提取结果（Fetch）三步，此方法就是通过对SQL语句在Oracle数据库中优化执行的三个过程来提高Oracle数据库的性能。

关键词 数据库 扫描 多表联结 子查询

1 如何优化Parse

1． 1 SQL语句的Parse处理步骤：

1） 计算语句值

2） 共享池中有无与此语句值相同的语句？

3） 共享池中有与此语句字符完全匹配的语句？

4） 准备要运行的SQL语句

5） 为新语句在共享池中创建空间

6） 将语句存放在共享池中

7） 修改共享池图，标明语句的值和在共享池中的位置

8） 执行准备好的SQL语句

最理想的是，语句只执行1、2、3和8步来进行处理。不经过2、3步来测试被传给Oracle的语句要使用1～8步进行处理。只经过1、2、3、8的SQL语句要比经过1～8步的语句更为有效。

1．2 在共享池中重用SQL语句

当SQL语句被传递给Oracle处理时，其秘诀是重复使用已经在共享池中的语句，而不是让Oracle在接受语句时去准备新的语句。前面表明，如果Oracle接受了一个与共享池中的语句相一致的语句，就重用共享池中的语句。

Oracle提供在数据库中存储代码的能力。当应用系统开始运行时，从数据库中读取代码（可用PL/SQL语句编制）并像其它语句那样传递到共享池中去处理。从数据库中取出的代码是编译过的并驻留在共享池中。

可以利用数据库中存储的程序代码设计应用系统，检查所有的事务处理以及主要的通用的过程，研究现有的应用系统并把主要的处理程序转换为数据库中存储的程序代码。在Oracle中存储代码可以通过过程、程序包、函数、触发器等来实现。

2 如何优化Execute和Fetch

2．1 避免无计划的全表扫描

全表扫描连续从表读取所有数据，而不管数据是否与查询有关。避免不必要的全表扫描有两个充足理由：1）全表扫描没有选择性

2） 过全表扫描读取的数据很快从SGA的缓冲区移走（如果正在扫描的表不是“高速存储”的表）

在基于规则优化的情况下，如果下列任何条件在SGA语句出现，就要对一个表进行全表扫描。

1） 该表无索引

2） 对返回的行无任何限定条件（如无Where语句）

3） 对数据表与任何索引主列相对应的行无限定条件。例如，在City－State－Zip列上创建了三列复合索引，那么仅对State列有限定条件的查询不能使用这个索引，因为State不是索引的主列。

4） 对索引主列的行有限定条件，但条件或者是NULL或者是不相等。例如，City列上存在索引，在所有下列情况下都不会使用索引。

Where city is null

Where city is not null

Where city!=’liaoning’

5） 对索引主列的行有限定条件，但条件在表达式里使用。例如，如果在City列上索引，那么限定条件

Where City=’liaoning’

可以使用索引。然而，如果限定条件是

Where UPPER(City)=’liaoning’

那么不会使用City列上的索引，因为City列在UPPER函数里。如果将City列与文本字符串联结在一起，也不会使用索引。例如，如果限定条件是

Where City||’x’ like ‘liaoning%’

那么不会使用City列上的索引。

6） 对索引主列的行有限定条件，但条件使用Like操作以及值以‘％’开始或者值是一个赋值变量。例如，在所有下列情况下都不会使用索引：

Where City like ‘%aonin%’

Where City like :City_Bind_Variable

如果表小、索引列无选择性，基于开销的优化器可能决定使用全表扫描。

2．2只使用选择性索引

索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。

如果使用基于开销的最优化，优化器不应该使用选择性不好的索引。

索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。

索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。

2．3管理多表联结

Oracle提供了3个联结操作：NESTED LOOPS、HASH JOIN和MERGE JOIN。MERGE JOIN是一组操作，在所有行被处理完之前，它不返任何记录给下一操作。NESTED LOOPS和HASH是行操作，因此会很快将第一批记录返回给下一个操作。

在每个联结选项里，必须执行一些步骤以获取最好的联结性能。如果没有适当地优化联结操作，那么联结所需的时间也许随着表的增长而呈指数级地增长。

2．4管理包含视图的SQL语句

如果查询包含视图，优化器有两种执行查询的方法：首先解决视图然后执行查询，或者把视图文本集成到查询里去。如果首先执行视图，那么首先完成全部的结果集，然后用其余的查询条件做过滤器。

首先解决视图会导致查询性能下降的问题，这取决于所涉及表的相对大小。如果视图被集成到查询里，那么查询的条件也可以应用于视图里，并且可以使用一个小一些的结果集。然而在一些情况下，也许可以通过视图分离组操作提高查询性能。

如果一个视图包含集合的操作（如Group by、SUM、COUNT或者DISTINCT），那么视图不能被集成到查询里去。

不使用组或者没有集合操作的视图的SQL语法可以被集成到大的查询里去。

2．5优化子查询

当使用自查询时，也许会碰到几个独特的问题。涉及子查询的查询潜在问题如下：

 也许在执行完查询的剩余部分前执行子查询（与执行分组功能的视图相似）。

 子查询也许要求特定的提示，但这些提示不直接与调用该子查询的查询有关

 可以作为单个查询执行的子查询也许被代替写成几个不同的子查询。

 也许在使用not in子句或者not exists子句时，不能在最有效的方式下进行子查询的存在查询。

1）当执行子查询时

如果一个查询包含子查询，那么优化器有两种完成查询的方法：首先完成子查询，然后完成查询（“视图的方法”），或者将子查询集成到查询里去（“联结”的方法）。如果首先解决子查询，那么整个子查询的结果集将首先被计算，并且用查询条件的剩余部分做过滤器。如果没有使用子查询去进行存在检查，那么“联结”方法将通常要比“视图”方法完成得好。

如果一个子查询包括集合操作，如group by，SUM或者DISTINCT，那么不能集成子查询到查询的其余部分里去。非集成的子查询限制了提供给优化器的选项。

2）如何组合子查询

一个查询可以包含多个子查询，使用的子查询越多，集成或者重写它们到大的联结里就越困难。既然有多个子查询使集成困难，就应该尽可能地组合多个子查询。

3）怎样进行存在检查

有时子查询不返回行（记录），但可以进行数据正确性检查。在相关表里的记录或者存在或者不存在的逻辑检查，称为存在检查。可以使用exists和not exists子句提高存在检查的性能。

2．6管理对非常巨大的表的访问

随着表增长到比SGA的数据块高速缓冲区存储器的空间显著大时，需要从另一个角度优化对这个表的查询。

1）问题

当表和它的索引小的时候，在SGA里可以有高度的数据共享。多用户读表或索引范围扫描可以反复使用同一个块。随着表的增长，表的索引也在增长。随着表和它的索引增长到比SGA里提供的空间大时，范围扫描需要的下一行将在SGA里找到的可能性变小，数据库的命中率将减小。最后，每一个逻辑读将要求一个单独的物理读。对使用非常大的表的优化方法着眼于特别的索引技术和有关索引的选择。

2）管理数据接近

在访问非常大的表期间，如果倾向于继续使用索引，那么应该关注数据接近，即逻辑相关记录的物理关系。为了使数据最大限度地接近，应该连续往表里插入记录。记录按通常在表的范围扫描里使用的列排序。

3）避免没有帮助的索引扫描

如果要对大表使用索引扫描，那么不能假定索引扫描将比全表扫描执行得更好。不紧跟表访问的索引唯一扫描或范围扫描执行得比较好，但紧跟通过RowID的表访问的索引范围扫描也许执行得差。随着表增长到比数据块高速缓冲存储器大得多，最终，索引扫描和全表扫描间的平衡点打破。

4）创建充分索引的表

如果表中的数据相当稳定，充分索引一个表是很有用的。创建一个复合索引，它包括所有在查询期间通常选择的列。在查询期间，查询要求的所有数据可以通过索引访问提供，不需要任何表访问。

5）并行选项

可以把一个数据库任务，比如Select语句，分为多个单元的工作，由多个Oracle进程同时执行。这种能够允许数据库的单个查询活动由多个协调的进程透明地进行处理的能力，称为并行查询选项（PQO）。

并行选项调用多个进程来利用空闲的系统资源，以减少完成任务所需要的时间。并行选项并不减少处理过程所要求的资源数量，而是把处理的任务分散给多个CPU。为了从并行选项中得到最大的好处，应该使CPU和磁盘上的I/O不要满负荷使用。因为并行的目的是使更多的CPU和磁盘同时参与处理数据库的命令，一个缺乏CPU和I/O资源的服务程序是不能从并行选项中得到好处的。

2．7使用UNION ALL而不是UNION

最常用的集操作是UNION操作，UNION操作使多个记录集联结成为单个集。UNION操作的数学定义是返回记录的单个集并且没有重复的行，所以在合并结果集里，Oracle只返回不同的记录。

当UNION操作用作SQL语句的一部分时，唯一性要求强迫Oracle移走重复的记录。Oracle的移走重复记录的功能是SORT UNIQUE操作，它与使用DISTINCT子句时执行的操作类似。

UNION ALL操作允许重复。UNION ALL不要求SORT UNIQUE操作，从而节省了开销。UNION ALL是一个行操作，所以当其变为有效就返回给用户。而UNION包括SORT UNIQUE集操作，在全部记录的集的排序结束前，不返回任何记录给用户。

当UNION ALL操作产生巨大的结果集时，不需要任何排序便返回记录给应用的事实意味着第一行检索的响应时间更快，并且在许多情况下，可以不用临时段完成操作。

在有些情况下，UNION ALL和UNION不返回同样的结果。如果在应用环境中，结果集并不包含任何重复的记录，则可以把UNION转换成UNION ALL。

2．8避免在SQL里使用PL/SQL功能调用

对于增加PL/SQL的使用，许多用户试图利PL/SQL功能的优势产生可重复使用的代码。其中一个强迫重复使用PL/SQL功能的方法是在SQL语句里使用。例如，可以创建一个将国际货币转换为US$的函数。这个函数称为US$。示例如下：

select transaction_type, US$ (amount, currency)

from international_transaction

where US$ (amount, currency) > 1000;

执行前面的SQL语句没有所期望的那样好。在测试时，它的性能大约比下面得出相同结果的SQL语句慢大约几十倍。

select transaction_type,

amount*exchange_rate US$

from exchange_rate er, international_transaction it

where er.currency = it.currency

and amount*exchange_rate > 1000;

响应时间不同的原因是混合PL/SQL和SQL时，Oracle使用的机制不同。在SQL查询里嵌入PL/SQL功能时，在执行时，Oracle将调用分成两部分：用带有赋值变量的SQL语句代替功能调用以及对每一个函数调用的无名PL/SQL块。

select transaction_type, :a1

from international_transaction

where :a1 > 1000

和

BEGIN

:a1 := US$ (:amount, :currency);

END

对在international_transaction表里的每一行，将执行在前面示例里显示的无名块两次。无名块调用导致查询响应时间的剧增。应该避免在SQL语句里使用PL/SQL功能调用。

参考文献

[1]　瓮正科，王新英著.Oracle8.X For Windows NT实用教程［M］.北京：清华大学出版社，1999.

[2]　萨师萱，王珊著.数据库系统概论［M］. 北京：高等教育出版社，2000.2.

[3]　Eyal Aronoff，Kevin Loney，Noorali Sonawalla著, 李逸波、王华驹、马赛红、曲宁等译.Oracle8性能优化和管理手册［M］.北京：电子工业出版社，2000.1.

[4]　Peter Koletzke,Dr.Paul Dorsey著, 刘晓霞、孙登峰、曲京、何粼等译.Oracle Designer信息系统开发［M］.

北京：机械工业出版社，1999.8