Automatic的SQL Profile来稳定执行计划(3)

从上面的调整结果，我们可以看到，他已经为我们目标SQL找到了更好的执行计划，并且也完成了针对该SQL的Automatic类型的SQL Profile，如果我们使用    execute dbms_sqltune.accept_sql_profile(task_name =>  'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
相应时间将会有89.9%的改善，逻辑读将会有 90.9 %的改善，并且接受后将会有全表扫描改变为IDX_T1的索引范围扫描。

然后我们按照oracle提示接受这个SQL profile，并重新查看执行计划
SQL>  execute dbms_sqltune.accept_sql_profile(task_name =>  'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);

PL/SQL procedure successfully completed.

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: 1369807930

---------------------------------------------------------------------------
 | Id  | Operation | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
 ---------------------------------------------------------------------------
 |  0 | SELECT STATEMENT |  |  |  | 1 (100)|  |
 |*  1 |  INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1  (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")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
 ------------------------------------------------

1 - access("N"=1)

Column Projection Information (identified by operation id):
 -----------------------------------------------------------

1 - "N"[NUMBER,22]

Note
 -----

PLAN_TABLE_OUTPUT
 -------------------------------------------------
    - SQL profile SYS_SQLPROF_0154103a51870000 used for this statement

46 rows selected.

我们可以看到Note部分SQL profile SYS_SQLPROF_0154103a51870000 used for this statement，这说明我们刚才接受的SQL Profile已经生效了，这同时也说明Automatic类型的SQL Profile确实可以再不改变目标SQL的SQl文本的情况下更改其执行计划

接下来我们尝试将where的条件从n=1改变为n=2,并查看执行计划 SQL>  select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;

N
 ----------
 2

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

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID 36wrvgrswajnh, child number 0
 -------------------------------------
  select /*+ no_index(t1 idx_t1) */ * from t1 where n=2

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
 -------------

/*+