dbms_sqltune.accept_sql_profile 中force_match的作用

最新推荐文章于 2024-08-07 22:18:55 发布

bigclouder

最新推荐文章于 2024-08-07 22:18:55 发布

阅读量3.1k

点赞数

分类专栏： oracle 调优

本文链接：https://blog.csdn.net/kiwi_kid/article/details/44658267

版权

oracle 调优专栏收录该内容

20 篇文章 1 订阅

订阅专栏

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

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


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

14:03:21 scott@orcl> select /*+ full(t2)*/ * from t2 where empno=300;

Elapsed: 00:00:00.01

Execution 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改成true
execute 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.02

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


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