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"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
42 rows selected.
我们发现还是走了全表扫描，要想使上面的SQL_PROFILE 生效，我们需要加上FORCE_MATCH=TRUE,true的含义，就是where条件中值发生变化，但是SQL_Profile仍然有效
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE,force_match => true);
PL/SQL procedure successfully completed.
再次查看相对应的执行计划
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 c4j6hxkqudj1s, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
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"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
Note
-----
PLAN_TABLE_OUTPUT
---------------------------------------------
- SQL profile SYS_SQLPROF_015410470fa40001 used for this statement
46 rows selected.
这是我们可以发现这次的执行计划走的是索引，为了再次验证新生成的SQL_Profile对其他值也有效，我们再次尝试n=3
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=3;
N
----------
3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0zz8t0qnm15hj, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=3
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 |