[转载] 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查询语句之性。

3  SQL优化的一般步骤

   
SQL优化一般需要通过发现题目、分析问题、提出解决措施、应用措施、测试性能几个步骤,如图1所显示。“发现题目虽是釜底抽薪问题之一半”,因此在SQL调优过程中,定位问题SQL是格外重要的平步,一般不过仰ORACLE自带的属性优化工具要STATSPACK、TKPROF、AUTOTRACE等帮扶用户展开,同时还应该重视动态性视图要V$SQL、V$MYSTAT、V$SYSSTAT等的钻。

图片 1

图1  SQL优化的相似步骤

4  SQL语句之优化

4.1  优化排序操作

   
排序的本非常昂扬,当在查询语句被采用了引起结果集排序的谓词时,SQL性能必然面临震慑。

4.1.1 排序过程分析

   
当待排序数据集不是无限死时,服务器在内存(排序区)完成排序操作,如果排序需要还多的内存空间,服务器将展开如下处理:

    (1) 将数据分为基本上只小的汇聚,对各一样集共进行排序。

    (2)
服务器向磁盘申请临时空间,将败好序的高中级结果写副临时段,再对另外的成团进行排序。

    (3)
在具有的聚合全排好程序后,服务器再用其进行联得到终极之结果,如果排序区尺寸最为小,合并无法等同破就时,将分多次展开。

   
从上述分析会,排序是同栽死昂贵的操作,它吃大量的CPU时间和内存,触发磁盘分页和置换操作,因此只要出或,我们即便应该于SQL语句被尽量避免排序操作。

4.1.2  SQL中引起排序的操作

    SQL查询语句被挑起排序的操作大致有:ORDER BY 和GROUP BY
从句;DISTINCT修饰符;UNION、INTERSECT、MINUS集合操作符;多表连接时的排序合并连接(SORT
MERGE JOIN)等。

4.1.3  如何避免排序

    1)建立适当的目录

   
对常进行排序和连接操作的字段建立目录。在确立目录后,当服务器向这些字段发出排序请求时,将一直引用索引而不开展排序操作;当进行等值连接查询操作时,若建立连接的字段未建目录,服务器进行的是排序合并连接(SORT
MERGE JOIN),连接操作的过程如下:

    对开展连接的蝇头个或多单说明分别展开全扫描;

    对各一个表中的行集分别开展全排序;

    合并排序结果。

    如果成立连接的字段已建立目录,服务器进行嵌套循环连接(NESTED LOOP
JOINS),该连方式不需要外排序,其经过如下:

    对驱动表进行全表扫描;

    对回到的诸一行以连续字段值实施索引惟一扫描;

    利用从索引围观中回到的ROWID值在从表中定位记录;

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

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

    2)用UNIION ALL替换UNION

   
UNION在进展表链接后会筛选掉还的笔录,所以当表链接后会指向所产生的结果集进行排序运算,删除重复的笔录重复回到结果。大部分动被凡是未见面发出重复记录的,最广的凡过程表与历史发明UNION
。因此,采用UNION ALL操作符替代UNION,因为UNION
ALL操作只是略的用少独结果合并后哪怕归。

4.2  优化I/O

   
过多的I/O操作会占用CPU时间、消耗大量内存和占有过多的栓锁,因此产生必不可少对SQL的I/O进行优化。优化I/O的无比有效方式就是是用索引围观代替全表扫描。

4.2.1  应用基于函数的目录

    基给函数的目(FUNCTION BASED
INDEX,简记为FBI)提供了目录计算排并于询问中以这些索引的力。FBI的本来面目是本着查询所用中结果进行先期处理。如果一个FBI与查询语句被之内嵌函数完全匹配,CBO于变更查询计划时,将自行启用索引范围扫描(INDEX
RANGE SCAN)替换全表扫描(FULL TABLE
SCAN)。考察下面的代码段并用AUTOTRACE观察创建FBI前后执行计划之变化。

    select * from emp where upper(ename)=’SCOTT’

    创建FBI前,很明朗是全表扫描。 

    Execution Plan

    ……

       1    0   TABLE ACCESS (FULL) OF ‘EMPLOYEES’ (Cost=2 Card=1
Bytes=22)

    idle>CREATE INDEX EMP_UPPER_FIRST_NAME ON
