Oracle中hint与spm区别,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)功能更加强大，更加智能。在后续的文章向大家介绍.