ORACLE的Optimizer hint基本原理剖析

                                  ORACLE的Optimizer hint基本原理剖析

       optimzer在评估执行计划的时候考虑的因素主要有统计信息，初始化参数，数据字典的信息。对于这些信息
我们能够人为更改的非常有限，而且影响比较大。这时候我们只能通过hint来实现让optimizer选择我们计划的执行
计划，从而达到一个稳定执行计划的目的.

     大部分使用hint来固定执行划的因素无非是:1,optimizer本身存在着bug,不能选择正确的执行计划;2,对于某些
特定的场景，固定执行计划可能更有效，防止执行计划的突然变更引发的性能问题.

    optimizer仅根据hint在可选的执行计划，选择满足hint的执行计划，如果hint指定的执行计划不存在，那么opmizer
会放弃掉hint.

例：创建一个表t1,并在object_id列上创建一个索引

SQL> create table frank.t1
 as select * from dba_objects;

Table created.

SQL> create index ix_object_id
on t1(object_id);

Index created.

SQL> exec dbms_stats.gather_
table_stats('FRANK','T1');

PL/SQL procedure successfully completed.

SQL> explain plan for select
/*+ INDEX(test) */ * from t1 where object_id>100;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'ROWS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 68677 |  6773K|   285   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   | 68677 |  6773K|   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

  例子中试图指定一个不存在索引test,由于该索引不存在，所以optimizer没有考虑这种执行
计划，从而没有使用这种执行划.

   hint可以分为很多类，比如有join方法，join顺序,access path等等，具体可以能考oracle
官方文档.hint的一般是紧跟在sql语句的第一个关健字之后，如select /*+ index */,
insert /*+ append*/ 等等，对于错误的hint的，可以通过设置会话的10053 level 4号事件来
查看hint是否被启用了

对应的10053事件的trace文件

SQL> select /*+ FULL(a) */  * from t1 a where object_id=10

 

============
Plan Table
============

-------------------------------------+-----------------------------------+

| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-
------------------------------------+-----------------------------------+

| 0   | SELE.CT STATEMENT   |         |       |       |  1135 |           |

| 1   |  TABLE ACCESS FULL | T1      |     1 |   101 |  1135 |  00:00:14 |

-------------------------------------+-----------------------------------+

Dumping Hints
=============
 
 atom_hint=(@=0x2ae3d31cdc10 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("A") )

used 1表示当前启了一个hint,err表示错误的hint.因为同一个语句可能会存在多个hint.如果hint有错误
会失效

SQL> select /*+ FULL(T1)  */ * from t1 a where object_id=10

在hint使用了一个非别名的表名，这种hint是不合法的，我们来看看对应的trace文件
End of Optimizer State Dump
Dumping Hints
=============
  atom_hint=(@=0x2b405c7135e0 err=0 resol=1 used=0 token=448 org=1 lvl=3 txt=FULL ("T1") )

可以看出hint没有被使用.

  通过使用outline，我们对于一些无法更改源代码的应用，很容易人为的固定住执行计划，后续11G推出
了SPM(SQL PLAN MANAGEMENT)功能更加强大，更加智能。在后续的文章向大家介绍.