Oracle SQL性能优化

作者：xcbeyond
疯狂源自梦想，技术成就辉煌！微信公众号：《程序猿技术大咖》号主，专注后端开发多年，拥有丰富的研发经验，乐于技术输出、分享，现阶段从事微服务架构项目的研发工作，涉及架构设计、技术选型、业务研发等工作。对于Java、微服务、数据库、Docker有深入了解，并有大量的调优经验。 







 


1、选择合适的Oracle优化器

      Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。

       通过PL/SQL Developer、Toad等工具可以方便的查看一个SQL语句的执行计划。

      在Load中，使用快捷键Ctrl+E，查看执行计划。也可以在sqlplus中通过命令查看：

 

    SQL> explain plan for select * from emp;
     
    已解释。
     
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
     
    Plan hash value: 3956160932
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
    已选择8行。

注：ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。

       Operation： 当前操作的内容。

       Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。

       Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。

       Time：Oracle 估计当前操作的时间。
(1).RBO方式

       即基于规则的优化方式(Rule-Based Optimization,简称为RBO)。优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。
(2).CBO方式

        基于代价的优化方式(Cost-Based Optimization,简称为CBO)。顾名思义，它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。

         我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。

         新版本的oracle逐渐抛弃对Rule方式的支持,即使是Rule方式,最后sql执行效率的衡量标准都是,sql执行消耗了多少资源?对代价(COST)的优化方式,需要表,索引的统计信息,需要每天多表和索引进行定时的分析,但是统计信息也是历史的,有时候也不一定是最优的,统计信息等于就是一个人的经验,根据以前的经验来判断sql该怎么执行(得到优化的sql执行路径),所以具体优化执行的时候,先手工分析sql,看是用RBO方式消耗大,还是CBO消耗大;DBA的工作就是要根据当前oracle的运行日志,进行各种调整,使当前的oracle运行效率尽量达到最优.可以在运行期间,采用hint灵活地采用优化方式。
(3).CHOOSE方式

       设置缺省的Oracle优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖。
       为了使用基于成本的优化器(CBO，Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。

       如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的Oracle优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过， 优化器模式将自动成为CBO ， 反之，数据库将采用RULE形式的优化器。
       在缺省情况下，Oracle采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的Oracle优化器。

 
2、选择最有效率的表名顺序

       ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理 。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。只在基于规则的优化器中有效。

       例如：

             表 TAB1          100000 条记录
             表 TAB2              1       条记录

     选择TAB2作为基础表 (最好的方法)
     select count(*) from tab1,tab2   执行时间0.96秒
     
     选择TAB2作为基础表 (不佳的方法)
     select count(*) from tab2,tab1   执行时间26.09秒

        如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

   例如：

       SELECT * FROM LOCATION L, CATEGORY C, EMP E
         WHERE E.EMP_NO BETWEEN 1000 AND 2000
         AND E.CAT_NO = C.CAT_NO
         AND E.LOCN = L.LOCN;
       将比下列SQL更有效率
       SELECT * FROM EMP E, LOCATION L, CATEGORY C
         WHERE E.CAT_NO = C.CAT_NO
         AND E.LOCN = L.LOCN
         AND E.EMP_NO BETWEEN 1000 AND 2000;

3、Where子句中的连接顺序

         Oracle采用自下而上的顺序解析WHERE子句。根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

    例如:

    (低效,执行时间156.3秒)
      SELECT  *
        FROM EMP E
         WHERE  SAL > 50000
         AND  JOB = ‘MANAGER’
         AND  25 < (SELECT COUNT(*) FROM EMP
                             WHERE MGR = E.EMPNO);
     
    <p>(高效,执行时间10.6秒)</p><p>   SELECT *</p><p>     FROM EMP E</p><p>      WHERE 25 <(SELECT COUNT(*) FROM EMP</p><p>         WHERE MGR=E.EMPNO)</p><p>     AND SAL > 50000</p><p>     AND JOB = ‘MANAGER’;</p>

4、SELECT子句中避免使用“*”

    Oracle在解析SQL语句的时候，对于“*”将通过查询数据库字典来将其转换成对应的列名。

    如果在Select子句中需要列出所有的Column时，建议列出所有的Column名称，而不是简单的用“*”来替代，这样可以减少多于的数据库查询开销。

5、减少访问数据库的次数

      当执行每条SQL语句时, ORACLE在内部执行了许多工作：

     解析SQL语句

    估算索引的利用率

    绑定变量

    读数据块等等

   由此可见， 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。

   如果有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系），以减少多于的数据库IO开销。虽然采取这种方法，效率得到提高，但是程序的可读性大大降低，所以还是要权衡之间的利弊 。
6、使用Truncate而非Delete

    Delete表中记录的时候，Oracle会在Rollback段中保存删除信息以备恢复。Truncate删除表中记录的时候不保存删除信息，不能恢复。因此Truncate删除记录比Delete快，而且占用资源少。

    删除表中记录的时候，如果不需要恢复的情况之下应该尽量使用Truncate而不是Delete。

    Truncate仅适用于删除全表的记录。


陆续更新中……