关于hint

fusnow · 发表于 2010-1-30 15:54

前几天看看坛子上有人问关于hint的问题，加了hint到底能不能保证执行计划？应不应该用hint？等等。

所以我想写一点关于Hint的小介绍，好久不来itpub了，算抛块砖吧，希望把玉引出来。。。。。。也可以拍砖。

oracle的hint是人人知道的啦，那加了hint到底能不能保证执行计划？我的看法是能保证，但前提是你用的对。
到底应不应该用？这个要看具体情况，看是什么样的系统，当然我在自己做测试的时候用Hint是用的很多的。

下面关于Hint浅议几个话题：

1，首先Hint是怎么工作的。

我先跑个简单的SQL，
alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';

select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id
and d.department_name='IT';

这个是执行计划：

----------------------------------------------------------+-----------------------------------+
| Id  | Operation                   | Name          | Rows  | Bytes | Cost  | Time    |
----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT             |                |    |    |    3 |          |
| 1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES       | 10 | 110 |    1 |  00:00:01 |
| 2 | NESTED LOOPS                |                | 10 | 270 |    3 |  00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |    1 | 16 |    2 |  00:00:01 |
| 4 |    INDEX RANGE SCAN          | DEPART_NAME_IND  |    1 |    |    1 |  00:00:01 |
| 5 | INDEX RANGE SCAN          | EMP_DEPARTMENT_IX| 10 |    |    0 |          |
----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."DEPARTMENT_NAME"=:SYS_B_0)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

然后看它的10053 trace，从trace里面可以清楚的看到在访问departments这个表的问题上，oracle对比了全表扫描和索引range scan,
最后觉得使用索引DEPART_NAME_IND是最好的。

SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): DEPARTMENT_NAME(VARCHAR2)
AvgLen: 12.00 NDV: 27 Nulls: 0 Density: 0.037037
  Table: DEPARTMENTS  Alias: D
Card: Original: 27  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  -----------------------------------------
  END Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
Cost:  3.01  Resp: 3.01  Degree: 0
   Cost_io: 3.00  Cost_cpu: 41547
   Resp_io: 3.00  Resp_cpu: 41547
  Access Path: index (AllEqRange)
Index: DEPART_NAME_IND
resc_io: 2.00  resc_cpu: 14613
ix_sel: 0.037037  ix_sel_with_filters: 0.037037
Cost: 2.00  Resp: 2.00  Degree: 1
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (AllEqRange)
Index: DEPART_NAME_IND
resc_io: 1.00  resc_cpu: 7321
ix_sel: 0.037037  ix_sel_with_filters: 0.037037
Cost: 1.00  Resp: 1.00  Degree: 0
  ****** finished trying bitmap/domain indexes ******
  Access Path: index (FullScan)
Index: DEPT_ID_PK
resc_io: 1.00  resc_cpu: 12521
ix_sel: 1  ix_sel_with_filters: 1
Cost: 1.00  Resp: 1.00  Degree: 0
******** Cost index join ********
Index join: Considering index join to index DEPART_NAME_IND
Index join: Joining index DEPT_ID_PK
Ix HA Join
  Outer table:
resc: 1.00  card 1.00  bytes: 22  deg: 1  resp: 1.00
  Inner table: <no name>
resc: 1.25  card: 27.00  bytes: 14  deg: 1  resp: 1.25
using dmeth: 2  #groups: 1
Cost per ptn: 0.50  #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 2.75  Resp: 2.75  [multiMatchCost=0.00]
******** Index join cost ********
Cost: 2.75
******** End index join costing ********
  Best:: AccessPath: IndexRange  Index: DEPART_NAME_IND
      Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0


好，现在我想走departments的全表扫描，很简单，加个full(d)的Hint：

alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';

select /*+ full(d) */ e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id
and d.department_name='IT';

执行计划改成了全表扫描：
============
Plan Table
============
--------------------------------------------------------+-----------------------------------+
| Id  | Operation                   | Name          | Rows  | Bytes | Cost  | Time    |
--------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT          |                |    |    |    4 |          |
| 1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES       | 10 | 110 |    1 |  00:00:01 |
| 2 | NESTED LOOPS             |                | 10 | 270 |    4 |  00:00:01 |
| 3 | TABLE ACCESS FULL       | DEPARTMENTS    |    1 | 16 |    3 |  00:00:01 |
| 4 | INDEX RANGE SCAN       | EMP_DEPARTMENT_IX| 10 |    |    0 |          |
--------------------------------------------------------+-----------------------------------+

