sql profile使用

r囧r小猫 2022-08-01 00:12 176阅读 0赞

在上一篇[《SQL Profiles-Part I》][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?  
答案是，正如上一篇中有朋友的[留言][Link 1]，使用DBMS\_SQLTUNE.IMPORT\_SQL\_PROFILE过程。

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> desc dbms\_sqltune  
2.  ...  
3.  PROCEDURE IMPORT\_SQL\_PROFILE  
4.  参数名称                       类型                    输入/输出默认值?  
5.  \------------------------------ ----------------------- ------ --------  
6.   SQL\_TEXT                       CLOB                    IN  
7.   PROFILE                        SQLPROF\_ATTR            IN  
8.   NAME                           VARCHAR2                IN     DEFAULT  
9.   DESCRIPTION                    VARCHAR2                IN     DEFAULT  
10.  CATEGORY                       VARCHAR2                IN     DEFAULT  
11.  VALIDATE                       BOOLEAN                 IN     DEFAULT  
12.  REPLACE                        BOOLEAN                 IN     DEFAULT  
13.  FORCE\_MATCH                    BOOLEAN                 IN     DEFAULT  
14. ...

这个过程其名字与实际功能有所差异，其实可以理解为CREATE OR REPLACE SQL\_PROFILE。过程中的PROFILE参数为SYS.SQLPROF\_ATTR，这种类型其实就是VARCHAR2的集合类型(COLLECTION)：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> select text from dba\_source where name='SQLPROF\_ATTR' and owner='SYS';  
2.    
3.  TYPE     sqlprof\_attr  
4.   AS VARRAY(2000) of VARCHAR2(500)

下面我们就用这个过程来创建SQL PROFILE：  
为避免干扰，将上一篇测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> exec dbms\_sqltune.drop\_sql\_profile('SYS\_SQLPROF\_014b39f084c88000');  
2.    
3.  PL/SQL 过程已成功完成。  
4.    
5.  SQL> exec dbms\_stats.set\_table\_stats('TEST1','T1',numrows=>49953);  
6.    
7.  PL/SQL 过程已成功完成。

现在我们手工创建一个SQL Profile：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> declare  
2.    2    v\_hints sys.sqlprof\_attr;  
3.    3  begin  
4.    4    v\_hints:=sys.sqlprof\_attr('USE\_NL(T1 T2)','INDEX(T2)');  
5.    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.    6                v\_hints,'SQLPROFILE\_NAME1',force\_match=>true);  
7.    7  end;  
8.    8  /  
9.    
10. PL/SQL 过程已成功完成。  
11.   
12. SQL> select attr\_val from dba\_sql\_profiles a, sys.sqlprof$attr b  
13.   2  where a.signature = b.signature  
14.   3  and a.name='SQLPROFILE\_NAME1';  
15.   
16. ATTR\_VAL  
17. \----------------------------------------  
18. USE\_NL(T1 T2)  
19. INDEX(T2)

下面执行SQL Profiles对应的SQL：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> select  t1.\*,t2.owner  
2.    2       from t1,t2  
3.    3       where t1.object\_name like '%T1%'  
4.    4       and t1.object\_id=t2.object\_id;  
5.    
6.  已选择29行。  
7.    
8.  执行计划  
9.  \----------------------------------------------------------  
10. Plan hash value: 1838229974  
11.   
12. \---------------------------------------------------------------------------  
13. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
14. \---------------------------------------------------------------------------  
15. |   0 | SELECT STATEMENT   |      |  2498 | 99920 |   219   (4)| 00:00:03 |  
16. |\*  1 |  HASH JOIN         |      |  2498 | 99920 |   219   (4)| 00:00:03 |  
17. |\*  2 |   TABLE ACCESS FULL| T1   |  2498 | 72442 |    59   (6)| 00:00:01 |  
18. |   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |  
19. \---------------------------------------------------------------------------  
20.   
21. Predicate Information (identified by operation id):  
22. \---------------------------------------------------  
23.   
24.    1 - access("T1"."OBJECT\_ID"="T2"."OBJECT\_ID")  
25.    2 - filter("T1"."OBJECT\_NAME" LIKE '%T1%')  
26.   
27. Note  
28. \-----  
29.    - SQL profile "SQLPROFILE\_NAME1" used for this statement  
30.   
31. 统计信息  
32. \----------------------------------------------------------  
33.           0  recursive calls  
34.           0  db block gets  
35.         933  consistent gets  
36.

