oracle sql 优化

前一段时间学习sql优化，看了网上的一些帖子，有一些心得。

1。TOAD中间看执行计划

先在相应的用户空间中建一个toad_plan_table的表，脚本如下：

CREATE TABLE TOAD_PLAN_TABLE (
  STATEMENT_ID     VARCHAR2 (32),
  TIMESTAMP        DATE,
  REMARKS          VARCHAR2 (80),
  OPERATION        VARCHAR2 (30),
  OPTIONS          VARCHAR2 (30),
  OBJECT_NODE      VARCHAR2 (128),
  OBJECT_OWNER     VARCHAR2 (30),
  OBJECT_NAME      VARCHAR2 (30),
  OBJECT_INSTANCE  NUMBER,
  OBJECT_TYPE      VARCHAR2 (30),
  SEARCH_COLUMNS   NUMBER,
  ID               NUMBER,
  COST             NUMBER,
  PARENT_ID        NUMBER,
  POSITION         NUMBER,
  CARDINALITY      NUMBER,
  OPTIMIZER        VARCHAR2 (255),
  BYTES            NUMBER,
  OTHER_TAG        VARCHAR2 (255),
  OTHER            LONG,
  PARTITION        NUMBER,
  PARTITION_START  VARCHAR2 (255),
  PARTITION_STOP   VARCHAR2 (255),
  DISTRIBUTION     VARCHAR2 (30) ) ; 

2. 优化

由于使用的是10g，oracle默认使用CBO，所以很多时候表连接使用hash，而表上使用full scan，所以建索引和指明hints都是很必要的，不过CBO的智能还不错，有时候它的执行路径还是比较好的。除了写sql语句常用的一些技巧外，指定hints能有目的的优化sql语句的执行，达到很好的效果。我有一个语句如果不指明hints，oracle执行不了，会提示temp空间不够，指明hints以后可以很快得到结果。

指明hints语法很简单，直接加在select 后面就可以了，比如：select /*+index(a,ind_a) use_nl(a b)*/ * from a,b where a.col=b.col;第一个+不能少，而且后面没有空格，多个hints中间至少有一个空格。

常见的hints

a. 指明使用哪个index

使用CBO的时候，系统经常不使用index，明明你建了索引，它就是不用（可以看执行计划，full scan的都是红色的） ，这个时候显式地指明使用哪个index就是最好的选择，一般而言使用index可以大大地优化sql（特殊情况下，有时候full scan比使用index更好，是个牛DBA讲的，而且还有例子，我在优化中也碰到过。优化的终极目的是让系统在完成任务的前提下尽可能少地访问硬盘次数）。

b.指明表连接的方式

10g中有三种连接方式:merge,nested loop,hash,指明连接方式能有效地影响查询的效率，设置不当也可能使你的sql慢如蜗牛。使用的方法就是use_merge(a b),use_nl(a b),use_hash(a b),也可以设置多个表，前者称为驱动表，后者称为被探测表

merge：将a,b两个表的数据排序，然后连接。最好用于非等值连接，由于要排序，所以如果a，b中有一个表数据很多的时候，就比较费时间了。

nested loop:等值连接的话，多数情况下nl是最快的，但nl对驱动表的选择很有讲究，一定要使用记录少的表做为驱动表，这里的记录少，是说做了where判断以后过滤以后的记录。否则效率会很差。如果有多重nl，就要注意表的连接顺序了。

hash：一般也是在等值连接的时候用，hash连接比较碰运气，一般情况下不如nl，但如果nl多重，这个时候可能会导致性能下降，使用hash没有多重带来的影响。

总的来说，cbo对表的连接顺序的有优化我觉得还是不错的，可以看看执行计划，判断一下那个地方容易出问题，需要提高效率。多试几次就知道哪个好了。

c. ordered

我看有人说要使用ordered强制系统使用自己sql语句中的表顺序连接，以便于自己确定，但我自己的使用中觉得一般情况下CBO的执行顺序就很好了，用ordered反而使得优化变得复杂。

最后说一点就是有些自己想要的执行计划，无论你怎么指定就是达不到，所以有时候不要太勉强。