dbms_sqltune.accept_sql_profile 中force_match的作用

dbms_sqltune.accept_sql_profile 中force_match的作用force_match的默认值为force，表示只有在sql文本完全一致的情况下才会应用sql_profile，这种情况下只要目标sql的sql文本发生一点改动，原来的profile将失去作用force_match的改为true，就相当于目标sql的where条件中的具体的输入值用绑定变量替换了14:03:16 scott@orcl> select /*+ full(t2)*/ * from t2 where empno=200;Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 2008370210--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=200)Note-----   - SQL profile "SYS_SQLPROF_014a6b5a4a4a0000" used for this statementStatistics----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size       1092  bytes sent via SQL*Net to client        519  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed14:03:21 scott@orcl> select /*+ full(t2)*/ * from t2 where empno=300;Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    39 |    15   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T2   |     1 |    39 |    15   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=300)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets         47  consistent gets          0  physical reads          0  redo size       1088  bytes sent via SQL*Net to client        519  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed可以看到，我们在将empno=200改成empno=300以后，oracle放弃应用sql_profile现在我们把force_match改成trueexecute dbms_sqltune.accept_sql_profile(task_name => 'test01',task_owner=> 'SCOTT',replace => TRUE,force_match=>true);现在我们再来执行看看14:17:07 scott@orcl> select /*+ full(t2)*/ * from t2 where empno=300;Elapsed: 00:00:00.02Execution Plan----------------------------------------------------------Plan hash value: 2008370210--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=300)Note-----   - SQL profile "SYS_SQLPROF_014a6b7e01950001" used for this statementStatistics----------------------------------------------------------         26  recursive calls          0  db block gets         30  consistent gets          1  physical reads          0  redo size       1092  bytes sent via SQL*Net to client        519  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          5  sorts (memory)          0  sorts (disk)          1  rows processed也能够顺利的使用sql_profile了