然后我们看看10053 trace，我们会看到这时候trace里只能看到关于TableScan的cost计算：
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): DEPARTMENT_NAME(VARCHAR2)
AvgLen: 12.00 NDV: 27 Nulls: 0 Density: 0.037037
  Table: DEPARTMENTS  Alias: D
Card: Original: 27  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  -----------------------------------------
  END Single Table Cardinality Estimation
  -----------------------------------------
Access Path: TableScan
Cost:  3.01  Resp: 3.01  Degree: 0
   Cost_io: 3.00  Cost_cpu: 41547
   Resp_io: 3.00  Resp_cpu: 41547
Best:: AccessPath: TableScan
      Cost: 3.01  Degree: 1  Resp: 3.01  Card: 1.00  Bytes: 0

默认情况下，只要一个SQL的所有的可能的access path和join path不是特别多，oracle的query optimizer都会考虑所有的可能的计划，进行比较，得出最佳方案。这是query optimizer的基本功能之一，我们都知道。。。
而hint的作用就是要求query optimizer在考虑的过程中只考虑某些access或Join path，或者排除某些access或Join path。所以简单的说，HINT的工作方式就是给oracle加限制。

（只不过有些Hint对oracle加的限制不像限制它智能full table scan这样的直观，比如修改参数用的OPT_PARAM，修改统计信息估计的CARDINALITY，控制是把子查询数据放在内存还是放在临时空间的MATERIALIZE，等等，不过都是给oracle加人为的限制。）

这种工作模式在我看来还是比较科学的，只要是合理的使用了hint, 应该是可以保证执行计划按照我们想要的方式走的。
（但是要想合理的使用Hint还是需要点练习的

)

到这里，让我们考虑一下一个经常被讨论的问题：到底该不该在系统里使用Hint？
我个人的观点是，把hint作为最后的手段，只要我能通过其他方式把问题解决，一般我不会考虑使用Hint。（这里的其他方式比如改表的结构，改sql，改统计信息收集的策略。。。），

对我来说，短期来说Hint也许是个解决问题的最快方案，但它从长远来看会引入风险。
因为我没法保证以后数据分布情况不会变化，
我也没法保证以后表结构不会变化，
我也没法保证以后数据库不会升级。
而以上这些情况都会导致加了Hint的SQL性能变差，而每一种这种变差，都有可能变成业务级别的故障。
（遥想当年，我也是在很多系统加了很多hint滴，为这些系统默哀一下。。。）

但这个问题是因系统而异的，比如在线事务系统一般是很少用Hint的，因为SQL一般都比较简单，oracle自己就可以处理的很好。
但是数据仓库类型的系统可能用hint就会都一些，我以前在ETL的语句里就加过不少，原因很简单，这里的表好多好大，SQL都好长好复杂，优化好难好挑战，我加了Hint可能会为将来引入风险，但是我不加的话估计连现在都保不住。

从心理的角度分析，我觉得（我就是觉得哈，我不是心理专家)，
1）倾向于加hint的人在精神深层次里存在一种对oracle的不信任，
2）不倾向于加hint的人在精神深层次里存在一种对oracle的信任，
（我本人就经历过从最开始的对oracle的盲目信任，到对oracle的盲目不信任，再到对oracle的针对性不信任，再到对oracle的针对性信任。。。）
我想信任也好，不信任也好，不过是个个人风格问题，当一个人在这个工作里混了5，6，7，8年以后，总会对oracle方面的工作或多或少产生点方法论，世界观之类的东西，
有的人信任oracle多点，有的人不信任多点，这纯粹是个风格问题，无所谓对错。回到用不用Hint这个话题，这里也存在一个风格的问题。
不喜欢加Hint的人，像我，就会找一堆不加的好处。喜欢加hint的人可能就会找出一堆加的好处。

