概念描述

DM 查询优化器采用基于代价的方法。在估计代价时，主要以统计信息或者普遍的数据分布为依据。在大多数情况下，估计的代价都是准确的。但在一些比较特殊的场合，例如缺少统计信息，或统计信息陈旧，或抽样数据不能很好地反映数据分布时，优化器选择的执行计划不是―最优‖的，甚至可能是很差的执行计划。

DBA 对于数据分布是很清楚的，知道SQL语句按照哪种方法执行会最快。在这种情况下，DBA 可以主动进行人工干预，指示优化器按照指定的方法去选择 SQL的执行计划。

DM 把这种人工干预优化器的方法称为 HINT，它使优化器根据 DBA 的 HINT 提示来生成指定的执行计划。如果优化器无法生成相应的执行计划，该 HINT 将会被忽略。

一、Hint常见格式：

1）hint格式   /*+ <具体的Hint内容> */
2）Hint在SQL中必须紧跟关键字SELECT、INSERT、UPDATE、DELETE或MERGE之后
{SELECT | INSERT | UPDATE | DELETE | MERGE} /*+ <具体的Hint内容> */
3）Hint中第一个星号(*)和加号(+)之间不能有空格
4）Hint中加号(+)和具体的Hint内容之间可以有空格，也可以没有空格，但通常都有空格。
5）Hint中的具体内容可以是单个Hint，也可以是多个Hint的组合，各个Hint间至少需要1个空格来彼此分隔。
6）Hint中指定具体对象时(比如指定表名或者索引名)，不能带上该对象所在SCHEMA的名称，即使该SQL文本中已经有对应的SCHEMA名称。
explain select /*+ NO_INDEX(emp,EMP_IDX) */ * from sysdba.emp where EMPLOYEE_ID=100;
7）Hint中指定具体表名时，如果该表在对应SQL文本中有别名(alias)，则应该使用该表的别名。
explain select /*+ NO_INDEX(e,EMP_IDX) */ * from emp e where e.EMPLOYEE_ID=100;

二、INI参数提示：

DBA 可以通过 HINT 方式对 INI 参数的值进行语句级的指定。语句中的 HINT 对 INI参数值的设置优先级高于 INI 文件中参数值的设置。通过 HINT 方式只会修改 INI 参数的在本会话中的值，不会改变它在 INI 文件中的值。支持使用 HINT 的 INI 参数可通过 V$HINT_INI_INFO 动态视图查询。支持 HINT 的INI 参数分为两类：一是 HINT_TYPE 为“OPT”，表示分析阶段使用的参数；二是HINT_TYPE 为“EXEC”，表示运行阶段使用的参数，运行阶段使用的参数对于视图无效。

例如：

SELECT /*+ ENABLE_HASH_JOIN(1)*/ * FROM T1,T2 WHERE C1=D1;

上面的语句中使用了 HINT，指明在执行此 SQL 时参数 ENABLE_HASH_JOIN 被置为 1。

测试验证

为了便于测试演示，创建如下表结构。

建表结构：
DROP TABLE test.t1 CASCADE;
create table test.t1 as select * from dba_objects;
CREATE INDEX IDX_T1_ID ON test.T1(object_id);
CREATE INDEX IDX_T1_NAME ON test.T1(object_name);

DROP TABLE test.t2 CASCADE;
create table test.t2 as select * from dba_tables;
CREATE INDEX IDX_T2_TABLENAME ON test.t2 (TABLE_NAME);

