程序优化注意的一些点

1.      值得注意的地方

（1）       写好SQL后尽量记得查看一下执行计划，因为不同的环境数据量没法比，所以尽量提高SQL的执行效率。如果程序可能扫描的数据范围大，最好能找个数据量大的环境测试性能。

（2）       执行计划中尽量避免非驱动表全表扫描，尽量让被驱动表走应该走的索引。因为对某个字段可能建立了一个索引或者多个联合索引，可能因为某些原因优化器选择了不恰当的索引，此时可以给SQL加上提示，强制SQL走正确的索引。

（3）       如果大数据表关联多表时且均为相关联小表导致查询慢，可考虑将相关联的小表统一建立成一个物化视图，并定时更新物化视图内容。将物化视图作为查询的驱动表，将将会减少被驱动（小）表与大表的关联代价。

（4）       如果打数据表A和大数据表B关联，且SQL的where过滤条件主要和A表有关、需要筛选出的数据量相对较小，那么有两种办法可提高效率：

1）可以考虑建立一个临时表，先根据where条件筛选A表的数据后，再用B表和A的临时表做关联取数。对于大数据量建议采用临时表，游标是要占用系统资源的，而且记录数目越多占用的资源越多，临时表不全都是存放在硬盘的。当你读取的时候，它是一块（页）的读出来的。这些都是占留内存的。。

2）可以考虑建立一个游标，先根据where条件筛选A表的数据到一个游标，在循环游标里和B表作关联。游标查询出的数据少于3000条时建议用游标。

（5）       一次commit是一次与数据库服务器的交互，如果在游标里循环对一个表进行插入操作，可以设置一个计数器，每插入500条记录时commit提交一次，不过记得循环完了后还要记得commit一下。

（6）       索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。

（7）       有大量重复值、且经常有范围查询（between, >,< >=,< =）和order by、group by发生的列考虑建立群集索引；

（8）       经常同时存取多列且每列都含有重复值可考虑建立组合索引。组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列

2.      查询视图异常缓慢（视图内包含大数据表）

情景：现有的以下查询SQL速度非常慢，例：

select *

  from my_view tb

 where tb.tb_big_id = 123;

视图my_view的创建语句为：

select *

  from tb_big_table    big,

        tb_small_table1small1,

        tb_small_table2small2

 where big.tb_big_id        =small1.tb_big_id

   and small1.tb_small_id1 = small2.tb_small_id1;

思路：将查询视图的SQL的where子句中和大数据表相关的条件放到视图自身的SQL里，如此一来可以缩小大数据表的数据量再和其他表关联。

策略：（1）利用面向对象的方法，且同一个会话Session中可以对同一个变量进行存取值，首先定义一个程序包，包含大数据表的条件的局部变量（需初始化值为不存在的一个值）以及对该变量进行赋值的过程、取值的函数。例：

create or replace packagemy_view_pkg is

  -- 赋值过程

  procedure tb_big_id(pi_id number);

  -- 取值函数

  function tb_big_id return number;

end my_view_pkg;

/

create or replace package body my_view_pkg is

  -- 定义局部变量并设置初始值

  ln_tb_big_id number := -1;

  -- 赋值过程

  procedure tb_big_id(pi_id number) is

  begin

    ln_tb_big_id := tb_big_id;

  endtb_big_id;

  -- 取值函数

  function tb_big_id return number is

  begin

    return tb_big_id;

  endtb_big_id;

end my_view_pkg;

/

（2）修改原有的视图的SQL，用刚建立的包里的取值函数作为条件的一端。列：

create or replace view my_viewas

select *

  from tb_big_table    big,

        tb_small_table1small1,

        tb_small_table2small2

 where big.tb_big_id        =my_view_pkg.tb_big_id

   andbig.tb_big_id        = small1.tb_big_id

   and small1.tb_small_id1 = small2.tb_small_id1;

（3）在查询该视图前，为该视图内引用的变量进行事先赋值。例：

begin

-- 先赋值

  my_view_pkg.tb_big_id = 123;

-- 查询结果和原视图效果一致，效果却大大提升

  select * from my_view;

end;

3.      执行计划不走索引的原因

情景：某个字段上建立了索引，但是在查询的时候却没有走该索引。

原因：A、不走索引大体有以下几个原因：

（1）       你在Instance级别所用的是all_rows的方式。

（2）       你的表的统计信息（最可能的原因），新建的表还没来得及生成统计信息，分析一下就好了。

（1）        

（2）        

（3）       你的表很小，Oracle的优化器认为不值得走索引。