这就像你去做领导，
1）有的领导是喜欢给team members定好一个完整的流程，什么都要严格的按流程来，对做事的每一个细节都要监控到。
2）有的领导是喜欢定个high level的目标和规定，然后让team member放手去做。
这也纯粹是个公司的或个人的风格问题.

对oracle的管理也像治家，治公司，治国，风格或有不同，实在说不上什么是一定好，什么是一定不好的。每个人都可以形成自己的方法论，每个人到了一定阶段都会形成自己的方法论，因人而因，因地制宜，运用之道，存乎一心。

[ 本帖最后由 fusnow 于 2010-2-5 09:53 编辑 ]

fusnow · 发表于 2010-1-30 16:18

继续，
前面看到hint的工作方式还是很科学的，但平时自己加的Hint总是不起作用，这一般都是hint用错了。

例如这个例子：

select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id
and d.department_name='IT';

Here is the plan:
----------------------------------------------------------+-----------------------------------+
| Id  | Operation                   | Name          | Rows  | Bytes | Cost  | Time    |
----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT             |                |    |    |    3 |          |
| 1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES       | 10 | 110 |    1 |  00:00:01 |
| 2 | NESTED LOOPS                |                | 10 | 270 |    3 |  00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |    1 | 16 |    2 |  00:00:01 |
| 4 | INDEX RANGE SCAN          | DEPART_NAME_IND  |    1 |    |    1 |  00:00:01 |
| 5 | INDEX RANGE SCAN          | EMP_DEPARTMENT_IX| 10 |    |    0 |          |
----------------------------------------------------------+-----------------------------------+

对于DEPART_NAME_IND这个索引, 如果我不想让它走range scan，让它走fast full index scan，行不行？
那我试着加INDEX_FFS这个Hint：
select /*+ INDEX_FFS(D ("DEPARTMENTS"."DEPARTMENT_NAME")) */ e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id
and d.department_name='IT';

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                | 10 | 270 |    3 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID  | EMPLOYEES       | 10 | 110 |    1 (0)| 00:00:01 |
| 2 | NESTED LOOPS             |                | 10 | 270 |    3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS    |    1 | 16 |    2 (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN       | DEPART_NAME_IND   |    1 |    |    1 (0)| 00:00:01 |
|*  5 | INDEX RANGE SCAN          | EMP_DEPARTMENT_IX | 10 |    |    0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

结果没有作用，按理说这么简单的语句加Hint是很少不生效的，问题就在于对fast full index scan的理解，
fast full index scan这种access path只能用在当你能够从index上取得所有需要的信息，而不需要访问表的时候，而这里最后很定是
需要访问DEPARTMENTS的，所以oracle优化器在考虑所有可能的执行计划的时候，完全就不会考虑到fast full index scan。
就好比oracle考虑的是a, b, c, d，而你要求的是e, 这样的Hint是不会生效的，你只能在a, b, c, d里面选。

当然如果我们建个复合索引，fast full index scan就没问题了。
SQL> create index depart_name_id_ind on departments(department_id, department_name);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'DEPARTMENTS');

PL/SQL 过程已成功完成。

select /*+ INDEX_FFS(D ("DEPARTMENTS"."DEPARTMENT_ID" "DEPARTMENTS"."DEPARTMENT_NAME")) */ e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id
and d.department_name='IT';

执行计划
----------------------------------------------------------
Plan hash value: 3172774359

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                   | 10 | 270 |    3 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES       | 10 | 110 |    1 (0)| 00:00:01 |
| 2 | NESTED LOOPS             |                   | 10 | 270 |    3 (0)| 00:00:01 |
|*  3 | INDEX FAST FULL SCAN    | DEPART_NAME_ID_IND |    1 | 16 |    2 (0)| 00:00:01 |
|*  4 | INDEX RANGE SCAN       | EMP_DEPARTMENT_IX  | 10 |    |    0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

[ 本帖最后由 fusnow 于 2010-2-5 09:54 编辑 ]

fusnow · 发表于 2010-1-30 18:38

前面的例子说明使用hint需要多执行计划有一定的了解，但其实很多时候Hint不生效都是因为一些很简单的原因：用错了语法或者用错的地方：
hint的基本使用方法可以在oracle的sql reference里面看到：
http://download.oracle.com/docs/ ... ments006.htm#i35922
这里也有oracle documented hint的列表，是个不错的参考的地方。

