`
maosheng
  • 浏览: 560291 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle SQL性能优化深入浅出 2

阅读更多
SQL 语句的编写原则:
1.不要让Oracle做得太多;
   1)避免复杂的多表关联

   2)避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间;
只提取你所要使用的列;
使用别名能够加快解析速度;

   3)避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

   4)用EXISTS替换DISTINCT
低效:
    SELECT DISTINCT DEPT_NO,DEPT_NAME
    FROM DEPT D,EMP E
    WHERE D.DEPT_NO = E.DEPT_NO

高效:
    SELECT DEPT_NO,DEPT_NAME
    FROM DEPT D
    WHERE EXISTS ( SELECT ‘X’
                    FROM EMP E
                    WHERE E.DEPT_NO = D.DEPT_NO);

   5)用UNION-ALL 替换UNION ( if possible)

2.给优化器更明确的命令;
    1)自动选择索引
如果表中有两个以上(包括两个)索引,其中有一个唯一性索引,而其他是非唯一性.在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯一性索引.

    2)至少要包含组合索引的第一列
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

   3)避免在索引列上使用函数
WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
低效:
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;

高效:
SELECT …
FROM DEPT
WHERE SAL  > 25000/12;

   4)避免使用前置通配符
WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)'%'开始, 索引将不被采用.在这种情况下,ORACLE将使用全表扫描.
例如:
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO LIKE '%109204421';

   5)避免在索引列上使用NOT
通常,我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

   6)避免在索引列上使用 IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引 .对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在此
记录. 如果至少有一个列不为空,则记录存在于索引中.
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入). 然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空. 因此你可以插入多条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

   7)避免出现索引列自动转换
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.因为内部发生的类型转换, 这个索引将不会被用到!

   8)在查询时尽量少用格式转换

3.减少访问次数;
    1)使用DECODE来减少处理时间

     2)减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
低效
          SELECT TAB_NAME
          FROM TABLES
          WHERE TAB_NAME = ( SELECT TAB_NAME
                                FROM TAB_COLUMNS
                                WHERE VERSION = 604)
          AND DB_VER= ( SELECT DB_VER
                           FROM TAB_COLUMNS
                           WHERE VERSION = 604)
高效
       SELECT TAB_NAME
          FROM TABLES
          WHERE  (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
                   FROM TAB_COLUMNS
                   WHERE VERSION = 604)   

4.细节上的影响;
   1)WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时,WHERE子句中排在最后的表应当是返回行数可能最少的表,有过滤条件的子句应放在WHERE子句中的最后。

   2)WHERE子句 ——函数、表达式使用
最好不要在WHERE子句中使用函或表达式,如果要使用的话,最好统一使用相同的表达式或函数,这样便于以后使用合理的索引。

   3)Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。

   4)联接列
对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。

select * from employss   where   first_name||''||last_name ='Beill Cliton';

系统优化器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。   select * from employee    where
         first_name ='Beill' and last_name ='Cliton';

   5)带通配符(%)的like语句
通配符(%)在搜寻词首出现,Oracle系统不使用last_name的索引。
select * from employee where last_name like '%cliton%';

在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。在下面的查询中索引得到了使用:
  select * from employee where last_name like 'c%';

   6)用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
低效:
     SELECT REGION,AVG(LOG_SIZE)
       FROM LOCATION
       GROUP BY REGION
       HAVING REGION REGION != ‘SYDNEY’
       AND REGION != ‘PERTH’
高效:
     SELECT REGION,AVG(LOG_SIZE)
       FROM LOCATION
       WHERE REGION REGION != ‘SYDNEY’
       AND REGION != ‘PERTH’
       GROUP BY REGION
顺序:
     WHERE > GROUP > HAVING

   7)用NOT EXISTS 替代 NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),NOT EXISTS要比NOT IN查询效率更高。
低效:

SELECT dname, deptno FROM dept WHERE
deptno NOT IN (SELECT deptno FROM emp);

高效:

SELECT dname, deptno FROM dept WHERE
NOT EXISTS
    (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

2要比1的执行性能好很多。因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index, 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。

   8)用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。
通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。

   9)用>= 替代 >
高效:

   SELECT *
   FROM EMP
   WHERE DEPTNO >=4
  
低效:

   SELECT *
   FROM EMP
   WHERE DEPTNO >3
  
   10)外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度。
例如,下面这条命令执行起来很慢:

select a.empno from emp a where a.empno not in
(select empno from emp1 where job='SALE');

利用外部联接,改写命令如下:

select a.empno from emp a ,emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job='SALE';

这样运行速度明显提高.

SQL 语句的执行步骤:
当一个用户与数据库建立了连接后,会向数据库发出操作请求,也就是向数据库送过去一条(或是几条或一个PL/SQL包)SQL语句。Oracle在接到这条SQL之后,首先会将这个SQL做一个Hash函数运算,得到一个Hash值,然后到共享池中寻找是否有和这个Hash值匹配的SQL存在。如果找到了,Oracle将直接使用已经存在的SQL的执行计划去执行当前的SQL,然后将结果返回给用户。如果在共享池中没有找到相同Hash值的SQL,Oracle会认为这是一条新的SQL,将会按照下面的顺序来执行:

a.语法分析
主要看这条SQL是否符合Oracle规定的语法规则,如果发现语法有误,将向用户抛出一个错误信息.

b.语义分析
当语法分析通过以后,Oracle将对这条SQL做一些对象、权限方面的检查,查看SQL中操作的表是否存在,表中的列是否正确,用户是否有操作这个对象的权限等。

c.生成执行计划
这个过程Oracle将通过一系列的操作,来做出最后SQL的执行计划,比如查看操作对象的统计信息,动态采样等。

d.SQL的执行
Oracle按照上一步生成的执行计划,实际地执行SQL语句,并将结果返回给用户。至此,一条SQL语句执行完毕。

对于OLTP系统来说,相同的SQL重复频率非常高,如果优化器反复解析SQL,必然极大地消耗系统资源;另外,OLTP系统用户请求的结果集都非常小,所以基本上都会考虑使用索引,那么既然大家的执行计划都一致,为什么要对SQL做重复分析呢?bind peeking在第一次获得了一个正确的执行计划之后,后续的所有SQL都按照这个执行计划来执行,可以极大地改善系统的性能,这是由OLTP系统的特性决定的。

对于OLAP系统,他的SQL执行计划和谓词的值关系极大,谓词的值不同,很可能执行计划就不同,如果都采用相同的执行计划,SQL的执行效率必然非常低;另外,一个OLAP系统数据库每天执行的SQL数量远远比OLTP少,并且SQL的重复率远远低于OLTP,这种情况下,SQL
解析花费的代价和SQL的执行花费的代价来比,完全可以忽略。

因此,对于OLAP系统,我觉得不需要做变量绑定,这样做可能发生执行计划选择错误的严重后果。另外,如果事实上的确做了变量绑定,bing peeking也只能保证第一条硬分析的SQL能够选择正确的执行计划,如果后面的谓词变量改变,很可能还是会出现SQL选择错误的执行计划,因此在OLAP系统中,不要绑定变量。

分享到:
评论

相关推荐

    oracle教程--深入浅出oracle学习资料

    Oracle教程——深入浅出Oracle学习资料 Oracle数据库系统是全球广泛应用的关系型数据库管理系统,由甲骨文公司(Oracle Corporation)开发。本教程旨在为初学者和有经验的IT专业人士提供全面、深入的Oracle知识,...

    Oracle数据库性能优化 盖国强著

    盖国强先生的《Oracle数据库性能优化》一书深入浅出地探讨了这个主题,旨在帮助读者理解并掌握提高Oracle数据库效率的关键技术。 在Oracle数据库性能优化中,首要涉及的知识点是SQL优化。SQL语句是数据库操作的基础...

    深入浅出Oracle

    《深入浅出Oracle》这本书是Oracle数据库管理员(DBA)学习和提升技能的重要参考资料。Oracle DBA是一个复杂的领域,涵盖数据库的安装配置、性能优化、备份恢复、安全性管理等多个方面。以下将详细介绍书中可能涉及的...

    深入浅出Oracle.pdf

    总之,《深入浅出Oracle》这本书涵盖了Oracle数据库的基础理论、安装配置、管理维护、性能优化、安全策略等多个方面,对于Oracle数据库的学习者和管理员来说,是一本不可或缺的参考书。通过阅读和实践,读者可以全面...

    《精通Oracle SQL 第2版》PDF版本下载.txt

    该书通过丰富的示例和深入浅出的讲解,帮助读者掌握Oracle SQL的核心概念和技术。 #### 主要内容概览 1. **SQL基础**: 包括表的创建、数据的插入、更新和删除等基本操作。 2. **数据查询**: 教授如何编写高效的SQL...

    浅谈Oracle数据库SQL性能优化 (1).pdf

    本文深入探讨了Oracle数据库中SQL性能优化的方法和技术。首先,文中强调了优化SQL语句对系统性能的重要性,指出应用程序的性能在很大程度上取决于数据库中SQL语句的执行效率。因此,为了提升Oracle数据库性能,必须...

    深入浅出Oracle: DBA入门、进阶与诊断案例.pdf

    《深入浅出Oracle:DBA入门、进阶与诊断案例》是一本专为数据库管理员(DBA)设计的Oracle技术指南。这本书详细介绍了Oracle数据库管理的基础知识,中级技能以及高级故障诊断技巧,旨在帮助读者从新手到专家逐步提升...

    深入浅出Oracle.rar

    在"深入浅出Oracle"这份资料中,读者可能将接触到Oracle的安装配置、数据库设计、SQL查询优化、存储结构、备份恢复策略、性能调优、安全性管理以及高级特性等内容。通过学习,无论是数据库管理员、开发人员还是系统...

    深入浅出 SQL.pdf

    下面将详细介绍SQL的基础知识、SQL在数据库操作中的应用以及SQL的基本语法结构,旨在深入浅出地解释SQL在IT行业中的重要性。 SQL是用于访问和处理数据库的标准的计算机语言。它广泛应用于多种数据库系统,如MySQL、...

    深入浅出Oracle:DBA入门、进阶与诊断案例

    《深入浅出Oracle:DBA入门、进阶与诊断案例》是盖国强先生的一部经典之作,专门针对Oracle数据库管理员(DBA)的学习路径和实际问题解决提供了详尽的指导。Oracle数据库是全球广泛使用的大型关系型数据库系统,对于...

    深入浅出Oracle: DBA入门、进阶与诊断案例(eygle)原生PDF+源代码--eygle(盖国强)

    在分析实例的过程中,兼顾深度与广度,不仅对实际问题的现象、产生原因和相关的原理进行了深入浅出的讲解,更主要的是,结合实际应用环境,提供了一系列解决问题的思路和方法,包括详细的操作步骤,具有很强的实战性...

    深入浅出Oracle:DBA入门

    本书《深入浅出Oracle:DBA入门、进阶与诊断案例》由盖国强编著,由人民邮电出版社出版,旨在为初学者提供全面的Oracle数据库管理入门知识,并为有一定经验的DBA提供进阶技巧和实际案例分析。 首先,本书开篇可能会...

    oracle sql 大全(三个文档)

    这套资料主要由三个文档组成,分别是"Oracle8i_9i数据库基础.pdf"、"oracle1.pdf"和"SQL基础.pdf",它们分别从不同角度深入浅出地介绍了Oracle SQL的各个方面。 首先,"Oracle8i_9i数据库基础.pdf"可能是针对Oracle...

    深入浅出Oracle之Forms开发指南

    《深入浅出Oracle之Forms开发指南》是一本专注于Oracle企业资源规划(ERP)E-BUSINESS SUITE中的核心应用技术——Forms开发的书籍。作者黄建华在2008年首次发布并更新了该文档,旨在提供Oracle Forms的详细开发指导...

    深入浅出Oracle: DBA入门、进阶与诊断案例. .pdf

    《深入浅出Oracle:DBA入门、进阶与诊断案例》是数据库领域的经典之作,由知名专家eagle精心编著,旨在帮助读者全面理解和掌握Oracle数据库管理(DBA)的各项技能。Oracle DBA是一个关键角色,负责维护Oracle数据库...

Global site tag (gtag.js) - Google Analytics