Oracle中基于hint的3种执行计划控制方法详细介绍

原作者: [db:作者] 来自: [db:来源] 收藏邀请

hint（提示）无疑是最基本的控制执行计划的方式了；通过在SQL语句中直接嵌入优化器指令，进而使优化器在语句执行时强制的选择hint指定的执行路径，这种使用方式最大的好处便是方便和快捷，定制度也很高，通常在对某些SQL语句执行计划进行微调的时候我会首选这种方式，不过尽管如此，hint在使用中仍然有很多不可忽视的问题；

使用hint过程中有一些值得注意的细则，首先便是要准确的识别对应的查询块，如果需要使用注释也可以hint中声明；对于使用别名的对象一律使用别名来引用，并且诸如“用户名.对象”的引用方式也不被允许，这几个都是我平时经常犯的错误，其实细心一点也就没什么关系了，不过最郁闷的是使用hint的过程中没有任何提示信息可以参考！！譬如语句中使用了无效的hint，Oracle并不会给予你任何相关的错误信息，相反这些hint会在执行时被默默的忽略，像什么都没发生一样。。

到这里，我并不想讨论如何正确的使用hint，我想说的是在Oracle中，仍然有很多可以控制执行计划的机制，11g中，有三种基于优化器hint的执行计划控制方式：

1.OUTLINE（大纲）
2.SQL PROFILE（概要文件）
3.SQL BASELINE（基线）

这些方式的使用比较hint更加的系统，完备，它们的出现很大程度上提高了hint这种古老的控制方式的实用性。

OUTLINE（大纲）

OUTLINE的原理是解析SQL语句的执行计划，在此过程中确定一套可以有效的强制优化器选择某个执行计划的hints，然后保存这些hints，当下次发生”相同“查询的时候，优化器便会忽略当前的统计信息因素，选用OUTLINE中记录的hints来执行查询，达到控制执行计划的目的。

OUTLINE的创建通常有两种方式，一种使用create outline语句，另一种便是借助于专属的DBMS_OUTLN包，使用Create outline方式时我们需要注明完整查询语句：

复制代码代码如下:

SQL> create outline my_test_outln for category test on
2 select count(*) from scott.emp;

Outline created.

相比之下，DBMS_OUTLN.CREATE_OUTLINE方式允许通过已经保存在缓存区中的SQL语句的hash值来创建outline，因此更加常用，下面是签名：

复制代码代码如下:

DBMS_OUTLN.CREATE_OUTLINE (

   hash_value    IN NUMBER,

   child_number  IN NUMBER,

   category      IN VARCHAR2 DEFAULT 'DEFAULT');

category用于指定OUTLINE的分类，在一个会话中只能使用一种分类，分类的选择由参数USE_STORED_OUTLINES决定，该参数的默认值为FALSE，表示不适用OUTLINE，设置成TRUE则选用DEFAULT分类下的OUTLINE，如果需要使用非DEFAULT分类下的OUTLINE，可以设置该参数值为对应的分类的名称。

关于OUTLINE的视图通常可以查询DBA_OUTLINES，DBA_OUTLINE_HINTS，数据库中OUTLN用户下也有三张表用于保存OUTLINE信息，其中OL#记载了每一个OUTLINE的完整定义。

复制代码代码如下:

SQL> select TABLE_NAME,OWNER from all_tables where owner='OUTLN';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
OL$                            OUTLN
OL$HINTS                       OUTLN
OL$NODES                       OUTLN

-- 查询当前系统中已有的OUTLINE已经对应OUTLINE使用的hints：
[sql]
SQL> select category,ol_name,hintcount,sql_text from outln.ol$;

CATEGORY   OL_NAME                         HINTCOUNT SQL_TEXT
---------- ------------------------------ ---------- --------------------------------------------------
TEST       MY_TEST_OUTLN                           6 select count(*) from scott.emp
DEFAULT    SYS_OUTLINE_13080517081959001           6 select * from scott.emp where empno=7654

-- 查询对应OUTLINE上应用的hints
SQL> select name, hint from dba_outline_hints where name = 'SYS_OUTLINE_13080517081959001';

NAME HINT
------------------------------ --------------------------------------------------------------------------------
SYS_OUTLINE_13080517081959001 INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
SYS_OUTLINE_13080517081959001 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_13080517081959001 ALL_ROWS
SYS_OUTLINE_13080517081959001 DB_VERSION('11.2.0.1')
SYS_OUTLINE_13080517081959001 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
SYS_OUTLINE_13080517081959001 IGNORE_OPTIM_EMBEDDED_HINTS

6 rows selected.

使用OUTLINE来锁定执行计划的完整实例：

复制代码代码如下:

-- 执行查询
SQL> select * from scott.emp where empno=7654;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

-- 查看该查询的执行计划
-- 注意这里的hash_value和child_number不可作为DBMS_OUTLN.CREATE_OUTLINE参数值，这些只是PLAN_TABLE中保存的执行计划的值！！！
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 40t73tu9dst5y, child number 1
-------------------------------------
select * from scott.emp where empno=7654

Plan hash value: 2949544139

