我们都希望Oracle数据库的执行的SQL，CBO都能够产生正确的执行计划，但是事实上由于各种原因（例如SQL所对应的对应的统计信息不准确，或者CBO内部一些计算公式的缺陷等），导致了CBO会产生效率不高的，甚至是错误的执行计划。特别是CBO对目标SQL所产生的初始执行计划是正确的，后来由于各种原因（比如统计信息的变更），导致了CBO重新产生了一个错误的执行计划，这种执行计划的改变往往会导致目标SQL执行时间呈一个数量级的递增，而且通常会给我们造成一个困惑，一条SQL原本可以正常的运行，但是为什么会突然变得很慢？其实这种SQL执行效率突然的衰减往往是因为目标SQL执行计划的改变。这时候我们可以使用SQL_Profile或者SPM来解决执行计划变更的问题，用他们来调整稳定目标的SQL执行计划。下面进行一个Automatic的SQL Profile来稳定执行计划的实验。
1.创建一个测试表并插入数据，并创建相对应的索引
SQL> create table t1(n number);
Table created.
SQL> declare
2 begin
3 for i in 1 .. 10000
4 loop
5 insert into t1 values(i);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
COUNT(*)
----------
10000
SQL> create index idx_t1 on t1(n);
Index created.
2.对表T1收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname =>'SYS',tabname =>'T1',method_opt =>'for all columns size 1',CASCADE =>true);
PL/SQL procedure successfully completed.
3.使用hint强制不使用索引，来模拟那些执行计划错误的SQL，并查看执行计划。
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1kg76709mx29d, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - filter("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
42 rows selected.
从上面的内容我们不难发现，这条sql语句所走的是全表扫描，但是这显然是个错误的执行计划，正确的执行计划，我们应该是走索引。
我们现在使用SQL Tuning Advisor来尝试对这条SQL进行通过产生Automatic类型的SQL Profile
4. 创建一个名为my_sql_tuning_task2的自动调整任务
SQL> declare
2 my_task_name varchar2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext :='select /*+ no_index(t1 idx_t1) */ * from t1 where n=1';
6 my_task_name := dbms_sqltune.create_tuning_task(
7 sql_text => my_sqltext,
8 user_name => 'SYS',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'my_sql_tuning_task_2',
12 description =>'TASK to tune a query on table t1');
13 END;
14 /
PL/SQL procedure successfully completed.
然后执行上述自动调整任务
SQL> begin
2 dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_2');
3 end;
4 /
PL/SQL procedure successfully completed.