测试目的，当有hint时，并且hint跟需要绑定的执行计划有冲突，谁的优先级高。
.
固定执行计划-使用SQL Tuning Advisor
固定执行计划-手工指定PLAN OUTLINE
固定执行计划-手工指定索引名称的方式
固定执行计划-使用coe_xfr_sql_profile固定执行计划
固定执行计划-使用SPM（Sql Plan Management）固定执行计划
.
这里是第一个测试，使用SQL Tuning Advisor来测试：
创建测试用例：
LUNAR@lunardb>create table lunartest1 (n number ); Table created. Elapsed: 00:00:00.08 LUNAR@lunardb>begin 2 for i in 1 .. 10000 loop 3 insert into lunartest1 values(i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:02.05 LUNAR@lunardb>create index idx_lunartest1_n on lunartest1(n); Index created. Elapsed: 00:00:00.04
执行查询，我们看到sql按照hint的方式没有使用索引，而是全表扫描，这是我们预期的结果：
LUNAR@lunardb>set autotrace on
LUNAR@lunardb>select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1;
N
----------------
1
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1172089107
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LUNARTEST1 | 1 | 13 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunardb>
下面我们运行SQL Tuning Advisor来生成建议报告：
LUNAR@lunardb>DECLARE 2 my_task_name VARCHAR2(50); 3 BEGIN 4 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 5 SQL_ID => 'bjgduva68mbqm', 6 scope => 'COMPREHENSIVE', 7 time_limit => 60, 8 task_name => 'Lunar_tunning_bjgduva68mbqm', 9 description => 'Task to tune a query on bjgduva68mbqm by Lunar'); 10 dbms_sqltune.execute_tuning_task(my_task_name); 11 dbms_output.put_line(my_task_name); 12 END; 13 / Lunar_tunning_bjgduva68mbqm PL/SQL procedure successfully completed. Elapsed: 00:00:00.20 LUNAR@lunardb>
查看生成的报告内容：
LUNAR@lunardb>SELECT dbms_sqltune.report_tuning_task('&task_name') FROM dual;
Enter value for task_name: Lunar_tunning_bjgduva68mbqm
old 1: SELECT dbms_sqltune.report_tuning_task('&task_name') FROM dual
new 1: SELECT dbms_sqltune.report_tuning_task('Lunar_tunning_bjgduva68mbqm') FROM dual
DBMS_SQLTUNE.REPORT_TUNING_TASK('LUNAR_TUNNING_BJGDUVA68MBQM')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : Lunar_tunning_bjgduva68mbqm
Tuning Task Owner : LUNAR
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 01/12/2016 10:09:54
Completed at : 01/12/2016 10:09:54
-------------------------------------------------------------------------------
Schema Name: LUNAR
SQL ID : bjgduva68mbqm
SQL Text : select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from
lunartest1 where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "LUNAR"."LUNARTEST1" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'LUNAR', tabname =>
'LUNARTEST1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 91.31%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'Lunar_tunning_bjgduva68mbqm', task_owner => 'LUNAR', replace =>
TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000196 .00002 89.79 %
CPU Time (s): .0002 0 100 %
User I/O Time (s): 0 0
Buffer Gets: 23 2 91.3 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1172089107
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LUNARTEST1 | 1 | 3 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
2- Using SQL Profile
--------------------
Plan hash value: 3241900148
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_LUNARTEST1_N | 1 | 3 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
-------------------------------------------------------------------------------
Elapsed: 00:00:00.06
LUNAR@lunardb>
这里我们看到SQL Tuning Advisor提示了两个建议：
1，收集统计信息：
execute dbms_stats.gather_table_stats(ownname => 'LUNAR', tabname =>
'LUNARTEST1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
1
.
2，提供了一个执行计划建议：
1
execute dbms_sqltune.accept_sql_profile(task_name =>
'Lunar_tunning_bjgduva68mbqm', task_owner => 'LUNAR', replace =>
TRUE);
并且给出了这个执行计划和原始执行计划的对比，可以看到 执行效率提高了89%以上，逻辑读从23降低为2，减少了91.3%。
.
下面我们按照建议执行。
首先收集统计信息：
LUNAR@lunardb>execute dbms_stats.gather_table_stats(ownname => 'LUNAR', tabname =>'LUNARTEST1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.12 LUNAR@lunardb>
然后我们接受建议中的执行计划：
LUNAR@lunardb>execute dbms_sqltune.accept_sql_profile(task_name => 'Lunar_tunning_bjgduva68mbqm', task_owner => 'LUNAR', replace =>TRUE); PL/SQL procedure successfully completed. Elapsed: 00:00:00.33 LUNAR@lunardb>
现在，再次查询看看效果：
LUNAR@lunardb>select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1;
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 3241900148
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_LUNARTEST1_N | 1 | 3 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
Note
-----
- SQL profile "SYS_SQLPROF_015236655fb80000" used for this statement
Statistics
----------------------------------------------------------
37 recursive calls
0 db block gets
14 consistent gets
1 physical reads
0 redo size
519 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
这里我们看到，这个执行计划中已经使用了索引，并且逻辑读从49降低为14,
但是这里还有物理读，因此，我们再次执行看看：
LUNAR@lunardb>/
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3241900148
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_LUNARTEST1_N | 1 | 3 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
Note
-----
- SQL profile "SYS_SQLPROF_015236655fb80000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunardb>
逻辑读从14降低为3，这个执行计划已经是我们需要的。
现在我们查看一下这个SQL Profile的OUTLINE：
LUNAR@lunardb>SELECT extractValue(value(h),'.') AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
4 WHERE so.name = 'SYS_SQLPROF_015236655fb80000'
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;
HINT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_STATS("LUNAR"."LUNARTEST1", "N", scale, length=3 distinct=10000 nulls=0 min=1 max=10000)
TABLE_STATS("LUNAR"."LUNARTEST1", scale, blocks=20 rows=10000)
OPTIMIZER_FEATURES_ENABLE(default)
IGNORE_OPTIM_EMBEDDED_HINTS
Elapsed: 00:00:00.05
LUNAR@lunardb>
这里我们看到该SQP Profile中提供了详细的表和列的统计信息
并且有“IGNORE_OPTIM_EMBEDDED_HINTS”，也就是忽略嵌入到SQL中的hint
结论：
虽然这个SQL的hint中指定了no index，即不使用索引，但是sql语句仍然按照SYS_SQLPROF_015236655fb80000指定的profile使用了index。
说明dbms_sqltune.accept_sql_profile方式绑定的执行计划优先级高于hint指定是否使用索引的方式。
不过不代表所有hint的优先级都低，还需要测试更多其他hint……