可以看到，SQL使用了SQL Profile，不过没有达到我们预期的效果。

看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name”。这一次在执行IMPORT\_SQL\_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> declare  
2.    2    v\_hints sys.sqlprof\_attr;  
3.    3  begin  
4.    4    v\_hints:=sys.sqlprof\_attr('USE\_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');  
5.    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.    6                v\_hints,'SQLPROFILE\_NAME1',force\_match=>true,replace=>true);  
7.    7  end;  
8.    8  /  
9.    
10. PL/SQL 过程已成功完成。  
11.   
12. SQL> select attr\_val from dba\_sql\_profiles a, sys.sqlprof$attr b  
13.   2  where a.signature = b.signature  
14.   3  and a.name='SQLPROFILE\_NAME1';  
15.   
16. ATTR\_VAL  
17. \----------------------------------------  
18. USE\_NL(T1@SEL$1 T2@SEL$1)  
19. INDEX(T2@SEL$1)

再次执行下面的SQL：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> select  t1.\*,t2.owner  
2.    2       from t1,t2  
3.    3       where t1.object\_name like '%T1%'  
4.    4       and t1.object\_id=t2.object\_id;  
5.    
6.  已选择29行。  
7.    
8.  执行计划  
9.  \----------------------------------------------------------  
10. Plan hash value: 3787413387  
11.   
12. \--------------------------------------------------------------------------------------  
13. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
14. \--------------------------------------------------------------------------------------  
15. |   0 | SELECT STATEMENT            |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
16. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
17. |   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
18. |\*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |  
19. |\*  4 |    INDEX RANGE SCAN         | T2\_IDX |     1 |       |     1   (0)| 00:00:01 |  
20. \--------------------------------------------------------------------------------------  
21.   
22. Predicate Information (identified by operation id):  
23. \---------------------------------------------------  
24.   
25.    3 - filter("T1"."OBJECT\_NAME" LIKE '%T1%')  
26.    4 - access("T1"."OBJECT\_ID"="T2"."OBJECT\_ID")  
27.   
28. Note  
29. \-----  
30.    - SQL profile "SQLPROFILE\_NAME1" used for this statement  
31.   
32. 统计信息  
33. \----------------------------------------------------------  
34.           0  recursive calls  
35.           0  db block gets  
36.         294  consistent gets

这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。  
那么我们再一次手工修改T1表的统计信息，看看结果如何：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> exec dbms\_stats.set\_table\_stats('TEST1','T1',numrows=>5000000);  
2.    
3.  PL/SQL 过程已成功完成。  
4.    
5.  SQL> select  t1.\*,t2.owner  
6.    2       from t1,t2  
7.    3       where t1.object\_name like '%T1%'  
8.    4       and t1.object\_id=t2.object\_id;  
9.    
10. 已选择29行。  
11.   
12. 执行计划  
13. \----------------------------------------------------------  
14. Plan hash value: 3787413387  
15. \--------------------------------------------------------------------------------------  
16. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
17. \--------------------------------------------------------------------------------------  
18. |   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
19. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
20. |   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
21. |\*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
22. |\*  4 |    INDEX RANGE SCAN         | T2\_IDX |     1 |       |     1   (0)| 00:00:01 |  
23. \--------------------------------------------------------------------------------------  
24. Predicate Information (identified by operation id):  
25. \---------------------------------------------------  
26.    3 - filter("T1"."OBJECT\_NAME" LIKE '%T1%')  
27.    4 - access("T1"."OBJECT\_ID"="T2"."OBJECT\_ID")  
28.   
29. Note  
30. \-----  
31.    - SQL profile "SQLPROFILE\_NAME1" used for this statement  
32.   
33. 统计信息  
34. \----------------------------------------------------------  
35.           0  recursive calls  
36.           0  db block gets  
37.         294  consistent gets

可以看到，Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大，但是这里的执行计划仍然与未修改统计信息之前一样，使用range scan+ nested loop join。

通过以上的测试，我们明白了DBMS\_SQLTUNE.IMPORT\_SQL\_PROFILE的使用，同时也验证了这种方式的有效性，SQL Profiles能够像Outlines一样，能够稳定SQL的执行计划。

