基于Oracle数据库的SQL语句优化

2011-07-28李展涛曹英忠

网络安全与数据管理 2011年21期

李展涛，曹英忠

(桂林理工大学信息科学与工程学院，广西桂林 541004)

随着信息化技术在各行业的广泛应用，Oracle数据库也越来越多地被使用到很多关键领域，成为国内高端数据库市场的主流产品和众多行业信息化系统的主要支柱。如何充分利用Oracle的各种功能来提高数据库的可用性，如何提高数据库的数据查询响应时间以及如何诊断数据库出现的问题已经成为不断提高Oracle应用水平和提高Oracle数据库应用系统性能的关键[1]。

1 SQL查询过程及优化器

1.1 SQL查询语句的执行过程[2]

查询优化最重要的就是对SQL语句进行优化。调整SQL对性能的改善要比调整其他方面明显得多。理解SQL语句的执行过程有助于更好地对其进行优化。SQL语句在Oracle中是自动执行的，绝大多数用户不需要关心各个阶段的执行细节。但是，对执行的各个阶段的了解会有助于快速找到性能低下的SQL语句，帮助书写出更高效的SQL语句，进而解决问题。几乎所有的SQL语句都分为语法分析、执行、读取数据三大阶段进行处理[3]。SQL查询语句的执行过程如图1所示。

1.2 Oracle查询优化器[4]

SQL是一种非过程化的语言，用户只需要发送取出数据的命令，对于数据的取出方式(如是通过索引还是全表扫描)，则由数据库的优化器决定。Oracle的优化器用来决定SQL访问数据的有效路径，使语句执行所需要的开销最小。在Oracle的发展过程中，一共开发过两类优化器：基于规则的优化器和基于成本的优化器。它们之间的不同之处主要在于取得代价的方法与衡量代价的大小不同。

图1 SQL查询语句执行图

1.3 SQL查询语句的执行计划

Oracle要实现许多步骤才能完成SQL查询语句的执行，优化器将这些步骤组合在一起称为SQL查询语句的执行计划。从执行计划中可以看出数据库是如何执行查询语句的，判断出查询语句的执行是否高效，从而制定查询的优化方案。获取执行计划的方法有以下两种：(1)用Explain plan命令对语句的执行过程的一些信息进行统计，Explain plan用来显示优化器使用的执行计划而不实际运行查询；(2)用Set Autotrace动态查看每个SQL语句的执行计划，Autotrace可以查看会话中每个SQL语句的执行计划。SQL自动地进行Explain plan的工作，不用维护plan table表，因此使用非常方便。

2 系统优化措施

以具体的实例来说明系统优化问题以及调整方法。在某电子产品售后服务系统中，为加强对售后维修点备件使用情况的精确管理，库房发货人员对出库的每件备件粘贴一个唯一的一式两联条码，一联粘贴在发出的好备件上，另一联粘贴到从用户那里返回的坏备件上。发货业务和备件条码管理有关的E-R图如图2所示。

图2 发货业务E-R图

这个系统中有一个查询出库信息详单的视图，该视图在系统运行初期的查询速度较快，但随着时间推移，数据量增加，其中有些表的数据量已达20万行以上，导致该视图的查询速度明显变慢，而由于资金等各方面的原因，短期内很难从硬件方面对系统进行升级。因此决定在其运行的Oracle 9i平台上进行优化。在进行优化前，该视图的查询时间为1′07″左右，运行的硬件环境为：P42.66、IGB内存、240 GB普通IDE硬盘。在SQL*Plus中优化前的运行时间如图3所示。

图3 优化前运行时间

2.1 优化SQL语句

2.1.1 分析SQL语句的执行计划

T1、T2、T3都是大表，且在 T1表上一个组合索引：T1(C1，C2)，注意C1列为索引的引导列。对于查询：：Select T1.C4 from T1，T2，T3 where T2.C4=6 and T1.C1=T2.C1 and T1.C2=T3.C2 and T3.C3=7，跟踪该查询的执行计划如图4所示。