DBMS_STATS.GATHER_TABLE_STATS('TEST', 'T1',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('TEST', 'T2',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

一、INI参数提示

1.HASH连接参数：ENABLE_HASH_JOIN

参数：0为禁用、1为启用。

explain 
SELECT /*+ ENABLE_HASH_JOIN(1) */* 
  FROM TEST.T1, 
       TEST.T2 
 WHERE T1.object_id<1000 
   AND T1.OBJECT_NAME=T2.TABLE_NAME;

执行计划如下：

1   #NSET2: [1, 64, 2890] 
2     #PRJT2: [1, 64, 2890]; exp_num(71), is_atom(FALSE) 
3       #HASH2 INNER JOIN: [1, 64, 2890];  KEY_NUM(1); KEY(T1.OBJECT_NAME=T2.TABLE_NAME) KEY_NULL_EQU(0)
4         #BLKUP2: [1, 62, 597]; IDX_T1_ID(T1)
5           #SSEK2: [1, 62, 597]; scan_type(ASC), IDX_T1_ID(T1), scan_range(null2,exp_cast(1000))
6         #CSCN2: [1, 84, 2293]; INDEX33555487(T2)

explain 
SELECT /*+ ENABLE_HASH_JOIN(0) */* 
  FROM TEST.T1, 
       TEST.T2 
 WHERE T1.object_id<1000 
   AND T1.OBJECT_NAME=T2.TABLE_NAME;

1   #NSET2: [4, 62, 2890] 
2     #PRJT2: [4, 62, 2890]; exp_num(71), is_atom(FALSE) 
3       #NEST LOOP INDEX JOIN2: [4, 62, 2890] 
4         #BLKUP2: [1, 62, 597]; IDX_T1_ID(T1)
5           #SSEK2: [1, 62, 597]; scan_type(ASC), IDX_T1_ID(T1), scan_range(null2,exp_cast(1000))
6         #BLKUP2: [4, 1, 48]; IDX_T2_TABLENAME(T2)
7           #SSEK2: [4, 1, 48]; scan_type(ASC), IDX_T2_TABLENAME(T2), scan_range[T1.OBJECT_NAME,T1.OBJECT_NAME]

改为0后禁用了语句级的HASH连接。

2.OR条件的参数：OPTIMIZER_OR_NBEXP

通过该hint 优化。 0 为分开，2为合并处理。

explain
select * 
  from test.t1 
 where object_name like 'UTL_%'
   AND (object_type ='SCH' 
     or object_type IS NULL);

1   #NSET2: [1, 2, 609] 
2     #PRJT2: [1, 2, 609]; exp_num(16), is_atom(FALSE) 
3       #UNION FOR OR2: [1, 2, 609]; key_num(1), outer_join(-)
4         #SLCT2: [1, 1, 609]; (T1.OBJECT_TYPE IS NULL AND T1.OBJECT_NAME LIKE 'UTL_%')
5           #BLKUP2: [1, 36, 609]; IDX_T1_NAME(T1)
6             #SSEK2: [1, 36, 609]; scan_type(ASC), IDX_T1_NAME(T1), scan_range['UTL','UTM')
7         #SLCT2: [1, 1, 609]; (T1.OBJECT_TYPE = 'SCH' AND T1.OBJECT_NAME LIKE 'UTL_%')
8           #BLKUP2: [1, 36, 609]; IDX_T1_NAME(T1)
9             #SSEK2: [1, 36, 609]; scan_type(ASC), IDX_T1_NAME(T1), scan_range['UTL','UTM')

执行成功, 执行耗时19毫秒

由于使用了OR查询object_type的两部分。默认情况下优化器把两部分单独查询处理。这样造成的问题是多次查询增加了几乎一般的性能开销。

explain
select /*+ OPTIMIZER_OR_NBEXP(2) */* 
  from test.t1 
 where object_name like 'UTL_%'
   AND (object_type ='SCH' 
     or object_type IS NULL);

将参数改为2.合并处理。

1   #NSET2: [1, 1, 609] 
2     #PRJT2: [1, 1, 609]; exp_num(16), is_atom(FALSE) 
3       #SLCT2: [1, 1, 609]; (T1.OBJECT_NAME LIKE 'UTL_%' AND (T1.OBJECT_TYPE IS NULL OR T1.OBJECT_TYPE = 'SCH'))
4         #BLKUP2: [1, 36, 609]; IDX_T1_NAME(T1)
5           #SSEK2: [1, 36, 609]; scan_type(ASC), IDX_T1_NAME(T1), scan_range['UTL','UTM')

执行成功, 执行耗时10毫秒

执行时间缩短了一半左右。

二、索引类别提示

通常的语法格式：

表名 + INDEX + 索引名
或
/*+ INDEX (表名[,] 索引名) {INDEX (表名[,] 索引名)} */
一个语句中最多指定 8 个索引。在后一种语法格式中，如果查询中给出了表的别名那么 必须使用别名。

1.使用索引：INDEX

explain select * from test.t1 T1 where object_id>1000 and object_name like 'DB%';

1   #NSET2: [1, 209, 609] 
2     #PRJT2: [1, 209, 609]; exp_num(16), is_atom(FALSE)

......