------------------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       2 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       2 |
|* 2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7654)

19 rows selected.

-- 通过v$sql视图获取查询sql语句的hash_value和child_number
SQL> select sql_id,hash_value,child_number,sql_text from v$sql
2 where sql_text like 'select * from scott.emp where empno%';

SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ --------------------------------------------------
40t73tu9dst5y 2463917246 0 select * from scott.emp where empno=7654

-- 创建OUTLINE，指定为默认DEFAULT分类
SQL> exec dbms_outln.create_outline(2463917246,0,'DEFAULT');

PL/SQL procedure successfully completed.

-- SESSION级别设置USE_STORED_OUTLINES参数为TRUE，启用OUTLINE
SQL> ALTER SESSION SET USE_STORED_OUTLINES=TRUE;

Session altered.

-- 重新执行查询，可以看到计划与原先的一致，同时在执行计划的Note中显示了使用了outline "SYS_OUTLINE_13080517081959001"
SQL> set autotrace traceonly
SQL> select * from scott.emp where empno=7654;

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|* 2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7654)

Note
-----
- outline "SYS_OUTLINE_13080517081959001" used for this statement

Statistics
----------------------------------------------------------
       1495 recursive calls
        147 db block gets
        262 consistent gets
          5 physical reads
        632 redo size
        896 bytes sent via SQL*Net to client
        512 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
         24 sorts (memory)
          0 sorts (disk)
          1 rows processed

使用非DEFAULT分类下的OUTLINE

复制代码代码如下:

-- 查看当前可用的OUTLINE
SQL> select category,ol_name,hintcount,sql_text from outln.ol$;

-- 设置使用test分类下的OUTLINE
SQL> ALTER SESSION SET USE_STORED_OUTLINES=test;

Session altered.

-- 执行计划Note显示使用了OUTLINE "MY_TEST_OUTLN"
SQL> set autotrace traceonly
SQL> select count(*) from scott.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id | Operation        | Name   | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
|   1 | SORT AGGREGATE |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- outline "MY_TEST_OUTLN" used for this statement

Statistics
----------------------------------------------------------
         34 recursive calls
        148 db block gets
         22 consistent gets
          0 physical reads
        540 redo size
        526 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          1 rows processed

关于OUTLINE的一些注意事项：

1，OUTLINE匹配SQL语句有点类似cusor_sharing参数中的similar，也就是说即使不同hash值的SQL语句也可能使用相同的OUTLINE，如：

复制代码代码如下:

SQL> ALTER SESSION SET USE_STORED_OUTLINES=test;

Session altered.

-- 使用不相同的SQL语句同样使用了和之前相同的OUTLINE
SQL> set autotrace traceonly
SQL> SELECT COUNT(*)FROM scott.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675

Note
-----
- outline "MY_TEST_OUTLN" used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          1 consistent gets
          0 physical reads
          0 redo size
        526 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

-- 查询v$sql可以看到两条语句是不同的
SQL> select sql_id,hash_value,child_number,sql_text from v$sql
2 where sql_text like '%scott.emp%';

SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ --------------------------------------------------
6xydpctfbwbm6 1555967590            0 select sql_id,hash_value,child_number,sql_text fro
                                      m v$sql where sql_text like '%scott.emp%'
40t73tu9dst5y 2463917246            0 select * from scott.emp where empno=7654
abj9tmfcs15bm 2575340915            0 select count(*) from scott.emp
d16cs4nzg9vmk 1056239218            0 SELECT COUNT(*)FROM scott.emp

2，DBMS_OUTLN.CREATE_OUTLINE中hash_value的是SQL语句的hash值，不是sql_id，也不是执行计划的hash_value。

3，DBMS_OUTLN.CREATE_OUTLINE无法像Create outline语句那样自定义outline的名称，这种方式创建的outline名称是系统自动生成的，需要可以手动使用alter outline语句来修改。

4，可以通过v$sql中的OUTLINE_SID和OUTLINE_CATEGORY字段查询到已经记录到大纲中的sql语句。

复制代码代码如下:

select sql_id,hash_value,child_number,OUTLINE_SID,OUTLINE_CATEGORY,sql_text from v$sql
where sql_text like '%scott.emp%'

SQL_ID        HASH_VALUE CHILD_NUMBER OUTLINE_SID OUTLINE_CA SQL_TEXT
------------- ---------- ------------ ----------- ---------- ---------------------------------------------
6xydpctfbwbm6 1555967590            0                        select sql_id,hash_value,child_number,sql_tex
                                                             t from v$sql where sql_text like '%scott.emp%'
40t73tu9dst5y 2463917246            0             DEFAULT    select * from scott.emp where empno=7654
abj9tmfcs15bm 2575340915            0             TEST       select count(*) from scott.emp
d16cs4nzg9vmk 1056239218            0             TEST       SELECT COUNT(*)FROM scott.emp

SQL Profile（SQL概要文件）