图4 查询的执行计划

分析图4查询计划，找出各个表之间的关联关系，从而得到执行计划中哪个表为驱动表。在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。

执行计划的第3列，即字母部分，每列值的左面有空格作为缩进字符。在该列值左边的空格越多，说明该列值的缩进越多，该列值也越靠右。如图4的执行计划所示：第一列值为6的行的缩进最多，即该行最靠右；第一列值为4、5的行的缩进一样，其靠右的程度也一样，但是第一列值为4的行比第一列值为5的行靠上；在上下关系方面，只对连续的、缩进一致的行有效。对于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS(FULL)OF’T2’，所以这一操作先执行，该操作对应的T2表为第一个驱动表(外部表)，T1表即为内部表。T2与T1表做嵌套循环后生成了新的row source，对该row source进行排序后，与T3表对应的排了序的row source(应用了 T3.C3=7限制条件)进行 MERGEJOIN连接操作。所以由此可以得出如下事实：T2表先与T1表做嵌套循环，然后将生成的row source与T3表做排序合并连接。通过分析上面的执行计划，不能认为T3表一定在T1、T2表之后才被读取，事实上，T2表有可能与 T3表同时被读入内存，因为将表中的数据读入内存的操作可能为并行的。

事实上许多操作可能为交叉进行，因为Oracle读取数据时，如果就是需要一行数据也是将该行所在的整个数据块读入内存，而且还有可能为多块读。看执行计划时，其关键不是看哪个操作先执行，哪个操作后执行，而关键是看表之间连接的顺序 (如需知道哪个为驱动表，这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引)，在从执行计划中判断出哪个表为驱动表后，根据掌握的知识判断该表作为驱动表。在这个例子中，T2为驱动表，表的连接顺序为 (T2->T1)->T3，查询的过程中也使用到了T1表中的索引，因此，Oracle优化器对其进行的优化效果是比较好的。如果分析了执行计划发现不合适，就要对SQL语句进行更改，或用Oracle提供的提示(Hints)使优化器可以选择正确的驱动表，以更为合理的顺序进行表的连接。

2.1.2 使用提示(Hints)干预执行计划[5]

基于成本的优化器智能化程度很高，绝大多数情况下它能对SQL进行合理地优化，减轻了DBA的负担。但有时受到一些因素的影响，优化器也会选择很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用所指定的存取路径或连接类型生成执行计划，从而使语句高效地运行。例如，如果认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则就可以指示优化器使用全表扫描。在Oracle中，是通过为语句添加 Hints(提示)来实现干预优化器优化的目的。Hints是Oracle提供的一种机制，用来告诉优化器按照技术人员告诉它的方式生成执行计划：

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

(2)基于代价的优化器的优化目标，是all_rows还是first_rows。

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

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

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

(6)语句的并行程度。

Hints只应用在其所在SQL语句块上，对其他SQL语句或语句的其他部分没有影响。除了“RULE”提示外，一旦使用别的提示，语句就会自动地改为使用CBO优化器，此时如果数据字典中没有统计数据，就会使用缺省的统计数据。所以如果使用CBO或Hints提示，则最好对表和索引进行定期的分析。

对于表的访问，可以使用两种Hints：FULL和ROWID。FULL提示告诉Oracle使用全表扫描的方式访问指定表。例如：

索引Hints告诉Oracle使用基于索引的扫描方式，不必说明具体的索引名称。例如：

使用Hints对Oracle 优化器缺省的执行路径进行手工修改是一个很有技巧性的工作，一般建议只针对特定的、少数的SQL进行Hints的优化。绝大多数情况下，只要SQL书写规范，Oracle查询优化器的工作情况是相当理想的。Hints提示虽然能带来一些方便，但是不能滥用，因为这种方法过于复杂，缺乏必要的通用性和应变能力，同时增加了维护上的代价。

调整SQL语句后的查询时间图5所示。