接下来我们需要完成两个任务。  
任务一：对现有的SQL稳定其执行计划。  
这里的问题是：稳定一条SQL语句的Hints从哪里来？简单的sql，没问题，我们可以手工构造，但是复杂的SQL，手工构造相对比较复杂，同时手工构 造的Hints不一定能够保证SQL的执行计划就会稳定。从10g开始，v$sql\_plan中就包括了SQL语句OUTLINE数据，也就是稳定执行计 划的Hints。从下面可以看到：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> select  t1.\*,t2.owner  
2.    2       from t1,t2  
3.    3       where t1.object\_name like '%T1%'  
4.    4       and t1.object\_id=t2.object\_id;  
5.    
6.  已选择29行。  
7.    
8.  SQL> select \* from table(dbms\_xplan.display\_cursor(null,null,'outline'));  
9.    
10. PLAN\_TABLE\_OUTPUT  
11. \----------------------------------------------------------------------------------------  
12. SQL\_ID  6m45w7r0xgdfj, child number 0  
13. \-------------------------------------  
14. select  t1.\*,t2.owner      from t1,t2      where t1.object\_name like '%T1%'  
15.    and t1.object\_id=t2.object\_id  
16.   
17. Plan hash value: 3787413387  
18.   
19. \--------------------------------------------------------------------------------------  
20. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
21. \--------------------------------------------------------------------------------------  
22. |   0 | SELECT STATEMENT            |        |       |       |  5061 (100)|          |  
23. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
24. |   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
25. |\*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |  
26. |\*  4 |    INDEX RANGE SCAN         | T2\_IDX |     1 |       |     1   (0)| 00:00:01 |  
27. \--------------------------------------------------------------------------------------  
28.   
29. Outline Data  
30. \-------------  
31.   
32.   /\*+  
33.       BEGIN\_OUTLINE\_DATA  
34.       IGNORE\_OPTIM\_EMBEDDED\_HINTS  
35.       OPTIMIZER\_FEATURES\_ENABLE('10.2.0.1')  
36.       ALL\_ROWS  
37.       OUTLINE\_LEAF(@"SEL$1")  
38.       FULL(@"SEL$1" "T1"@"SEL$1")  
39.       INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT\_ID"))  
40.       LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")  
41.       USE\_NL(@"SEL$1" "T2"@"SEL$1")  
42.       END\_OUTLINE\_DATA  
43.   \*/  
44.   
45. Predicate Information (identified by operation id):  
46. \---------------------------------------------------  
47.   
48.    3 - filter("T1"."OBJECT\_NAME" LIKE '%T1%')  
49.    4 - access("T1"."OBJECT\_ID"="T2"."OBJECT\_ID")  
50.   
51. Note  
52. \-----  
53.    - SQL profile "SQLPROFILE\_NAME1" used for this statement