SQL Profile基本上相当于OUTLINE的升级版本，也是功能上最强大的，很多时候都是在使用SQL优化顾问（SQL Tuning Advisor，STA）才会接触到，同OUTLINE相同，SQL Profile同样由一系列hint组成，使用SQL Profile我们可以在SQL语句执行的同时后台应用这些hint从而达到维持执行计划稳定性的目的，事实上，相对OUTLINE它还具备一些特有的优势，比如允许概要文件通过忽略常量应用到多条SQL语句上，同时还可以将任意hint集合与指定的SQL语句结合起来！！

在使用SQL Profile的过程中，参数SQLTUNE_CATEGORY实现了和OUTLINE中的USER_STORED_OUTLINE参数一样的功能，于此同时，概要文件也会默认创建到DEFAULT分类中，通过为SQLTUNE_CATEGORY参数指定不同的分类名称来启用对应分类的SQL Profile；通常我们都是使用STA来创建概要文件，其实这些操作都直接间接的使用了DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程，调用签名如下：

复制代码代码如下:

PROCEDURE IMPORT_SQL_PROFILE

 Argument Name                  Type                    In/Out Default?

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

 SQL_TEXT                       CLOB                    IN

 PROFILE                        SQLPROF_ATTR            IN

 NAME                           VARCHAR2                IN     DEFAULT

 DESCRIPTION                    VARCHAR2                IN     DEFAULT

 CATEGORY                       VARCHAR2                IN     DEFAULT

 VALIDATE                       BOOLEAN                 IN     DEFAULT

 REPLACE                        BOOLEAN                 IN     DEFAULT

 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT

PROCEDURE IMPORT_SQL_PROFILE

 Argument Name                  Type                    In/Out Default?

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

 SQL_TEXT                       CLOB                    IN

 PROFILE_XML                    CLOB                    IN

 NAME                           VARCHAR2                IN     DEFAULT

 DESCRIPTION                    VARCHAR2                IN     DEFAULT

 CATEGORY                       VARCHAR2                IN     DEFAULT

 VALIDATE                       BOOLEAN                 IN     DEFAULT

 REPLACE                        BOOLEAN                 IN     DEFAULT

 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT

可以看到SQL Profile的创建是通过对SQL_TEXT指定hint集来完成的，并非OUTLINE中的HASH_VALUE，同时PROFILE字段的类型显示使用的SQLPROF_ATTR，PROFILE_XML字段也是需要通过获取V$SQL_PLAN视图的OTHER_XML字段来填充hint集的，可惜的是在官档中并没有提及这一概要文件的重要过程，因此无法详细了解它的使用细节，实际使用中还是建议使用STA来完成SQL Profile的创建。Kerry Osborne曾利用该过程来实现通过SQL_ID来创建SQL Profile，同时给出了利用IMPORT_SQL_PROFILE过程自定义hint集合来强制改变执行计划的解决方案【可以访问kerryosborne.oracle-guy.com获取详细信息】

基线（BASELINE）

BASELINE更像是一个性能的指标，oracle会通过基线来维护和消除系统的性能退化，基线的核心是一套具有特定名称并与特定语句相联系的hint，它可以像概要文件一样匹配SQL语句，虽然对计划的控制能力没有概要文件那么灵活，但它仍然是限制计划不稳定性的重要方法，下面是基线的一些特点：
1，基线中不存在分类category。
2，每个SQL语句可以有多个基线，比如固定基线集合。
3，基线保存了hint和执行计划的hash_value，因此优化器在判定是否采用基线时还需要验证是否有对应的计划存在。
4，可以通过设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为true来为每一条执行过的SQL语句自动创建基线，默认情况下不会创建基线。
5，通过查询视图DBA_SQL_PLAN_BASELINES可以获得已经创建的基线。
6，使用dbms_spm.load_plans_from_cursor_cache过程可以为一条缓存的SQL语句创建基线。
7，在11g中，默认会使用已经存在的基线维持执行计划的稳定性。

为指定SQL语句创建基线

复制代码代码如下:

-- 仍然使用OUTLINE中的示例查询
SQL> select * from scott.emp where empno=7654;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID 40t73tu9dst5y, child number 0
-------------------------------------
select * from scott.emp where empno=7654

Plan hash value: 2949544139

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7654)

19 rows selected.

-- 创建BASELINE，注意参数为SQL_ID和PLAN_HASH_VALUE
SQL> var ret number
SQL> exec :ret := dbms_spm.load_plans_from_cursor_cache(-
>     sql_id=>'&sql_id', -
>     plan_hash_value=>&plan_hash_value,-
>     fixed=>'&fixed');
Enter value for sql_id: 40t73tu9dst5y
Enter value for plan_hash_value: 2949544139
Enter value for fixed: NO

PL/SQL procedure successfully completed.

-- 再次运行查询可以发现在执行计划输出的Note中显示使用了基线SQL_PLAN_bmwra43zx42kr695cc014
SQL> set autotrace traceonly
SQL> select * from scott.emp where empno=7654;

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7654)

Note
-----
- SQL plan baseline "SQL_PLAN_bmwra43zx42kr695cc014" used for this statement

Statistics
----------------------------------------------------------
        747 recursive calls
         14 db block gets
        117 consistent gets
          0 physical reads
       2956 redo size
       1028 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed