ORACLE中SQL查询优化研究论文

导语：ORACLE中SQL查询优化研究论文一文来源于网友上传，不代表本站观点，若需要原创文章可咨询客服老师，欢迎参考。

ORACLE中SQL查询优化研究论文

摘要数据库性能问题一直是决策者及技术人员共同关注的焦点，影响数据库性能的一个重要因素就是SQL查询语句的低效率。论文首先分析了导致SQL查询语句性能低下的四个常见原因以及SQL调优的一般步骤，然后分别针对如何降低I/O操作、在查询语句中如何避免对查询结果的高成本操作以及在多表连接时如何提高查询效率进行了分析。

关键词oracle；sql；优化；连接

1引言

随着网络应用不断发展，系统性能已越来越引起决策者的重视。影响系统性能的因素很多，低效的SQL语句就是其中一个不可忽视的重要原因。论文首先分析导致SQL性能低下的常见原因，然后分析SQL调优应遵循的一般步骤，最后从如何降低I/O、避免对查询结果的高成本操作和多表连接中如何提高SQL性能进行了研究。鉴于目前ORACLE在数据库市场上的主导地位，论文将只针对ORACLE进行讨论。

2影响SQL性能的原因

影响SQL性能的因素很多，如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序(CBO)的正确判断等，这些往往和DBA密切相关。纯粹从SQL语句出发，笔者认为影响SQL性能不外乎以下四个重要原因：

(1)在大记录集上进行高成本操作，如使用了引起排序的谓词等。

(2)过多的I/O操作(含物理I/O与逻辑I/O)，最典型的就是未建立恰当的索引，导致对查询表进行全表扫描。

(3)处理了太多的无用记录，如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。

(4)未充分利用数据库提供的功能，如查询的并行化处理等。

第(4)个原因处理起来相对简单。论文将针对前三个原因论述如何提高SQL查询语句的性能。

3SQL优化的一般步骤

SQL优化一般需经过发现问题、分析问题、提出解决措施、应用措施、测试性能几个步骤，如图1所示。“发现问题就是解决问题的一半”，因此在SQL调优过程中，定位问题SQL是非常重要的一步，一般可借助于ORACLE自带的性能优化工具如STATSPACK、TKPROF、AUTOTRACE等辅助用户进行，同时还应该重视动态性能视图如V$SQL、V$MYSTAT、V$SYSSTAT等的研究。

图1SQL优化的一般步骤

4SQL语句的优化

4.1优化排序操作

排序的成本十分高昂，当在查询语句中使用了引起结果集排序的谓词时，SQL性能必然受到影响。

4.1.1排序过程分析

当待排序数据集不是太大时，服务器在内存(排序区)完成排序操作，如果排序需要更多的内存空间，服务器将进行如下处理：

(1)将数据分成多个小的集合，对每一集合进行排序。

(2)服务器向磁盘申请临时空间，将排好序的中间结果写入临时段，再对另外的集合进行排序。

(3)在所有的集合均排好序后，服务器再将它们进行合并得到最终的结果，如果排序区尺寸太小，合并无法一次完成时，将分多次进行。

从上述分析可知，排序是一种十分昂贵的操作，它消耗大量的CPU时间和内存，触发磁盘分页和交换操作，因此只要有可能，我们就应该在SQL语句中尽量避免排序操作。

4.1.2SQL中引起排序的操作

SQL查询语句中引起排序的操作大致有：ORDERBY和GROUPBY从句；DISTINCT修饰符；UNION、INTERSECT、MINUS集合操作符；多表连接时的排序合并连接(SORTMERGEJOIN)等。

4.1.3如何避免排序

1)建立恰当的索引

对经常进行排序和连接操作的字段建立索引。在建立索引后，当服务器向这些字段发出排序请求时，将直接引用索引而不进行排序操作；当进行等值连接查询操作时，若建立连接的字段未建立索引，服务器进行的是排序合并连接(SORTMERGEJOIN)，连接操作的过程如下：

对进行连接的两个或多个表分别进行全扫描；

对每一个表中的行集分别进行全排序；

合并排序结果。

如果建立连接的字段已建立索引，服务器进行嵌套循环连接(NESTEDLOOPJOINS)，该连接方式不需要任何排序，其过程如下：

对驱动表进行全表扫描；

对返回的每一行利用连接字段值实施索引惟一扫描；