PS: 如果要看undocumented的oracle hint，这里还不错：
http://www.psoug.org/reference/hints.html

比较容易让人忽视的一点是要留意Hint的作用域，Hint一般都是在它所在的query block生效，所以如下的Hint是无效的：
select /*+ full(d) */ * from employees e where department_id in
(select department_id from departments d where d.department_name='IT');

正确的用法是：
在departments所在的query block加hint
select * from employees e where department_id in
(select /*+ full(d) */ department_id from departments d where d.department_name='IT');

或者定义query block的名字，然后再外围query block使用query block的名字。
select /*+ full(@qb1 d) */ * from employees e where department_id in
(select /*+ qb_name (qb1) */ department_id from departments d where d.department_name='IT');

[ 本帖最后由 fusnow 于 2010-1-30 18:48 编辑 ]

ZALBB · 发表于 2010-1-31 00:45

我有时也用HINT，虽不能保证得到最优的执行计划，但比起不用，用HINT的效率大大提高了．

主要是依据系统的设计，在使用前，我征求了开发人员的意见，得知道某个查询必须用上一个约束，
比如：查询时间段．通常我是指定从此约束条件开始执行．

ZALBB · 发表于 2010-1-31 00:47

原帖由 fusnow 于 2010-1-30 18:38 发表
前面的例子说明使用hint需要多执行计划有一定的了解，但其实很多时候Hint不生效都是因为一些很简单的原因：用错了语法或者用错的地方：
hint的基本使用方法可以在oracle的sql reference里面看到：
http://download.oracle.com/docs/ ... ments006.htm#i35922
这里也有oracle documented hint的列表，是个不错的参考的地方。

PS: 如果要看undocumented的oracle hint，这里还不错：
http://www.psoug.org/reference/hints.html

比较容易让人忽视的一点是要留意Hint的作用域，Hint一般都是在它所在的query block生效，所以如下的Hint是无效的：
select /*+ full(d) */ * from employees e where department_id in
(select department_id from departments d where d.department_name='IT');

正确的用法是：
在departments所在的query block加hint
select * from employees e where department_id in
(select /*+ full(d) */ department_id from departments d where d.department_name='IT');

或者定义query block的名字，然后再外围query block使用query block的名字。
select /*+ full(@qb1 d) */ * from employees e where department_id in
(select /*+ qb_name (qb1) */ department_id from departments d where d.department_name='IT');

之前觉得不方便对匿名块使用HINT．看到这个，感觉ORACLE确实在不断完善其功能．

sundog315 · 发表于 2010-1-31 08:14

顶，好！！！
这个帖子可以很完美的回答前阵子的针对Hint的问题了。

个人倾向于能不使用Hint便不使用，毕竟如楼主所言，数据在发生变化。而我们这边碰到最多的情况是因为升级Oracle版本而导致Hint不合理。

ZALBB · 发表于 2010-1-31 10:44

原帖由 sundog315 于 2010-1-31 08:14 发表
顶，好！！！
这个帖子可以很完美的回答前阵子的针对Hint的问题了。

个人倾向于能不使用Hint便不使用，毕竟如楼主所言，数据在发生变化。而我们这边碰到最多的情况是因为升级Oracle版本而导致Hint不合理。

升级后，ORACLE的优化器变得更聪明，这时其很可能找到更佳的执行计划，所以觉得原HINT不合理．

fusnow · 发表于 2010-1-31 12:06

谢谢楼上的几位捧场子

我继续：

介绍一些次常用的hint，
我们都熟悉那些常用的Hint，
比如用来调整access path的（如full, index, parallel...)，
调整join order的（如order, leading...),
调整join path的（use_nl, use_hash...)
调整remote access的（如driving_site)，
调整查询转化的（如rewrite, unnest, merge),

这些都是比较常用的，说起来没什么意思，现在说几个"次常用"的hint.

1）dynamic_sampling,
这个Hint在sql reference里面有介绍：
The DYNAMIC_SAMPLING hint instructs the optimizer how to control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes.

