Oracle性能优化23条建议（欢迎补充）

建议1：恰当控制事务大小，commit不要过于频繁。
说明
    1． 要根据具体业务合理控制事务的大小，在需要提交时才提交事务，不要无目的减小事务；
    2． 事务过小，频繁commit会带来以下影响：
        * 程序性能降低，执行时间长，因为需要花费大量时间来等待log file sync事件；
        * 产生的总的redo、undo数量变大；
        * 系统总的栓锁数量变大，造成并发能力减弱；
        * 由于已commit的undo信息可以被覆盖，因此容易造成ORA-01555错误。
    3．事务过大，会带来以下影响：
        * 长时间占有锁，对其他相关事务造成等待；
        * 产生大量undo，造成undo表空间严重扩张或不足。
建议2：在OLTP系统中一定要注意使用绑定变量。
    【示例】
    不建议的写法：
    declare
    type           tcur_ref is ref cursor;
        cur_obj        tcur_ref;
        i_l_start      number    default dbms_utility.get_time;
        str_l_dummy    all_objects.object_name%type;
    begin
        for i in 1..1000
        loop
            open cur_obj for
            'select object_name
              from all_objects
             where object_id = ' || i;    --未使用绑定变量
             fetch cur_obj
    into str_l_dummy;
             close cur_obj;
        end loop;
        dbms_output.put_line(round((dbms_utility.get_time-i_l_start)/100,2)||'seconds...');
    end;
    推荐写法：
    declare
    type           tcur_ref is ref cursor;
        cur_obj        tcur_ref;
        i_l_start      number    default    dbms_utility.get_time;
        str_l_dummy    all_objects.object_name%type;
    begin
        for i in 1..1000
        loop
            open cur_obj for
            'select object_name
               from all_objects
              where object_id = :x' using i;    --使用了绑定变量
              fetch cur_obj
    into str_l_dummy;
              close cur_obj;
        end loop;
        dbms_output.put_line ( round ( ( dbms_utility.get_time-i_l_start ) / 100,2) || 'seconds...');
    end;
    说明
    1．OLTP系统的特点是同一个SQL语句的执行频繁度高，因此，减少分析时间和减少消耗在分析上的资源是非常重要的。不使用绑定变量会引起下面的问题：
        * 严重降低系统的并发能力。不绑定变量会造成过多的硬分析，过多的硬分析会导致共享池的栓锁争用，而过多的栓锁争用会严重降低系统的并发能力。
        * 容易引起ORA-04031错误。
    2． 数据仓库系统的特点是同一SQL语句的执行频繁度低，因此，分析时间和分析所消耗的资源可以忽略，使SQL语句根据实际的数据分布获得一个最佳的执行计划才是最重要的。因此，数据仓库系统中是否绑定变量并不重要，而且由于Oracle Peeking机制的问题，绑定变量可能会引起软分析的SQL语句执行计划不合理的问题。
    
建议3：谨用或尽量不要使用in/exists、not in/not exists方式，而应该使用直接连接方式替换。
    【示例】
    不建议的写法：
    --NOT IN方式写法，假设t_tablename2.column1不返回空值：
    select count(*)
      from t_tablename1
         where column1 not in ( select column1
    from t_tablename2 );

    --NOT EXISTS方式写法：
    select count(*)
          from t_tablename1 a
         where not exists ( select null
    from t_tablename2 b
    where b.column1 = a.column1 );
    推荐写法：
    --直接外连接方式写法：
    select count(*)
      from t_tablename1 a, t_tablename2 b
         where a.column1 = b.column1(+)
    and b.column1 is null;
    【示例】
    如下代码不符合规范：
    --IN方式写法：
    select count(*)
      from t_tablename1
     where column1 in ( select column1
    from t_tablename2 );

    --EXISTS方式写法：
    select count(*)
          from t_tablename1 a
         where exists ( select null
    from t_tablename2 b
    where a.column1 = b.column1 );
    应编写如下：
    --直接连接方式写法：
    select count(*)
      from t_tablename1 a, t_tablename2 b
     where a.column1 = b.column1;
    说明
    使用in/exists/not in/not exists方式，要求开发人员必须正确理解其处理机制，并且需要对语句进行细致的测试和优化工作，如果不假思索的拿来使用往往会造成严重的性能问题。因此，我们通常建议使用直接连接方式进行替换。
    