EMPLOYEES(UPPER(FIRST_NAME));

    索引已开立。

    再次运行相同查询,

    Execution Plan

    ……

       1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘EMPLOYEES’ (Cost=1
Card=1 Bytes=22)

       2    1   INDEX (RANGE SCAN) OF ‘EMP_UPPER_FIRST_NAME’
(NON-UNIQUE) (Cost=1 Card=1)

   
这同样大概的例证充分证实了FBI在SQL查询优化中之图。FBI所用之函数可以是用户自己创立的函数,该函数更是繁杂,基于该函数创造FBI对SQL查询性能的优化作用更强烈。

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

   
物化视图是一个预计算结果集,其中便含聚集和多表连接等复杂操作。数据库自动保护物化视图,且据用户的求开展刷新。查询重写机制就用数据库被的替代对象(如物化视图)将用户提交的查询重写为全两样但力量等的查询。查询重写针对性用户透明,用户完全本正常编排访问数据库的询问语句,优化程序(CBO)自动决定是否对准用户提交的询问进行重写。查询重写是提高查询性能的同种植很管用的方法,尤其是当数据仓库环境遭受对汇总、多表连接和另外强资本的操作方面。

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

图片 2图片 3Code
1图片 4    select dept.deptno,dept.dname,count(*)
2图片 5    from emp,dept
3图片 6    where emp.deptno=dept.deptno
4图片 7    group by dept.deptno,dept.dname

    /

询问计划以及重大统计数据如下:

    执行计划:

    —————————————–

    ……

       2    1   HASH JOIN (Cost=5 Card=14 Bytes=224)

       3    2   TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=2 Card=4 Bytes=52)

       4    2   TABLE ACCESS (FULL) OF ‘EMP’ (Cost=2 Card=14 Bytes=42)

    主要统计数据:

    —————————————–

        305  recursive calls

          46  consistent gets

    创建物化视图EMP_DEPT:

图片 8图片 9Code
1图片 10    create materialized view emp_dept build immediate
2图片 11    refresh on demand
3图片 12    enable query rewrite
4图片 13    as
5图片 14    select dept.deptno,dept.dname,count(*)
6图片 15    from emp,dept
7图片 16    where emp.deptno=dept.deptno
8图片 17    group by dept.deptno,dept.dname

    /

    再次实施查询,执行计划暨主要统计数据如下:

    执行计划:

    ————————————-

    ……

       1    0   TABLE ACCESS (FULL) OF ‘EMP_DEPT’ (Cost=2 Card=327
Bytes=11445)

    主要统计数据:

    ————————————

         79  recursive calls

         28  consistent gets

   
可见,在树物化视图之前,首先实施两个说明底全表扫描,然后进行HASH连接,再开展分组排序和抉择操作;而起物化视图后,CBO自动将上述复杂操作转换为对物化视图EMP_DEPT的全都扫描,相关的统计数据也时有发生矣生非常的改进,递归调用(RECURSIVE
CALLS)由305暴跌到79,逻辑I/O(CONSISTENT GETS)由46下跌为28。

4.2.3  将反复造访的小表读入CACHE

   
逻辑I/O总是快被物理I/O。如果数据库中在叫应用次第往往造访的小表,可将这些发明强行读入KEEP池,从而避免物理I/O的发出。

4.3  多表连接优化

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

4.3.1  消除外部连接

   
通过解除外部连接,不仅要得到的询问更便于读取,而且性能为时常可以收获改善。一般的笔触是,有以下形式之询问:

图片 18图片 19Code
1图片 20    SELECT …,OUTER_JOINED_TABLE.COLUMN
2图片 21    FROM SOME_TABLE,OUTER_JOINED_TO_TABLE
3图片 22    WHERE …=OUTER_JOINED_TO_TABLE(+)

    可转换为如下形式之询问:

   

