十二
30
在上一篇《SQL Profiles-Part I》，我向大家介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天，将向大家介绍如何手工创建SQL Profiles（即不通过SQL Tuning Advisor)来达成2个目的：
- 锁定或者说稳定SQL执行计划。
- 在不能修改应用的SQL的情况下，来改变或者说是强制使SQL使用我们指定的执行计划，即使原始的SQL包含了Hints。
那么，这里最关键的一点是，如何来手工创建SQL Profiles?
答案是，正如上一篇中有朋友的留言，使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。
SQL> desc dbms_sqltune ... PROCEDURE IMPORT_SQL_PROFILE 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- SQL_TEXT CLOB IN PROFILE SQLPROF_ATTR IN NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULT CATEGORY VARCHAR2 IN DEFAULT VALIDATE BOOLEAN IN DEFAULT REPLACE BOOLEAN IN DEFAULT FORCE_MATCH BOOLEAN IN DEFAULT ...
这个过程其名字与实际功能有所差异，其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR，这种类型其实就是VARCHAR2的集合类型(COLLECTION)：
SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS'; TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)
下面我们就用这个过程来创建SQL PROFILE：
为避免干扰，将上一篇测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>49953);
PL/SQL 过程已成功完成。
现在我们手工创建一个SQL Profile：
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr('USE_NL(T1 T2)','INDEX(T2)');
5 dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
6 v_hints,'SQLPROFILE_NAME1',force_match=>true);
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
2 where a.signature = b.signature
3 and a.name='SQLPROFILE_NAME1';
ATTR_VAL
----------------------------------------
USE_NL(T1 T2)
INDEX(T2)
下面执行SQL Profiles对应的SQL：
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
已选择29行。
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 99920 | 219 (4)| 00:00:03 |
|* 1 | HASH JOIN | | 2498 | 99920 | 219 (4)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 2498 | 72442 | 59 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
Note
-----
- SQL profile "SQLPROFILE_NAME1" used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
933 consistent gets
可以看到，SQL使用了SQL Profile，不过没有达到我们预期的效果。
看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name"。这一次在执行IMPORT_SQL_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile：
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');
5 dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
6 v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
2 where a.signature = b.signature
3 and a.name='SQLPROFILE_NAME1';
ATTR_VAL
----------------------------------------
USE_NL(T1@SEL$1 T2@SEL$1)
INDEX(T2@SEL$1)
再次执行下面的SQL：
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
已选择29行。
执行计划
----------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 99920 | 5061 (1)| 00:01:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2498 | 99920 | 5061 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 72442 | 59 (6)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SQLPROFILE_NAME1" used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
294 consistent gets
这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。 Read the rest of this entry
那么我们再一次手工修改T1表的统计信息，看看结果如何：