drop table t1;
drop table t2;
create table t1 (x int, y int);
create index t1_xy on t1(x,y);

create table t2 (x int);
create index t2_x on t2(x);

insert into t1 select mod(rownum, 10), mod(rownum, 10) from dba_objects where rownum<=50000;
insert into t2 select mod(rownum, 50) from dba_objects where rownum<=50000;

commit;

exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');

SQL> set autotrace traceonly exp
SQL> select t1.x, t2.x from t1,t2
  2  where t1.x=1 and t1.y=1 and t2.x=t1.x;

执行计划
----------------------------------------------------------
Plan hash value: 4152676900

---------------------------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 501K|  3428K| 32  (29)| 00:00:01 |
|*  1 |  HASH JOIN       |    | 501K|  3428K| 32  (29)| 00:00:01 |
|*  2 | INDEX RANGE SCAN| T1_XY | 496 |  2480 | 19 (0)| 00:00:01 |
|*  3 | INDEX RANGE SCAN| T2_X  |  1011 |  2022 |    4 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T2"."X"="T1"."X")
2 - access("T1"."X"=1 AND "T1"."Y"=1)
3 - access("T2"."X"=1)

SQL> select /*+ dynamic_sampling(4) */ t1.x, t2.x from t1,t2
       where t1.x=1 and t1.y=1 and t2.x=t1.x;

执行计划
----------------------------------------------------------
Plan hash value: 224376697

---------------------------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |  5015K| 33M| 110  (80)| 00:00:02 |
|*  1 |  HASH JOIN       |    |  5015K| 33M| 110  (80)| 00:00:02 |
|*  2 | INDEX RANGE SCAN| T2_X  |  1011 |  2022 |    4 (0)| 00:00:01 |
|*  3 | INDEX RANGE SCAN| T1_XY |  4963 | 24815 | 19 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T2"."X"="T1"."X")
2 - access("T2"."X"=1)
3 - access("T1"."X"=1 AND "T1"."Y"=1)

Note
-----
- dynamic sampling used for this statement

那个执行计划更好一些？应该是第二个，

这里的第一个执行计划把t1作为驱动表，t2作为被驱动表，因为oracle认为t1.x=1 and t1.y=1应该返回更少的行数。
oracle在判断t1.x=1 and t1.y=1的总行数 => （t1.x=1的选择率 * t1.y=1的选择率）* t1行数 => （0.1 * 0.1）* 50000 = 500 （计划里是496）
oracle在判断t2.x=1的总行数 => t2.x=1的选择率 * t2行数 => 0.02 * 50000 = 1000.

所以oracle把t1作为驱动表是可以理解的，但问题是实际的数据分布t1.x总是等于t1.y，所以真实的数据t1.x=1 and t1.y=1的总行数 = t1.x=1的选择率 * t1行数 = 0.1 * 50000 = 5000 （计划里的4963) !

为了让oracle认识到这一点，我们使用level 4的dynamic_sampling, dynamic_sampling level 4的意思是：

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all
tables that have single-table predicates that reference 2 or more columns. The
number of blocks sampled is the default number of dynamic sampling blocks. For
unanalyzed tables, the number of blocks sampled is two times the default number
of dynamic sampling blocks

从level 4开始oracle开始对“tables that have single-table predicates that reference 2 or more columns” 这种情况开始采样，所以能够意识到真实的数据分布是什么样，所以我们
看到第二个是以t2为驱动表的。

这种类似的问题在复杂的SQL里面会很常见。

但是dynamic_sampling这个Hint会在oracle解析的时候产生额外的开销，我很少真的在代码里用，不过我经常在调优的时候用。
比如当我运行完这个SQL语句之后：
select /*+ dynamic_sampling(4) */ t1.x, t2.x from t1,t2
       where t1.x=1 and t1.y=1 and t2.x=t1.x;

我会马上跟一个：
select * from table(dbms_xplan.display_cursor(null, 0 , 'advanced'));

这样就可以得到为了实现这个比较好的计划所需要的所有的Hint，你如果要在系统里加hint, 动态采样+dbms_xplan.display_cursor是个捷径:

  /*+
BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."X"))
   INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."X" "T1"."Y"))
   LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
   USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
  */

