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

----------------------------------------------- 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_01436cac2c3c0000" used for this statement 看来sql profile发挥作用了，从执行计划和note部分都可以看出。在来看一下相似的语句

SQL>  select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' 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 '%T2%' AND "T1"."OBJECT_NAME" IS NOT
	      NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

sql profile同样发挥了作用，这是为什么那？

大家可能注意到accept_sql_profile中的一个参数force_match,对！正是这个参数导致sql profile可以影响相似的sql语句。在继续下面的讨论之前，先来看一下v$sql中的两个字段

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_SIGNATURE)	  TO_CHAR(FORCE_MATCHING_SIGNATURE)
---------------------------------------- ---------------------------------------- ----------------------------------------
 select t1.*,t2.owner from t1,t2 where t 16954193673788994662			  3960696072677096522
1.object_name like '%T2%' AND T1.object_
id=t2.object_id

 select t1.*,t2.owner from t1,t2 where t 8975541025552400288			  3960696072677096522
1.object_name like '%T1%' AND T1.object_
id=t2.object_id

然后我们看一下sqlprofile到底存储了什么内容？

SQL> l
  1  SELECT so.signature,extractValue(value(h),'.') AS hint
  2  FROM sys.sqlobj$data od, sys.sqlobj$ so,
  3  table(xmlsequence(extract(

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