利用从索引扫描中返回的ROWID值在从表中定位记录；

合并主、从表中的匹配记录。

因此，建立索引可避免多数排序操作。

2)用UNIIONALL替换UNION

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。大部分应用中是不会产生重复记录的，最常见的是过程表与历史表UNION。因此，采用UNIONALL操作符替代UNION，因为UNIONALL操作只是简单的将两个结果合并后就返回。

4.2优化I/O

过多的I/O操作会占用CPU时间、消耗大量内存和占用过多的栓锁，因此有必要对SQL的I/O进行优化。优化I/O的最有效方式就是用索引扫描代替全表扫描。

4.2.1应用基于函数的索引

基于函数的索引(FUNCTIONBASEDINDEX，简记为FBI)提供了索引计算列并在查询中使用这些索引的能力。FBI的实质是对查询所需中间结果进行预处理。如果一个FBI与查询语句中的内嵌函数完全匹配，CBO在生成查询计划时，将自动启用索引范围扫描(INDEXRANGESCAN)替换全表扫描(FULLTABLESCAN)。考察下面的代码段并用AUTOTRACE观察创建FBI前后执行计划的变化。

select*fromempwhereupper(ename)=’SCOTT’

创建FBI前，很明显是全表扫描。

ExecutionPlan

……

10TABLEACCESS(FULL)OF''''EMPLOYEES''''(Cost=2Card=1Bytes=22)

idle>CREATEINDEXEMP_UPPER_FIRST_NAMEONEMPLOYEES(UPPER(FIRST_NAME))；

索引已创建。

再次运行相同查询，

ExecutionPlan

……

10TABLEACCESS(BYINDEXROWID)OF''''EMPLOYEES''''(Cost=1Card=1Bytes=22)

21INDEX(RANGESCAN)OF''''EMP_UPPER_FIRST_NAME''''(NON-UNIQUE)(Cost=1Card=1)

这一简单的例子充分说明了FBI在SQL查询优化中的作用。FBI所用的函数可以是用户自己创建的函数，该函数越复杂，基于该函数创建FBI对SQL查询性能的优化作用越明显。

4.2.2应用物化视图和查询重写

物化视图是一个预计算结果集，其中通常包含聚集与多表连接等复杂操作。数据库自动维护物化视图，且随用户的要求进行刷新。查询重写机制就是用数据库中的替代对象(如物化视图)将用户提交的查询重写为完全不同但功能等价的查询。查询重写对用户透明，用户完全按常规编写访问数据库的查询语句，优化程序(CBO)自动决定是否对用户提交的查询进行重写。查询重写是提高查询性能的一种非常有效的方法，尤其是在数据仓库环境中针对汇总、多表连接以及其它高成本的操作方面。

下面以一个非常简单的例子来演示物化视图和查询重写在优化SQL查询性能方面的作用。

selectdept.deptno，dept.dname，count(*)

fromemp，dept

whereemp.deptno=dept.deptno

groupbydept.deptno，dept.dname

查询计划及主要统计数据如下：

执行计划：

-----------------------------------------

……

21HASHJOIN(Cost=5Card=14Bytes=224)

32TABLEACCESS(FULL)OF''''DEPT''''(Cost=2Card=4Bytes=52)

42TABLEACCESS(FULL)OF''''EMP''''(Cost=2Card=14Bytes=42)

主要统计数据：

-----------------------------------------

305recursivecalls

46consistentgets

创建物化视图EMP_DEPT：

creatematerializedviewemp_deptbuildimmediate

refreshondemand

enablequeryrewrite

selectdept.deptno，dept.dname，count(*)

fromemp，dept

whereemp.deptno=dept.deptno

groupbydept.deptno，dept.dname

再次执行查询，执行计划及主要统计数据如下：

执行计划：

-------------------------------------

……

10TABLEACCESS(FULL)OF''''EMP_DEPT''''(Cost=2Card=327Bytes=11445)

主要统计数据：

------------------------------------

79recursivecalls

28consistentgets

可见，在建立物化视图之前，首先执行两个表的全表扫描，然后进行HASH连接，再进行分组排序和选择操作；而建立物化视图后，CBO自动将上述复杂操作转换为对物化视图EMP_DEPT的全扫描，相关的统计数据也有了很大的改善，递归调用(RECURSIVECALLS)由305降到79，逻辑I/O(CONSISTENTGETS)由46降为28。