在真实使用的时候你必须把outline相关的和IGNORE_OPTIM_EMBEDDED_HINTS这个奇怪的Hint去掉，
也可以把OPTIMIZER_FEATURES_ENABLE('10.2.0.1')，ALL_ROWS去掉，因为这些环境不大会改变。

然后可以把oracle自定义的query block去掉，如果你的sql有多个query block的话可能需要自己定义query block的名字（见我前面的帖子）

我这个SQL最后留下这么几个hint就够了：
/*+
   INDEX("T2" ("T2"."X"))
   INDEX("T1" ("T1"."X" "T1"."Y"))
   LEADING( "T2" "T1")
   USE_HASH("T1")
  */

还有一个问题，就是有的SQL一跑就是几天，甚至会影响整个系统，你优化的时候不可能把它跑一遍，所以也不能用dbms_xplan.display_cursor，那你可以考虑这个方法。

alter session set events '10132 trace name context forever, level 1';
这里使用10053效果也类似，但我发现10053只有在hard parse的时候才生成trace，所以一般用10053之前可能需要flush shared_pool。

set autotrace traceonly exp
select /*+ dynamic_sampling(4) */ t1.x, t2.x from t1,t2
       where t1.x=1 and t1.y=1 and t2.x=t1.x;

于是在trace里你可以看到下面的内容：
sql_id=62dhqxdq49t6a.
Current SQL statement for this session:
EXPLAIN PLAN SET STATEMENT_ID='PLUS1777' FOR select /*+ dynamic_sampling(4) */ t1.x, t2.x from t1,t2
       where t1.x=1 and t1.y=1 and t2.x=t1.x

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation       | Name | Rows  | Bytes | Cost  | Time    |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT |       |    |    | 110 |          |
| 1 |  HASH JOIN       |       | 4897K | 33M | 110 |  00:00:02 |
| 2 | INDEX RANGE SCAN | T2_X |  1011 |  2022 |    4 |  00:00:01 |
| 3 | INDEX RANGE SCAN | T1_XY |  4963 | 24K | 19 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T2"."X"="T1"."X")
2 - access("T2"."X"=1)
3 - access("T1"."X"=1 AND "T1"."Y"=1)

Content of other_xml column
===========================
  db_version    : 10.2.0.1
  parse_schema : HR
  dynamic_sampling: yes
  plan_hash    : 224376697
  Outline Data:
  /*+
BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."X"))
   INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."X" "T1"."Y"))
   LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
   USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
  */

[ 本帖最后由 fusnow 于 2010-2-5 09:54 编辑 ]

viadeazhu · 发表于 2010-1-31 12:11

随着CBO越来越完善，我也觉得尽量不使用hint为好。
hint最好只用于几个别CBO不够聪明的情况。

fusnow · 发表于 2010-1-31 12:39

趁周末多写点东西，
继续
关于这个Hint: swap_join_inputs,
这是一个undocumented Hint,在官方文档里面看不到，它的作用是帮我们调整join的顺序。
一般调整Join的顺序用leading或者order就可以了，但是看看下面的4表连接例子：

select e.last_name, d.department_name, j.job_title
from employees e, departments d, jobs j, locations l
where e.department_id=d.department_id
and e.job_id = j.job_id
and d.location_id=l.location_id
and j.job_title='Programmer'
and l.city='Beijing'
and d.department_name='IT'
;