建议4：嵌套循环连接中非常大的表作内表（被驱动表）时，其连接字段上需建有索引。
    说明
    嵌套循环连接在内表较大时，如果其连接字段无索引，那么每次循环都会对内表进行全表扫描，这样会产生大量的逻辑I/O，语句性能严重下降，在内表连接字段建立索引的目的是为了减少逻辑I/O。
    
建议5：在返回的结果集大于1万行时，不建议使用嵌套循环连接，应考虑使用哈希连接或排序合并连接进行替换。
    说明
    返回大量结果集时嵌套循环连接的成本较高、效率很低，此时应考虑变换连接方式，特别在使用RBO优化器时，嵌套循环连接的优先级较高，通常需要用“提示”来强制改变连接方式。

建议6：两个无序大表的连接，在连接字段无索引时，不建议使用合并排序连接。
* 说明
    排序合并连接需要先对两个表按连接字段进行排序，如果两个表都非常大，其排序的操作的代价会非常巨大。

建议7：含有多表的SQL语句，必须明确指定各表的连接条件，以避免产生迪卡尔积。
    【示例】
    不建议的写法：
        --没有明确t_tablename3表的连接条件
    select a.column2, b.column2, c.column2
          from t_tablename1 a, t_tablename2 b, t_tablename3 c
    where a.column1 = b.column1
           and c.column1 <> value;
    推荐写法：
        --明确所有表的连接条件
    select a.column2, b.column2, c.column2
          from t_tablename1 a, t_tablename2 b, t_tablename3 c
    where a.column1 = b.column1
    and b.column1 = c.column1
           and c.column1 <> value;
建议8：在执行频度高，又含有排序操作的SQL语句中，建议使用索引来避免排序操作。
    说明
    排序是一种昂贵的操作，在一秒钟执行成千上万次的SQL语句中如果带有排序动作，往往会消耗大量的系统资源、性能低下。索引是一种有序结构，如果order by后面的字段上建有索引，那么Oracle通常会直接使用索引而避免排序。

建议9：在删除（delete）、插入（insert）、更新（update）频繁的表中，建议不要使用位图索引。
    说明
    在数据增、删、改频繁的表中建立位图索引，会导致位图索引越来越大，效率越来越低，除非定期重建位图索引，否则反而会因此降低性能。另外，如果在此类表中建立位图索引会产生严重锁等待和阻塞，原因是位图索引的一个位被更新会锁住表中N行数据。

建议10：在删除（delete）、插入（insert）、更新（update）操作频繁，而数据又来源于序列的字段上建议尽量不要建立索引。
    说明
    例如，表T的C1字段是一个序列值，对T的delete操作十分频繁，而且每次插入、更新C1字段的值都来源于一个序列，插入、更新的值和删除的值永远都不可能重复。如果，在C1字段上建立一个索引，这将导致该索引越来越大、效率越来越低，该索引的空间很难被重用，除非重建索引。
    
建议11：尽量使用前端匹配的模糊查询（like），以通过使用索引来避免全表扫描。
    说明
    例如，column1 like ‘ABC%’方式，可以对column1字段进行索引范围扫描；而column1 like ‘%ABC%’方式，即使column1字段上存在索引，也无法使用该索引，只能走全表扫描。

建议12：在建立复合索引时，尽量把最常用的字段放在最前面，尽量把离散值较少的字段往前放，同时还要尽量保证复合索引中至少有一个字段具有非空约束。
    说明
    例如，要在C1和C2两个字段上建立复合索引，如果C1字段使用更频繁一些，那么复合索引的顺序应该是（C1,C2），把最常用字段作为复合索引的第一个字段，可以使Oracle更容易用上该索引。如果C1字段只有（1,2,3）三个离散值，C2字段具有成千上万个离散值，则复合索引的顺序应该为（C1,C2），这样做目的是如果在SQL语句的条件中没有用上C1字段时，使Oracle可以走跳跃式索引扫描，而避免走全表扫描。