4.2.3将频繁访问的小表读入CACHE

逻辑I/O总是快于物理I/O。如果数据库中存在被应用程序频繁访问的小表，可将这些表强行读入KEEP池，从而避免物理I/O的发生。

4.3多表连接优化

最能体现查询复杂性的就是多表连接，多表连接操作往往要耗费大量的CPU时间和内存，因此多表连接查询性能优化往往是SQL优化的重点与难点。

4.3.1消除外部连接

通过消除外部连接，不仅使得到的查询更易于读取，而且性能也经常可以得到改善。一般的思路是，有以下形式的查询：

SELECT…，OUTER_JOINED_TABLE.COLUMN

FROMSOME_TABLE，OUTER_JOINED_TO_TABLE

WHERE…=OUTER_JOINED_TO_TABLE(+)

可转换为如下形式的查询：

SELECT…，(SELECTCOLUMNFROMOUTER_JOINED_TO_TABLEWHERE…)FROMSOME_TABLE；

4.3.2谓词前推，优化中间结果

多表连接的性能低下多数是因为连接操作与过滤操作的次序不合理，大多数用户在编写多表连接查询时，总是先进行连接操作再应用过滤条件，这导致服务器做了太多的无用功。针对这类问题，其优化思路就是尽可能将过滤谓词前推，使不符合条件的记录提前被筛选掉，只对符合条件的少数记录进行连接处理，这样可成倍的提高SQL查询效能。

如下图所示的星形模型，现要统计最近三个月进货的商品在各种销售渠道上的销售业绩。

图2产品销售的星形模型

标准连接查询如下：

Selecta.prod_name，sum(b.sale_quant)，

sum(c.sale_quant)，sum(d.sale_quant)

Fromproducta，tele_saleb，online_salec，store_saled

Wherea.prod_id=b.prod_idanda.prod_id=c.prod_id

anda.prod_id=d.prod_idAnda.order_date>sysdate-90

Groupbya.prod_id；

启用内嵌视图，且将条件a.order_date>sysdate-90前移，优化后代码如下：

Selecta.prod_name，b.tele_sale_sum，c.online_sale_sum，d.store_sale_sumFromproducta，

(selectsum(sal_quant)tele_sale_sumfromproduct，tele_sale

Whereproduct.order_date>sysdate-90andproduct.prod_id=tele_sale.prod_id)b，

(selectsum(sal_quant)online_sale_sum

fromproduct，tele_sale

Whereproduct.order_date>sysdate-90andproduct.prod_id=online_sale.prod_id)c，

(selectsum(sal_quant)store_sale_sum

fromproduct，store_sale

Whereproduct.order_date>sysdate-90andproduct.prod_id=store_sale.prod_id)d，

Wherea.prod_id=b.prod_idand

a.prod_id=c.prod_idanda.prod_id=d.prod_id；

5结束语

SQL语言在数据库应用中占有非常重要的地位，其性能的优劣直接影响着整个信息系统的可用性。论文从影响SQL性能的最主要的三个方面入手，分析了如何优化SQL查询的I/O、避免高成本的排序操作和优化多表连接。需要强调的一点是，理解SQL语句所解决的问题比SQL调优本身更重要，因此SQL调优需要系统分析人员、开发人员和数据库管理员密切协作。

参考文献

[1]ThomasKyte.EffectiveOraclebyDesign：DesignandBuildHigh-performanceOracleApplication[M]，TheMcGral-HillCompanies，Inc，2003

[2]KevinLoney，GeorgeKoch，Oracle9i：TheCompleteReference[M]，TheMcGral-HillCompanies，Inc，2002

[3]Oracle9iSQLReferencerelease2(9.2)[OL/M]，2002.10.http：///technology/

[4]Oracle9iDataWarehousingGuiderelease2(9.2)[OL/M]，2002.03.http：///technology/

[5]AlexeyDanchenkov，DonaldBurleson，OracleTuning：TheDefinitiveReference[OL/M]，RampantTechpress，2006.

[6]Oracle9iDatabaseConceptsrelease2(9.2)[OL/M]，2002.08.http：///technology/

[7]Oracle9isuppliedplsqlpackagesandtypesreferencerelease2(9.2)[OL/M]，2002.12.http：///technology/