（4）       你要走索引的这列是nullable，虽然这列没有空值。（将字段改为not null）。

（5）       建立组合索引，但查询谓词并未使用组合索引的第一列，此处有一个INDEX SKIP SCAN概念。

（6）       在包含有null值的table列上建立索引，当使用select count(*) from table时不会使用索引。

（7）       在索引列上使用函数时不会使用索引，如SUBSTR，DECODE，INSTR等，对索引列进行运算，需要建立函数索引就可以解决了。例：

select * from emp where to_char(hire_date,'yyyy')='2008' (不使用)

select * from emp where hire_date = to_char('2008','yyyy')(使用)

（8）       当被索引的列进行隐式的类型转换时不会使用索引。例：

select * from t whereindexed_column = 5;

而indexed_column列建立索引但类型是字符型，这时Oracle会产生隐式的类型转换，转换后的语句类似于：

select * from t whereto_number(indexed_column) = 5;

此时不走索引的情况类似于（4）。日期转换也有类似问题，例：

select * from t where trunc(date_col) = trunc(sysdate);

其中date_col为索引列，这样写不会走索引，可改写成

select * from t wheredate_col >= trunc(sysdate)

and date_col < trunc(sysdate+1);

此查询会走索引。

（9）       并不是所有情况使用索引都会加快查询速度，full scan table 有时会更快，尤其是当查询的数据量占整个表的比重较大时，因为full scan table采用的是多块读，当Oracle优化器没有选择使用索引时不要立即强制使用，要充分证明使用索引确实查询更快时再使用强制索引。

（10）    基于cost的成本分析，访问的表过小，使用全表扫描的消耗小于使用索引

（11）    单独的>、<、like ’%abc’ 百分号在前。

（12）    使用<>、not in 、not exist，对于这三种情况大多数情况下认为结果集很大，一般大于5%-15%就不走索引而走FTS。

（13）    B-tree索引 is null不会走，is not null会走。位图索引is null，is not null都会走、联合索引 is not null只要在建立的索引列（不分先后）都会走。

（14）    ‘||’是字符连接函数，像其他函数一样，停用了索引。

（15）    ‘+’是数学函数，像其他数学函数样，停用了索引。

（16）    相同的索引列不能互相比较，这样将会启用全表扫描。

4.      常用提示Hint备忘

（1）       /*+ INDEX*/

/*+ INDEX(TABLE INDEX1, index2) */

/*+ INDEX(tab1.col1 tab2.col2) */

/*+ NO_INDEX */

/*+ NO_INDEX(TABLE INDEX1, index2) */

/*+ INDEX(mmt,index1) INDEX(mtln,index2) */

表明对表选择索引的扫描方法。

第一种不指定索引名是让oracle对表中可用索引比较并选择某个最佳索引；

第二种是指定索引名且可指定多个索引；

第三种是10g开始有的，指定列名，且表名可不用别名；

第四种即全表扫描；

第五种表示禁用某个索引，特别适合于准备删除某个索引前的评估操作。

第六种表示指定多个表的多个索引，9i我是这样操作的，可行。

如果同时使用了INDEX和NO_INDEX则两个提示都会被忽略掉。例如：

SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

（2）   /*+ ORDERED */

FROM子句中默认最后一个表是驱动表，ORDERED将from子句中第一个表作为驱动表. 特别适合于多表连接非常慢时尝试。例如：

SELECT /*+ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

（3）  /*+PARALLEL(table1,DEGREE) */

/*+ NO_PARALLEL(table1) */

该提示会将需要执行全表扫描的查询分成多个部分（并行度）执行，然后在不同的操作系统进程中处理每个部分。该提示还可用于DML语句。如果SQL里还有排序操作，进程数会翻倍，此外还有一个负责组合这些部分的进程，如下面的例子会产生9个进程。 如果在提示中没有指定DEGREE，那么就会使用创建表时的默认值。该提示在默认情况下会使用APPEND提示，NO_PARALLEL是禁止并行操作，否则语句会使用由于定义了并行对象而产生的并行处理。例如：

select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

（4）  /*+ APPEND*/

/*+ NOAPPEND */

直接插入到表的最后，该提示不会检查当前是否有插入操作所需的块空间而是直接添加到新块中，所以可以提高速度。当然也会浪费些空间，因为它不会使用那些做了delete操作的块空间。

NOAPPEND提示则相反，所以会取消PARALLEL提示的默认APPEND提示。例如：

insert /*+ append */ into test1 select * from test4;

insert /*+ parallel(test1)noappend */ into test1 select * from test4