----------------------------------------------------------
Plan hash value: 3164695383

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                |    1 | 78 |    5 (0)| 00:00:01 |
| 1 |  NESTED LOOPS                |                |    1 | 78 |    5 (0)| 00:00:01 |
| 2 | NESTED LOOPS                |                | 10 | 510 |    4 (0)| 00:00:01 |
| 3 | NESTED LOOPS             |                |    1 | 31 |    3 (0)| 00:00:01 |
| 4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS    |    1 | 19 |    2 (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN       | DEPART_NAME_IND |    1 |    |    1 (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| LOCATIONS       |    1 | 12 |    1 (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN       | LOC_CITY_IX    |    1 |    |    0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES       | 10 | 200 |    1 (0)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX | 10 |    |    0 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID  | JOBS             |    1 | 27 |    1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN          | JOB_ID_PK       |    1 |    |    0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

这里的连接顺序是
1） departments先和location连，departments作为驱动表。
2）上一步的结果集和employees连，上一步的结果集作为驱动表。
3）上一步的结果集和JOBS连，上一步的结果集作为驱动表。

这里有oracle的一个规则，就是如果前面一步的Join的结果在和其他表做连接，那么默认情况下总是把前面一步的Join的结果作为驱动者，

CBO本身一般不会基于cost的考虑来打破这个规则，所以如果我想要的连接顺序变成下面这样，似乎有点麻烦，因为leading 和order这样的Hint是做不到的：
1） departments先和location连，departments作为驱动表。
2）上一步的结果集和employees连， employees作为驱动表。
3）上一步的结果集和JOBS连， JOBS作为驱动表。

你可以试一下swap_join_inputs这个Hint，swap_join_inputs就是用来修改上面说的这个规则的：

select
/*+ leading(d l e j)
use_nl(l)
use_hash(e)
swap_join_inputs(e)
use_hash(j)
swap_join_inputs(j)
*/
e.last_name, d.department_name, j.job_title
from employees e, departments d, jobs j, locations l
where e.department_id=d.department_id
and e.job_id = j.job_id
and d.location_id=l.location_id
and j.job_title='Programmer'
and l.city='Beijing'
and d.department_name='IT'
;

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                |    1 | 78 | 10  (10)| 00:00:01 |
|*  1 |  HASH JOIN                   |                |    1 | 78 | 10  (10)| 00:00:01 |
|*  2 | TABLE ACCESS FULL          | JOBS          |    1 | 27 |    3 (0)| 00:00:01 |
|*  3 | HASH JOIN                   |                | 10 | 510 |    7  (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL          | EMPLOYEES    | 107 |  2140 |    3 (0)| 00:00:01 |
|*  5 | TABLE ACCESS BY INDEX ROWID  | LOCATIONS    |    1 | 12 |    1 (0)| 00:00:01 |
| 6 |    NESTED LOOPS             |                |    1 | 31 |    3 (0)| 00:00:01 |
| 7 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS    |    1 | 19 |    2 (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN       | DEPART_NAME_IND |    1 |    |    1 (0)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN          | LOC_CITY_IX    |    1 |    |    0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

你也许奇怪为什么我要加use_hash(e)这样的Hint，因为我发现如果我使用其他join的方式，swap_join_inputs一般是不生效的。
由于swap_join_inputs是个没有官方记载的Hint，所以我还不能肯定的说swap_join_inputs只支持hash join, 这一点欢迎大家都来测一下。

但是如果你想连接顺序变成这样该怎么搞？
1） departments和location连。
2） employees和jobs去连接
3）第一步的结果和第二步的结果做连接，第二步的结果作为驱动。

(⊙o⊙)… 别难为我了，写成子查询吧。。。

SWAP_JOIN_INPUTS这个Hint还可以用在调整外连接的连接顺序，下面这里例子我在wabjtam123 的帖子里贴过的，

SQL> set autotrace traceonly
SQL> select
  2  t2.c1, t2.c2
  3  from t1,t2 where t2.c1(+)=t1.c1;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 | 39 |    5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER |    |    1 | 39 |    5  (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 |    1 | 13 |    2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 |    1 | 26 |    2 (0)| 00:00:01 |
---------------------------------------------------------------------------

要调整outer join的连接顺序，使用leading是不够的，oracle在处理outer join的连接顺序的时候会有些小固执，需要加上USE_HASH
和SWAP_JOIN_INPUTS：

SQL>
SQL> select
  2  /*+
  3    LEADING(t1 t2)
  4    USE_HASH(t2)
  5    SWAP_JOIN_INPUTS(t2)
  6  */
  7  t2.c1, t2.c2
  8  from t1,t2 where t2.c1(+)=t1.c1;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 312430291

------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    1 | 39 |    5  (20)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|    |    1 | 39 |    5  (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 |    1 | 26 |    2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 |    1 | 13 |    2 (0)| 00:00:01 |
------------------------------------------------------------------------------

[ 本帖最后由 fusnow 于 2010-2-5 09:55 编辑 ]