关于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 编辑 ]

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 编辑 ]

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 编辑 ]

fusnow · 发表于 2010-1-31 20:02

query block的名字

这段的实际应用意义不大，研究这个主要为了好玩。

前面提到我们可以用qb_name这个hint来定义SQL里面的query block的名字，实际上如果我们多看看
dbms_xplan.display_cursor的advanced模式的输出，或者看看10053 trace，或者看看10132 trace，就能发现其实oracle的CBO会
自己定义query block的名字，而且你会发现对于固定的SQL，这些query block的名字都是固定的。

这里打算介绍一下oracle是如何确定这些query block名字的，这已经超出了hint的范畴，但是如果你看到oracle给你列出这样的一条Hint，
了解query block的命名的由来对你读懂这个hint是有帮助的：
LEADING(@"SEL$F5BB74E1" "D"@"SEL$1" "EMPLOYEES"@"SEL$2")
--如果SQL多次出现employees这张表，你知道这个Hint是指哪个employees么？

其实一般来说，oracle对query block的命名还是很直观的，
如果是一个select，那么就命名为SEL$n，这里的n是1，2，3...
如果是delete，就命名为DEL$n，是update，就命名为UPD$n，是insert，就叫INS$n, 是merge，就叫MISC$n...

下面我举出两个例子，一个select, 一个delete，其他的大家可以自己去试。

explain plan for
select * from employees where salary = (select max(salary) from employees);

