ORACLE概要文件--sqlprofile(一） - 数据库编程

xmltype(od.comp_data),'/outline_data/hint'))) h 4 WHERE so.name = 'SYS_SQLPROF_01436cac2c3c0000' 5 AND so.signature = od.signature 6 AND so.category = od.category 7 AND so.obj_type = od.obj_type 8* AND so.plan_id = od.plan_id SQL> / SIGNATURE HINT -------------------------- ------------------------------------------------------------------------------------------ 3960696072677096522 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144) 3960696072677096522 OPTIMIZER_FEATURES_ENABLE(default)

让我们删除sql profile，重新创建并设置force_match为false，

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01436cac2c3c0000');

PL/SQL 过程已成功完成。

SQL> exec dbms_sqltune.accept_sql_profile(task_name=>'任务_309',task_owner=>user,replace=>true,force_match=>false);

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly explain
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' AND T1.object_id=t2.object_id;

执行计划
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation		     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	      |    36 |  1476 |   141	(1)| 00:00:02 |
|   1 |  NESTED LOOPS		     |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS		     |	      |    36 |  1476 |   141	(1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL	     | T1     |    36 |  1080 |    69	(2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN	     | T2_IDX |     1 |       |     1	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
	      NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_01436d001cfe0001" used for this statement

SQL>




 select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' AND T1.object_id=t2.object_id;

执行计划
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |  2500 |   100K|   370   (1)| 00:00:05 |
|*  1 |  HASH JOIN	   |	  |  2500 |   100K|   370   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    69   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 75308 |   808K|   300   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS
	      NOT NULL)

我们看到对于相似的sql语句，sql profile不会发生作用

在来看看关于signatrue的内容

SQL> select sql_text,to_char(exact_matching_signature),to_char(force_matching_signature) from v$sql where sql_text like 'select t1.*,t2.owner from t1,t2 %';

SQL_TEXT				 TO_CHAR(EXACT_MATCHING_SIGN

ORACLE概要文件--sqlprofile(一）(六)