ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转载--常见hints的基础用法

转载--常见hints的基础用法

原创 Linux操作系统 作者:wadekobe9 时间:2012-01-19 15:19:59 0 删除 编辑

                                              --      优化器名词解释    --                     

Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based  Optimization,简称为RBO)和基于代价的优化方式(Cost-Based  Optimization,简称为CBO),在Oracle8及以后的版本,Oracle强列推荐用CBO的方式 

        RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。 

 

        CBO方式:它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息,这时直接走rule要快得多。 

 

        注意:走索引不一定就是优的,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full  table  scan)是最好 

 

        优化模式包括RuleChooseFirst  rowsAll  rows四种方式: 

 

        Rule:基于规则的方式。 

 

        Choolse:默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。 

 

        First  Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。 

 

        All  Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。 

 

        设定选用哪种优化模式: 

 

        AInstance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。 

        BSessions级别通过ALTER  SESSION  SET  OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。 

        C、语句级别用Hint/*+  ...  */)来设定 

 

        为什么表的某个字段明明有索引,但执行计划却不走索引? 

 

        1、优化模式是all_rows的方式 

        2、表作过analyze,有统计信息,CBO选择走全部扫描 

        3、表很小,上文提到过的,Oracle的优化器认为不值得走索引。

 

 

 

Hint概述 

基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。

此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从  而使语句高效的运行。例如,如果我们认为对于一个特定的语句,执行全表扫描要比执行索引扫描更有效,则我们就可以指示优化器使用全表扫描。在Oracle  中,是通过为语句添加  Hints(提示)来实现干预优化器优化的目的。

不建议在代码中使用hint,在代码使用hint使得CBO无法根据实际的数据状态选择正确的执行计划。毕竟  数据是不断变化的,  10g以后的CBO也越来越完善,大多数情况下我们该让Oracle自行决定采用什么执行计划。

Oracle  Hints是一种机制,用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用Oracle  Hints来实现:

1使用的优化器的类型

2基于代价的优化器的优化目标,是all_rows还是first_rows

3表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid

4表之间的连接类型

5表之间的连接顺序

6语句的并行程度

除了RULE提示外,一旦使用的别的提示,语句就会自动的改为使用CBO优化器,此时如果你的数据字典中没有统计数据,就会使用缺省的统计数据。所以建议大家如果使用CBOHints提示,则最好对表和索引进行定期的分析。

如何使用Hints:

Hints只应用在它们所在sql语句块(statement  block,由selectupdatedelete关键字标识)上,对其它SQL语句或语句的其它部分没有影响。如:对于使用union操作的2sql语句,如果只在一个sql语句上有Hints,则该Hints不会影响另一个sql语句。

我们可以使用注释(comment)来为一个语句添加Hints,一个语句块只能有一个注释,而且注释只能放在SELECT,  UPDATEor  DELETE关键字的后面

使用Oracle  Hints的语法:

{DELETE|INSERT|SELECT|UPDATE/*+  hint  [text]  [hint[text]]...  */

or

{DELETE|INSERT|SELECT|UPDATE--+  hint  [text]  [hint[text]]...

注解:

1DELETEINSERTSELECTUPDATE是标识一个语句块开始的关键字,包含提示的注释只能出现在这些关键字的后面,否则提示无效。

2)  “+”号表示该注释是一个Hints,该加号必须立即跟在/*”的后面,中间不能有空格。

3)  hint是下面介绍的具体提示之一,如果包含多个提示,则每个提示之间需要用一个或多个空格隔开。

4)  text  是其它说明hint的注释性文本

5)使用表别名。如果在查询中指定了表别名,那么提示必须也使用表别名。例如:select  /*+  index(e,dept_idx)  */  *  from  emp  e;

6)不要在提示中使用模式名称:如果在提示中指定了模式的所有者,那么提示将被忽略。例如:

select  /*+  index(scott.emp,dept_idx)  */  *  from  emp