select * from table(dbms_xplan.display(null,null,'alias'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$2
3 - SEL$2 / EMPLOYEES@SEL$2

explain plan for
delete from employees;

select * from table(dbms_xplan.display(null,null,'alias'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - DEL$1
2 - DEL$1 / EMPLOYEES@DEL$1

那为什么会出现类似于SEL$F5BB74E1这样的query block呢？这是因为oracle做了sql转化，我们知道oracle处于性能考虑会
把一些子查询转化为表与表连接的模式，SQL转化是个比较有意思的话题，包含的东西也很多（不仅仅针对子查询），
对于子查询而言，就是当你的SQL包含了子查询或者视图，oracle会对你的SQL进行转化，当然这种转化在10g里是基于cost的，也就是说如果转化后发现cost太高，这个转化就不会被采用。

对于oracle的SQL转化主要是做：
1）转化SPJ (select + project + join) view
2） unnest Subquery
3） merge Complex View （mergable complex view的定义可见于oracle performance tuning guide)
4） Predicate Pushing， or expense, 物化视图重写，等。。。

那么当转化发生后，SQL的结构就变了，所以oracle会为转化后的SQL生成新的query block，为了和没有发生转化的SQL有所区别，
oracle在这里采用比较特殊的格式。

转化后oracle会把原来的一个外围的query block（假如名字是a), 和一个内部的query block(假如名字是b), 转化生成的新的query block的名字应该是a 和 b 的hash函数。

看看下面的例子：
SQL1，这是一个多层嵌套，每个嵌套的子查询都可以被转化，我们能看到oracle在做完转化后生成的新的query block为SEL$9C113579。
explain plan for
select /*+ qb_name(qb1) */ * from
  (select /*+ qb_name(qb2) */ * from
(select /*+ qb_name(qb3) */ * from employees));

select * from table(dbms_xplan.display(null,null,'alias'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9C113579 / EMPLOYEES@QB3

SQL2，是SQL1的第二层嵌套的查询，可以看到转化后生成的query block名字是SEL$1B0F72FE
explain plan for
select /*+ qb_name(qb2) */ * from
(select /*+ qb_name(qb3) */ * from employees);

select * from table(dbms_xplan.display(null,null,'alias'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1B0F72FE / EMPLOYEES@QB3

SQL3，最外面得查询还是叫qb1，在里面随便套了一个子查询，但是我们把第二层查询的名字定义为SEL$1B0F72FE。
explain plan for
select /*+ qb_name(qb1) */ * from
  (select /*+ qb_name(SEL$1B0F72FE) */ * from departments);

select * from table(dbms_xplan.display(null,null,'alias'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9C113579 / EMPLOYEES@SEL$1B0F72FE

可以看到转化后的query block的名字是SEL$9C113579，和我们的第一个SQL转化后得到的query block名字一致，这就可以证明，当oracle对SQL的子查询进行了转化后，
会生成新的query block的名字，这个名字是由原来的外围query block名字和原来的内层的query block名字决定的，估计是一个hash 函数。

如果我们把外面的query block名字改掉，得到的新的转化后的query block名字就会变了：

explain plan for
select /*+ qb_name(qbnew) */ * from
  (select /*+ qb_name(SEL$1B0F72FE) */ * from departments);

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$90C23CFB / DEPARTMENTS@SEL$1B0F72FE  --变了

下面这个例子可以看出，我把第二层的子查询做了一些改动，又套上了一些其他的子查询，但是使用no_merge hint来避免里面的子查询被oracle的CBO转化掉：
explain plan for
select /*+ qb_name(qb1) */ * from
  (select /*+ qb_name(SEL$1B0F72FE) */ * from employees where department_id in
    (select /*+ no_merge */ max(department_id) from departments where location_id in
    (select /*+ no_merge */ max(location_id) from locations)));

select * from table(dbms_xplan.display(null,null,'alias'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$9C113579 / EMPLOYEES@SEL$1B0F72FE
2 - SEL$9C113579 / EMPLOYEES@SEL$1B0F72FE
3 - SEL$1
4 - SEL$1       / DEPARTMENTS@SEL$1
5 - SEL$1       / DEPARTMENTS@SEL$1
6 - SEL$2
7 - SEL$2       / LOCATIONS@SEL$2

可以看到，即使把内层的SQL做了这么多改变，最终转化出来的query block的名字还叫SEL$9C113579。

最后看一个特殊的例子：

这里是一个简单的2个表连接，没有子查询，但是我们可以看到在query block的名字是SEL$58A6D7F6，和我前面说的规则并不一样，为什么会这样？

explain plan for
select department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing';

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                |    4 | 76 |    3 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS    |    4 | 28 |    1 (0)| 00:00:01 |
| 2 | NESTED LOOPS             |                |    4 | 76 |    3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| LOCATIONS       |    1 | 12 |    2 (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN       | LOC_CITY_IX    |    1 |    |    1 (0)| 00:00:01 |
|*  5 | INDEX RANGE SCAN          | DEPT_LOCATION_IX |    4 |    |    0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$58A6D7F6 / D@SEL$1
3 - SEL$58A6D7F6 / L@SEL$1
4 - SEL$58A6D7F6 / L@SEL$1
5 - SEL$58A6D7F6 / D@SEL$1

原因在于这个SQL使用了ANSI的表连接语法，oracle在解析“join locations l on”这样的条件的时候会把它看做是一个子查询，于是乎会做转化，于是乎生成SEL$58A6D7F6这样的query block名字。

在10053 trace里面会看到(VIEW MERGE SEL$2; SEL$1)这样的字样，然后query被转化为：
select department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing'

10053 trace的部分内容：
SQL:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "HR"."DEPARTMENTS" "D","HR"."LOCATIONS" "L" WHERE "L"."CITY"='Beijing' AND "D"."LOCATION_ID"="L"."LOCATION_ID"
Query block (04B6A5E0) unchanged
Registered qb: SEL$58A6D7F6 0x4b6a5e0 (VIEW MERGE SEL$2; SEL$1)
  signature (): qb_name=SEL$58A6D7F6 nbfros=2 flg=0
fro(0): flg=0 objn=51905 hint_alias="D"@"SEL$1"
fro(1): flg=0 objn=51900 hint_alias="L"@"SEL$1"
.........................................................
.........................................................
****************
QUERY BLOCK TEXT
****************
select department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing'

这对我们有什么影响吗？其实影响不大，我前面也说这一段不是为了使用，仅仅为了好玩，不过假如上面的SQL作为我们下面语句的子查询，并且我想让表locations走全表扫描，我按理应该可以这样写hint：
explain plan for
select /*+ full(@inner l) */ * from employees where department_id in
(select /*+ qb_name(inner) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing');

-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                | 39 |  3159 |    6  (17)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID    | EMPLOYEES       | 10 | 680 |    1 (0)| 00:00:01 |
| 2 | NESTED LOOPS                   |                | 39 |  3159 |    6  (17)| 00:00:01 |
| 3 | VIEW                         | VW_NSO_1       |    4 | 52 |    3 (0)| 00:00:01 |
| 4 |    HASH UNIQUE                |                |    4 | 76 |          |       |
| 5 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS    |    4 | 28 |    1 (0)| 00:00:01 |
| 6 |    NESTED LOOPS             |                |    4 | 76 |    3 (0)| 00:00:01 |
| 7 |       TABLE ACCESS BY INDEX ROWID| LOCATIONS       |    1 | 12 |    2 (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN       | LOC_CITY_IX    |    1 |    |    1 (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | DEPT_LOCATION_IX  |    4 |    |    0 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN             | EMP_DEPARTMENT_IX | 10 |    |    0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$78C29F24 / EMPLOYEES@SEL$2
3 - SEL$49A70C8F / VW_NSO_1@SEL$78C29F24
4 - SEL$49A70C8F
5 - SEL$49A70C8F / D@SEL$1
7 - SEL$49A70C8F / L@SEL$1
8 - SEL$49A70C8F / L@SEL$1
9 - SEL$49A70C8F / D@SEL$1
  10 - SEL$78C29F24 / EMPLOYEES@SEL$2

但如你所见，hint似乎没有生效，这个原因就是尽管我们给
(select /*+ qb_name(inner) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing')
这个子查询定义了query block的名字，但是因为这个是ANSI语法的连接，oracle会做一次转换，这次转化会给子查询生成一个新的query block(SEL$49A70C8F),
这进一步导致/*+ full(@inner l) */这个针对inner的Hint失效。

要想达到目的的方法也很简单，比如hint可以这么写：
select * from employees where department_id in
(select /*+ full(l) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing');

或者既然已经知道了新的query block的名字，我们就可以这么写（当然这种写法很奇怪，一般也不应该怎么写就是了）：
select /*+ full(@SEL$49A70C8F l) */ * from employees where department_id in
(select /*+ qb_name(inner) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing');

--如前所述，这里/*+ qb_name(inner) */还是需要的，否则转化后的query block名字又会不一样。

如果说能从这里例子总结点什么出来的话，就是如果你在oracle里写SQL，并估计以后可能需要给它加Hint，为了使用Hint方便，可以考虑尽量使用oracle的语法.

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

fusnow · 发表于 2010-2-1 16:20

谢谢rollingpig兄加精

fusnow · 发表于 2010-2-4 23:23

原帖由 lynch0227 于 2010-2-4 10:13 发表
前三百年后三百年无人能敌~
狂顶

还好看过凤姐的故事，差点被你忽悠。

信凤姐，得自信

fusnow · 发表于 2010-2-5 09:56

原帖由〇〇于 2010-2-5 06:08 发表
lz把字体改为宋体表格就对齐

关于hint

浏览过的版块