建议13：不要在选择性较低的字段上建立索引。
    说明
    在选择性较低的字段使用索引，不但不会降低逻辑I/O，相反往往会增加大量逻辑I/O降低性能。除非SQL语句本身具备使用快速全索引扫描的特点，因此，通常不建议在选择性较低的字段上建立索引。
    
建议14：尽量不要在较长字符串的字段上建立索引，如char(1000)、varchar2(1000)等。
    说明
    这会导致索引非常庞大，索引高度过高，效率很低。
    
建议15：不要在语句的where条件中对字段进行数学表达式运算。
    【示例】
    不建议的写法：
    select column1, column2
          from t_tablename
    where column1/3 < 3000;
    推荐写法：
    select column1, column2
          from t_tablename
    where column1 < 1000;
    说明
    在where条件中的字段，如果参与数学表达式运算会导致无法使用索引，通常不推荐这样的写法，当然，除非特意使用该写法来屏蔽索引。
    
建议16：在where条件中对字段使用函数，会导致用不上索引，可以考虑建立函数索引。
    说明
    在Oracle9i中想让Oracle自动使用函数索引，必须使用CBO优化器，因此就需要对表进行“分析”。但是，如果优化器模式为CHOOSE，在没有分析的情况下可以通过“提示”来强制使用函数索引。
    
建议17：在业务逻辑允许的情况下，尽量使用union all来代替union。
    【示例】
    不建议的写法：
    --如果上下两个SQL返回的结果集没有重复数据
    select column1
          from t_tablename1
    union
    select column1
      from t_tablename2;
    推荐写法：
    --如果上下两个SQL返回的结果集没有重复数据
    select column1
          from t_tablename1
    union all
    select column1
      from t_tablename2;
      
建议18：如果SQL语句针对同一个字段做多个选择（or）条件，建议考虑使用no_expand提示来避免串联（concatenation）操作。
    【示例】
    不建议的写法：
    select column1, column2
    from t_tablename
    where column3 = value1
    or column3 = value2
    or column3 = value3
    or column3 = value4;

    --或写成：
    select column1, column2
    from t_tablename
    where column3 in ( value1, value2, value3, value4 );
    推荐写法：
    select /*+ no_expand */ column1, column2
    from t_tablename
    where column3 = value1
    or column3 = value2
    or column3 = value3
    or column3 = value4;

    --或写成：
    select /*+ no_expand */ column1, column2
    from t_tablename
    where column3 in ( value1, value2, value3, value4 );
    说明
    在RBO模式下，这种情况Oracle只会走“串联”操作，串联会造成多次索引扫描，通常效率较低；使用no_expand提示或直接用index提示来绑定索引，Oracle会转向使用IN-List迭代操作，这样可以有效避免索引的多次访问，提高效率。但有时情况可能恰好相反，例如多次的索引范围扫描或唯一扫描，换成IN-List之后可能会走“全索引扫描”效率反而下降。

建议19：用case语句合并多次表扫描
    【示例】
    不建议的写法：
    select count(*) from t_tablename where column1 < 1000;
    select count(*) from t_tablename where column1 between 1000 and 5000;
    select count(*) from t_tablename where column1 > 5000;
    推荐写法：
    select count ( case when column1 < 1000 then 1 else null end ),
    count ( case when column1 between 1000 and 5000 then 1 else null end),
           count ( case when column1 > 5000 then 1 else null end)
    from t_tablename;
    
建议20：慎用distinct、union、order by、group by语句，因为这些语句会引起排序，在无法消除排序的情况下尽量不要使用。

建议21：当使用索引产生的逻辑I/O远远大于表扫描时，应该屏蔽索引，如果字段为数值型的就在表达式后面“+ 0”，为字符型的就并上一个“空串”。
    【示例】
        --数值型屏蔽索引
    select column
          from t_tablename
         where column_num + 0 > 30;

        --字符型屏蔽索引
        select column
          from t_tablename
         where column_char || '' = 'ABC';
        
建议22：删除分区表数据时，建议考虑使用truncate进行整分区的删除。

建议23：对于大批量数据的删除，要考虑使用特殊方式处理，不要使用delete方式。
    说明
    delete不会释放高水位（HWM），一来造成空间的浪费，二来会导致效率的下降，另外delete方式删除大批量数据的效率也是极差的。可以考虑使用truncate、create table as等方法。