注意:如果你没有正确的指定HintsOracle将忽略该Hints,并且不会给出任何错误。

hint被忽略

如果CBO认为使用hint会导致错误的结果时,hint将被忽略,详见下例

SQLselect  /*+  index(t  t_ind)  */  count(*)  from  t;

Execution  Plan

----------------------------------------------------------

Plan  hash  value2966233522

-------------------------------------------------------------------

Id    |  Operation                    |  Name  |  Rows    |  Cost  (%CPU)|  Time          |

-------------------------------------------------------------------

|      0  |  SELECT  STATEMENT      |            |          1  |        57      (2)|  00:00:01  |

|      1  |    SORT  AGGREGATE        |            |          1  |                        |                    |

|      2  |      TABLE  ACCESS  FULL|  T        |  50366  |        57      (2)|  00:00:01  |

-------------------------------------------------------------------

因为我们是对记录求总数,且我们并没有在建立索引时指定不能为空,索引如果CBO选择在索引上进行count时,但索引字段上的值为空时,结果将不准确,故CBO没有选择索引。 

SQL>    select  /*+  index(t  t_ind)  */  count(idfrom  t;

Execution  Plan

----------------------------------------------------------

Plan  hash  value646498162

--------------------------------------------------------------------------

Id    |  Operation                |  Name    |  Rows    |  Bytes  |  Cost  (%CPU)|  Time          |

--------------------------------------------------------------------------

|      0  |  SELECT  STATEMENT  |              |          1  |          5  |      285      (1)|  00:00:04  |

|      1  |    SORT  AGGREGATE    |              |          1  |          5  |                        |                    |

|      2  |      INDEX  FULL  SCAN|  T_IND  |  50366  |      245K|      285      (1)|  00:00:04  |

--------------------------------------------------------------------------

因为我们只对id进行count,这个动作相当于count索引上的所有id值,这个操作和对表上的id字段进行count是一样的(组函数会忽略null)

Hint的具体用法

和优化器相关的hint

1/*+  ALL_ROWS  */

表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.

SELECT  /*+  ALL+_ROWS*/  EMP_NO,EMP_NAM,DAT_IN  FROM  BSEMPMS  WHERE  EMP_NO='SCOTT'

 

2/*+  FIRST_ROWS(n)  */

表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

SELECT  /*+FIRST_ROWS(20)  */  EMP_NO,EMP_NAM,DAT_IN  FROM  BSEMPMS  WHERE  EMP_NO='SCOTT'

 

3/*+  RULE*/

表明对语句块选择基于规则的优化方法.

SELECT  /*+  RULE  */  EMP_NO,EMP_NAM,DAT_IN  FROM  BSEMPMS  WHERE  EMP_NO='SCOTT'

 

和访问路径相关的hint

1/*+  FULL(TABLE)*/

表明对表选择全局扫描的方法.

SELECT  /*+FULL(A)*/  EMP_NO,EMP_NAM  FROM  BSEMPMS  A  WHERE  EMP_NO='SCOTT'

 

2/*+  INDEX(TABLE  INDEX_NAME)  */

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

SELECT  /*+INDEX(BSEMPMS  SEX_INDEX)  */  *  FROM  BSEMPMS  WHERE  SEX='M'

 

5/*+  INDEX_ASC(TABLE  INDEX_NAME)*/

表明对表选择索引升序的扫描方法.

SELECT  /*+INDEX_ASC(BSEMPMS  PK_BSEMPMS)  */  *  FROM  BSEMPMS  WHERE  DPT_NO='SCOTT'

 

6/*+  INDEX_COMBINE*/

为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.

SELECT  /*+INDEX_COMBINE(BSEMPMS  SAL_BMI  HIREDATE_BMI)  */    *  FROM  BSEMPMS

WHERE  SAL<5000000  AND  HIREDATE 

 

7/*+  INDEX_JOIN(TABLE  INDEX_NAME1  INDEX_NAME2)  */

当谓词中引用的列都有索引的时候,可以通过指定采用索引关联的方式,来访问数据

select  /*+  index_join(t  t_ind  t_bm)  */  id  from  t  where  id=100  and  object_name='EMPLOYEES' 

 

8/*+  INDEX_DESC(TABLE  INDEX_NAME)*/

表明对表选择索引降序的扫描方法.

SELECT  /*+INDEX_DESC(BSEMPMS  PK_BSEMPMS)  */  *  FROM  BSEMPMS  WHERE  DPT_NO='SCOTT'

 

9/*+  INDEX_FFS(TABLE  INDEX_NAME)  */

对指定的表执行快速全索引扫描,而不是全表扫描的办法.

SELECT  /*  +  INDEX_FFS(BSEMPMS  IN_EMPNAM)*/  *  FROM  BSEMPMS  WHERE  DPT_NO='TEC305';

 

10/*+  INDEX_SS(T  T_IND)  */

9i开始,oracle引入了这种索引访问方式。当在一个联合索引中,某些谓词条件并不在联合索引的第一列时,可以通过Index  Skip  Scan来访问索引获得数据。当联合索引第一列的唯一值个数很少时,使用这种方式比全表扫描效率高。

SQLcreate  table  t  as  select  1  id,object_name  from  dba_objects;

Table  created.

SQLinsert  into  t  select  2,object_name  from  dba_objects;             

50366  rows  created.

SQLinsert  into  t  select  3,object_name  from  dba_objects;              

50366  rows  created. 

SQLinsert  into  t  select  4,object_name  from  dba_objects;             

50366  rows  created. 

SQLcommit;

Commit  complete.

SQLcreate  index  t_ind  on  t(id,object_name);

Index  created.

SQLexec  dbms_stats.gather_table_stats('HR','T',cascade=>true);

PL/SQL  procedure  successfully  completed.

执行全表扫描 

SQLselect  /*+  full(t)  */  *  from  t  where  object_name='EMPLOYEES';

6  rows  selected.

Execution  Plan

----------------------------------------------------------

Plan  hash  value1601196873

--------------------------------------------------------------------------

Id    |  Operation                  |  Name  |  Rows    |  Bytes  |  Cost  (%CPU)|  Time          |

--------------------------------------------------------------------------

|      0  |  SELECT  STATEMENT    |            |          5  |      135  |      215      (3)|  00:00:03  |

|*    1  |    TABLE  ACCESS  FULL|  T        |          5  |      135  |      215      (3)|  00:00:03  |

--------------------------------------------------------------------------

Predicate  Information  (identified  by  operation  id):

---------------------------------------------------

      1  -  filter("OBJECT_NAME"='EMPLOYEES')

Statistics

----------------------------------------------------------

                    0    recursive  calls

                    0    db  block  gets

                942    consistent  gets

                    0    physical  reads

                    0    redo  size

                538    bytes  sent  via  SQL*Net  to  client

                385    bytes  received  via  SQL*Net  from  client

                    2    SQL*Net  roundtrips  to/from  client

                    0    sorts  (memory)

                    0    sorts  (disk)

                    6    rows  processed 

不采用hint 

SQL>    select  *  from  t  where  object_name='EMPLOYEES';

6  rows  selected.

Execution  Plan

----------------------------------------------------------

Plan  hash  value2869677071

--------------------------------------------------------------------------

Id    |  Operation                |  Name    |  Rows    |  Bytes  |  Cost  (%CPU)|  Time          |

--------------------------------------------------------------------------

|      0  |  SELECT  STATEMENT  |              |          5  |      135  |          5      (0)|  00:00:01  |

|*    1  |    INDEX  SKIP  SCAN  |  T_IND  |          5  |      135  |          5      (0)|  00:00:01  |

--------------------------------------------------------------------------

Predicate  Information  (identified  by  operation  id):

---------------------------------------------------

      1  -  access("OBJECT_NAME"='EMPLOYEES')

              filter("OBJECT_NAME"='EMPLOYEES')

Statistics

----------------------------------------------------------

                    1    recursive  calls

                    0    db  block  gets

                  17    consistent  gets

                    1    physical  reads

                    0    redo  size

                538    bytes  sent  via  SQL*Net  to  client

                385    bytes  received  via  SQL*Net  from  client

                    2    SQL*Net  roundtrips  to/from  client

                    0    sorts  (memory)

                    0    sorts  (disk)

                    6    rows  processed

当全表扫描扫描了942个块,联合索引只扫描了17个数据块。可以看到联合索引的第一个字段的值重复率很高时,即使谓词中没有联合索引的第一个字段,依然会使用index_ss方式,效率远远高于全表扫描效率。但当  第一个字段的值重复率很低时,使用  index_ss的效率要低于  全表扫描,读者可以自行实验 

和表的关联相关的hint

/*+  leading(table_1,table_2)  */

在多表关联查询中,指定哪个表作为驱动表,即告诉优化器首先要访问哪个表上的数据。 

select  /*+  leading(t,t1)  */  t.*  from  t,t1  where  t.id=t1.id; 

 

/*+  order  */

Oracle根据from后面表的顺序来选择驱动表,oracle建议使用leading,他更为灵活 

select  /*+  order  */  t.*  from  t,t1  where  t.id=t1.id;

 

/*+  use_nl(table_1,table_2)  */ 

在多表关联查询中,指定使用nest  loops方式进行多表关联。

select  /*+  use_nl(t,t1)  */  t.*  from  t,t1  where  t.id=t1.id;

 

/*+  use_hash(table_1,table_2)  */ 

在多表关联查询中,指定使用hash  join方式进行多表关联。

select  /*+  use_hash(t,t1)  */  t.*  from  t,t1  where  t.id=t1.id;

 

/*+  use_merge(table_1,table_2)  */ 

在多表关联查询中,指定使用merge  join方式进行多表关联。

select  /*+  use_merge(t,t1)  */  t.*  from  t,t1  where  t.id=t1.id;

 

/*+  no_use_nl(table_1,table_2)  */ 

在多表关联查询中,指定不使用nest  loops方式进行多表关联。

select  /*+  no_use_nl(t,t1)  */  t.*  from  t,t1  where  t.id=t1.id;

 

/*+  no_use_hash(table_1,table_2)  */ 

在多表关联查询中,指定不使用hash  join方式进行多表关联。

select  /*+  no_use_hash(t,t1)  */  t.*  from  t,t1  where  t.id=t1.id;

 

/*+  no_use_merge(table_1,table_2)  */ 

在多表关联查询中,指定不使用merge  join方式进行多表关联。

select  /*+  no_use_merge(t,t1)  */  t.*  from  t,t1  where  t.id=t1.id;

 

其他常用的hint

/*+  parallel(table_name  n)  */ 

sql中指定执行的并行度,这个值将会覆盖自身的并行度

select  /*+  parallel(t  4)  */  count(*)    from  t;

 

/*+  no_parallel(table_name)  */ 

sql中指定执行的不使用并行

select  /*+  no_parallel(t)  */  count(*)    from  t;

 

/*+  append  */以直接加载的方式将数据加载入库

insert  into  t  /*+  append  */  select  *  from  t;

 

/*+  dynamic_sampling(table_name  n)  */

设置sql执行时动态采用的级别,这个级别为0~10

select  /*+  dynamic_sampling(t  4)  */  *  from  t  where  id  >  1234 

 

/*+  cache(table_name)  */ 

进行全表扫描时将table置于LRU列表的最活跃端,类似于tablecache属性

select  /*+  full(employees)  cache(employees)  */  last_name  from  employees 

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10678398/viewspace-715106/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 非空闲等待事件
下一篇: LRU链
请登录后发表评论 登录
全部评论

注册时间:2010-11-30

  • 博文量
    36
  • 访问量
    56693