上面所显示的“Outline Data”即是我们稳定SQL执行计划需要的Hints(我们甚至可以将这些Hints直接写到我们的SQL中）。对需要稳定执行计划的SQL，我们所要 做的就是如前面所示，将Hints与SQL文本一起创建一个SQL Profile。这里不得不提到一个SQL脚本，来自MOS。”SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly \[ID 215187.1\]“，在这篇文章中，可以下载到sqlt.zip，这个压缩文件内有一个文件：coe\_xfr\_sql\_profile.sql。这个脚 本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。下面是示例：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> select /\*+ proftest1 \*/ t1.\*,t2.owner  
2.    2       from t1,t2  
3.    3       where t1.object\_name like '%T1%'  
4.    4       and t1.object\_id=t2.object\_id;  
5.    
6.  \--在shared pool中查找刚刚执行的SQL，其ID为b4zvp712np1bp--  
7.    
8.  SQL> @coe\_xfr\_sql\_profile.sql  
9.    
10. Parameter 1:  
11. SQL\_ID (required)  
12.   
13. 输入 1 的值:  b4zvp712np1bp  
14.   
15. PLAN\_HASH\_VALUE AVG\_ET\_SECS  
16. \--------------- -----------  
17.      2959412835        .112  
18.   
19. Parameter 2:  
20. PLAN\_HASH\_VALUE (required)  
21.   
22. 输入 2 的值:  2959412835  
23.   
24. Values passed:  
25. ~~~~~~~~~~~~~  
26. SQL\_ID         : "b4zvp712np1bp"  
27. PLAN\_HASH\_VALUE: "2959412835"  
28.   
29. Execute coe\_xfr\_sql\_profile\_b4zvp712np1bp\_2959412835.sql  
30. on TARGET system in order to create a custom SQL Profile  
31. with plan 2959412835 linked to adjusted sql\_text.  
32.   
33. COE\_XFR\_SQL\_PROFILE completed.  
34. SQL>@coe\_xfr\_sql\_profile\_b4zvp712np1bp\_2959412835.sql  
35.   
36. PL/SQL 过程已成功完成。  
37.   
38. SQL>WHENEVER SQLERROR CONTINUE  
39. SQL>SET ECHO OFF;  
40.   
41.             SIGNATURE  
42. \---------------------  
43.   6058051510930011685  
44.   
45. ... manual custom SQL Profile has been created  
46.   
47. COE\_XFR\_SQL\_PROFILE\_b4zvp712np1bp\_2959412835 completed  
48.   
49. SQL>select attr\_val from sys.sqlprof$attr where signature=6058051510930011685;  
50.   
51. ATTR\_VAL  
52. \--------------------------------------------------  
53. BEGIN\_OUTLINE\_DATA  
54. IGNORE\_OPTIM\_EMBEDDED\_HINTS  
55. OPTIMIZER\_FEATURES\_ENABLE('10.2.0.1')  
56. ALL\_ROWS  
57. OUTLINE\_LEAF(@"SEL$1")  
58. FULL(@"SEL$1" "T2"@"SEL$1")  
59. FULL(@"SEL$1" "T1"@"SEL$1")  
60. LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")  
61. USE\_HASH(@"SEL$1" "T1"@"SEL$1")  
62. END\_OUTLINE\_DATA

coe\_xfr\_sql\_profile.sql这个脚本首先要求输入sql id，然后从shared pool、awr中获取sql执行的各个执行计划的统计信息（执行计划不稳定的SQL通常会有多个不同的执行计划），然后输入你认为是正确的、需要稳定的 执行计划的hash value，脚本就会生成另一个脚本，这里为coe\_xfr\_sql\_profile\_b4zvp712np1bp\_2959412835.sql，然后 运行这个脚本，就会创建出稳定执行计划所需要的SQL Profile，SQL Profile的名字为：coe+sql\_id+plan\_hash\_value，这里为coe\_b4zvp712np1bp\_2959412835。注 意，这里创建的SQL Profile，force match默认为FALSE，我们可以手工修改脚本将其改为TRUE，同时我们也可以按意愿来修改生成的脚本的其他内容。

除了上面提到的脚本，[http://kerryosborne.oracle-guy.com][http_kerryosborne.oracle-guy.com]这个BLOG里面也有许多与SQL Profiles相关的脚本。其中[create\_sql\_profile.sql][create_sql_profile.sql]可完成类似的功能，只不过功能相对简单，只能从shared pool中生成SQL Profile，因此也更方便。

任务二：在不能修改SQL的情况下改变并固定SQL的执行计划，即使原始的SQL使用了Hints。  
常常遇到这样的情况，SQL语句其执行计划有问题，或者是SQL使用了错误的Hints（比如 /\*+ RULE \*/）导致SQL性能较差，但是应用又不能修改或者时间内不能修改，那么我们怎么来改变SQL的执行计划呢。有3种办法，一种是调整统计信息，这个不建议 使用，因为比较复杂、不稳定可靠（统计信息可能会重新收集），影响面广（会影响其他访问此对象的SQL）。第二种是使用OUTLINE，这种方法比较复 杂。第三种就是我们今天要介绍的使用SQL Profiles了。

使用SQL Profiles来改变SQL的执行计划，其本质上就是使用Hints来改变SQL的执行计划。对于简单的SQL，我们同样可以像前面一样手工构造 Hints然后再使用DBMS\_SQLTUNE.IMPORT\_SQL\_PROFILE来实现。但是这种方法还是略显烦琐。那么通常的方法就是”乾坤大挪 移“了：

*  取得原始SQL的文本（如有可能还包括sql id)
 *  构造一个与原始SQL在逻辑上、结构上完全相同的SQL。这里强制逻辑上和结构上相同，SQL解析的用户名、SQL中引用对象的用户名甚至是一些predicate条件都可以不同。当然能够与原始SQL完全一样就更省事。
 *  执行我们构造的SQL，并取得构造的SQL的Outline Data。
 *  使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile。

下面我们来演示一下整个过程。我们这里要修改执行计划的SQL是：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  select /\*+ orig\_sql full(t1) full(t2) use\_hash(t1 t2) \*/ t1.\*,t2.owner     
2.       from t1,t2     
3.       where t1.object\_name like '%T1%'     
4.       and t1.object\_id=t2.object\_id;

我们首先需要执行这一条SQL，然后取得的SQL ID为gmvb9bp7f9kqd：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> select /\*+ orig\_sql full(t1) full(t2) use\_hash(t1 t2) \*/ t1.\*,t2.owner  
2.    2       from t1,t2  
3.    3       where t1.object\_name like '%T1%'  
4.    4       and t1.object\_id=t2.object\_id;  
5.    
6.  已选择29行。  
7.    
8.  执行计划  
9.  \----------------------------------------------------------  
10. Plan hash value: 2959412835  
11.   
12. \-----------------------------------------------------------------------------------  
13. | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
14. \-----------------------------------------------------------------------------------  
15. |   0 | SELECT STATEMENT   |      |   250K|  9765K|       |   998  (25)| 00:00:12 |  
16. |\*  1 |  HASH JOIN         |      |   250K|  9765K|  1128K|   998  (25)| 00:00:12 |  
17. |   2 |   TABLE ACCESS FULL| T2   | 49954 |   536K|       |   159   (2)| 00:00:02 |  
18. |\*  3 |   TABLE ACCESS FULL| T1   |   250K|  7080K|       |   288  (81)| 00:00:04 |  
19. \-----------------------------------------------------------------------------------

然后我们构造一条SQL，让这条SQL按我们希望的执行计划运行，构造的SQL其ID为cymak300cycmd：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL> select /\*+ modify\_sql index(t1) use\_nl(t1 t2) \*/ t1.\*,t2.owner  
2.    2       from t1,t2  
3.    3       where t1.object\_name like '%T1%'  
4.    4       and t1.object\_id=t2.object\_id;  
5.    
6.  已选择29行。  
7.    
8.  执行计划  
9.  \----------------------------------------------------------  
10. Plan hash value: 3787413387  
11.   
12. \--------------------------------------------------------------------------------------  
13. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
14. \--------------------------------------------------------------------------------------  
15. |   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
16. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
17. |   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
18. |\*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
19. |\*  4 |    INDEX RANGE SCAN         | T2\_IDX |     1 |       |     1   (0)| 00:00:01 |  
20. \--------------------------------------------------------------------------------------  
21.   
22. Predicate Information (identified by operation id):  
23. \---------------------------------------------------  
24.   
25.    3 - filter("T1"."OBJECT\_NAME" LIKE '%T1%')  
26.    4 - access("T1"."OBJECT\_ID"="T2"."OBJECT\_ID")

然后使用coe\_xfr\_sql\_profile.sql脚本来提取我们构造的SQL的Outline Data，生成的结果为coe\_xfr\_sql\_profile\_cymak300cycmd\_3787413387.sql，打开结果文件，可以看到有这么一段：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  h := SYS.SQLPROF\_ATTR(  
2.  q'\[BEGIN\_OUTLINE\_DATA\]',  
3.  q'\[IGNORE\_OPTIM\_EMBEDDED\_HINTS\]',  
4.  q'\[OPTIMIZER\_FEATURES\_ENABLE('10.2.0.1')\]',  
5.  q'\[ALL\_ROWS\]',  
6.  q'\[OUTLINE\_LEAF(@"SEL$1")\]',  
7.  q'\[FULL(@"SEL$1" "T1"@"SEL$1")\]',  
8.  q'\[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT\_ID"))\]',  
9.  q'\[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")\]',  
10. q'\[USE\_NL(@"SEL$1" "T2"@"SEL$1")\]',  
11. q'\[END\_OUTLINE\_DATA\]');

再针对gmvb9bp7f9kqd使用coe\_xfr\_sql\_profile.sql，生成的结果文件为 coe\_xfr\_sql\_profile\_gmvb9bp7f9kqd\_2959412835.sql。手工修改这个文件，将里面h := SYS.SQLPROF\_ATTR…那一段替换成我们之前得到的那一段。这一次我们将这个文件中的force\_match从FALSE改成TRUE。  
最后我们运行coe\_xfr\_sql\_profile\_gmvb9bp7f9kqd\_2959412835.sql这个脚本文件：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL>@coe\_xfr\_sql\_profile\_gmvb9bp7f9kqd\_2959412835.sql  
2.    
3.  PL/SQL 过程已成功完成。  
4.    
5.  SQL>WHENEVER SQLERROR CONTINUE  
6.  SQL>SET ECHO OFF;  
7.    
8.              SIGNATURE  
9.  \---------------------  
10.  15409905709853673912  
11.   
12. ... manual custom SQL Profile has been created  
13.   
14. COE\_XFR\_SQL\_PROFILE\_gmvb9bp7f9kqd\_2959412835 completed

这样就完成了我们所需要的SQL Profile的创建。下面再看看原来的SQL执行情况（这里我故意将like条件改了一下，以查看force match是否起作用）：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  SQL>select /\*+ orig\_sql full(t1) full(t2) use\_hash(t1 t2) \*/ t1.\*,t2.owner  
2.    2       from t1,t2  
3.    3       where t1.object\_name like '%T2%'  
4.    4       and t1.object\_id=t2.object\_id;  
5.    
6.  已选择77行。  
7.    
8.  执行计划  
9.  \----------------------------------------------------------  
10. Plan hash value: 3787413387  
11.   
12. \--------------------------------------------------------------------------------------  
13. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
14. \--------------------------------------------------------------------------------------  
15. |   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
16. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
17. |   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
18. |\*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
19. |\*  4 |    INDEX RANGE SCAN         | T2\_IDX |     1 |       |     1   (0)| 00:00:01 |  
20. \--------------------------------------------------------------------------------------  
21.   
22. Predicate Information (identified by operation id):  
23. \---------------------------------------------------  
24.   
25.    3 - filter("T1"."OBJECT\_NAME" LIKE '%T2%')  
26.    4 - access("T1"."OBJECT\_ID"="T2"."OBJECT\_ID")  
27.   
28. Note  
29. \-----  
30.    - SQL profile "coe\_gmvb9bp7f9kqd\_2959412835" used for this statement  
31.   
32. 统计信息  
33. \----------------------------------------------------------  
34.           0  recursive calls  
35.           0  db block gets  
36.         363  consistent gets

可以看到SQL Profile起作用了。

最后一步，生成SQL Profile时稍显复杂，不过我们可以修改之前提到的create\_sql\_profile.sql文件来达到同样的目的，只不过前几个步骤仍然是不可省略的。将里面的代码：

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  select extractvalue(value(d), '/hint') as outline\_hints bulk collect  
2.    into ar\_profile\_hints  
3.    from xmltable('/\*/outline\_data/hint' passing  
4.                  (select xmltype(other\_xml) as xmlval  
5.                     from v$sql\_plan  
6.                    where sql\_id = '&&sql\_id'  
7.                      and child\_number = &&child\_no  
8.                      and other\_xml is not null)) d;

改为

[view plain][] [copy to clipboard][view plain] [print][view plain] [?][view plain]

1.  select extractvalue(value(d), '/hint') as outline\_hints bulk collect  
2.    into ar\_profile\_hints  
3.    from xmltable('/\*/outline\_data/hint' passing  
4.                  (select xmltype(other\_xml) as xmlval  
5.                     from v$sql\_plan  
6.                    where sql\_id = '&&modi\_sql\_id'  
7.                      and child\_number = &&modi\_child\_no  
8.                      and other\_xml is not null)) d;

注意这里modi\_sql\_id和modi\_child\_no为我们构造的SQL执行后的id及child\_number。同时这2个变量在文件前面需要定义，此处不再细述。

小结：本文承接上一篇，介绍了如何利用SQL Profile来稳定执行计划；如何利用SQL Profile来改变SQL的执行计划。对于SQL Profiles来说，不属于任何一个用户，比Outlines更具有操控性灵活性。对于SQL Profiles的category，这里不做介绍，有兴趣的朋友请参考文档。

[SQL Profiles-Part I]: http://www.laoxiong.net/sql-profiles-part.html
[Link 1]: http://www.laoxiong.net/sql-profiles-part.html#comment-1071
[view plain]: http://www.laoxiong.net/sql-profiles-partii.html#
[http_kerryosborne.oracle-guy.com]: http://kerryosborne.oracle-guy.com/
[create_sql_profile.sql]: http://kerryosborne.oracle-guy.com/.s/create_sql_profile.sql