图片 23图片 24Code
1图片 25SELECT …,(SELECT COLUMN FROM OUTER_ JOINED_TO_TABLE WHERE …)FROM SOME_TABLE;

4.3.2  谓词前推动,优化中结果

   
多表连接的特性低下多数凡是盖老是操作以及过滤操作的次不客观,大多数用户以编写多表连接查询时,总是先进行连续操作更以过滤条件,这致使服务器做了无与伦比多的无用功。针对当时类似问题,其优化思路就尽可能将过滤谓词前推动,使非符合条件的笔录提前吃筛选掉,只针对符合条件的个别记下进行连续处理,这样只是加倍的增进SQL查询功能。

   
如下图所展示之星形模型,现要统计以来叔个月上贾的货物在各种销售渠道及之行销业绩。

图片 26

贪图2  产品销售的星形模型

    标准连接查询如下:

图片 27图片 28Code
1图片 29    Select a.prod_name,sum(b.sale_quant),
2图片 30    sum(c.sale_quant),sum(d.sale_quant)
3图片 31    From product a,tele_sale b,online_sale c,store_sale d
4图片 32    Where a.prod_id=b.prod_id and a.prod_id=c.prod_id 
5图片 33    and a.prod_id=d.prod_id And a.order_date>sysdate-90
6图片 34    Group by a.prod_id;

   
启用内嵌视图,且以标准a.order_date>sysdate-90前换,优化后代码如下:

   

图片 35图片 36Code
 1图片 37    Select a.prod_name,b.tele_sale_sum,c.online_sale_sum,d.store_sale_sum From product a,
 2图片 38    (select sum(sal_quant) tele_sale_sum from product,tele_sale
 3图片 39    Where product.order_date>sysdate-90 and product.prod_id =tele_sale.prod_id) b,
 4图片 40    (select sum(sal_quant) online_sale_sum
 5图片 41    from product,tele_sale
 6图片 42    Where product.order_date>sysdate-90 and product.prod_id =online_sale.prod_id) c,
 7图片 43    (select sum(sal_quant) store_sale_sum 
 8图片 44    from product,store_sale
 9图片 45    Where product.order_date>sysdate-90 and product.prod_id =store_sale.prod_id) d, 
10图片 46
11图片 47    Where a.prod_id=b.prod_id and 
12图片 48    a.prod_id=c.prod_id and a.prod_id=d.prod_id;

5  结束语

   
SQL语言在数据库应用被据为己有十分关键之身份,其属性的优劣直接影响在所有信息体系的可用性。论文从影响SQL性能的绝要的老三单地方下手,分析了什么样优化SQL查询的I/O、避免大资产的排序操作与优化多表连接。需要强调的某些是,理解SQL语句所缓解的题材比较SQL调优本身还要紧,因此SQL调优需要系统分析人员、开发人员和数据库管理员密切合作。

参考文献

[1]Thomas Kyte.Effective Oracle by Design:Design and Build
High-performance Oracle
Application[M],The McGral- Hill
Companies,Inc,2003

[2]Kevin Loney,George Koch,Oracle 9i:The Complete
Reference[M],The McGral-Hill Companies,Inc,2002

[3] Oracle9i SQL Reference release 2(9.2)[OL/M],2002.10.
http://www.oracle.com/technology/

[4] Oracle9i Data Warehousing Guide release 2(9.2) [OL/M],2002.03.
http://www.oracle.com/technology/

[5]Alexey Danchenkov,Donald Burleson,Oracle Tuning:The Definitive
Reference[OL/M],Rampant Techpress,2006.

[6] Oracle9i Database Concepts release 2(9.2) [OL/M],2002.08.
http://www.oracle.com/technology/

[7] Oracle9i supplied plsql packages and types reference release
2(9.2) [OL/M],2002.12. http://www.oracle.com/ technology/

相关文章