您现在的位置是：网站首页> 编程资料编程资料
Oracle中基于hint的3种执行计划控制方法详细介绍_oracle_
2023-05-27
615人已围观
简介 Oracle中基于hint的3种执行计划控制方法详细介绍_oracle_
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$;
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
-- 设置使用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，如：<
相关内容
- Oracle中的半联结和反联结详解_oracle_
- Oracle的RBO和CBO详细介绍和优化模式设置方法_oracle_
- Oracle中使用Rownum分页详细例子_oracle_
- Oracle CBO几种基本的查询转换详解_oracle_
- Oracle 11g Dataguard参数详解_oracle_
- Oracle中插入特殊字符:&和'的解决方法汇总_oracle_
- Oracle阻塞(blockingblocked)实例详解_oracle_
- Oracle监听口令及监听器安全详解_oracle_
- Oracle数据库TNS常见错误的解决方法汇总_oracle_
- Oracle监听器服务不能启动的解决方法_oracle_
