sql profile使用

最新推荐文章于 2021-02-19 23:48:23 发布
最新推荐文章于 2021-02-19 23:48:23 发布
阅读量711 收藏
点赞数
分类专栏： 工具

在上一篇《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过程。

view plain copy to clipboard print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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这个BLOG里面也有许多与SQL Profiles相关的脚本。其中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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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 print ?
  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，这里不做介绍，有兴趣的朋友请参考文档。

coe_load_sql_profile.sql
08-20
oracle sqlt工具脚本，用来使用sql_profile绑定内存中已有的执行计划
使用SQL Profile进行SQL优化案例
12-14
3. **应用SQL Profile**：一旦生成了Profile，可以使用`DBMS_SQLTUNE接受`或`APPLY`过程将其应用到SQL语句上。这将修改数据库的优化器，使其在执行该SQL时遵循Profile中的优化策略。 4. **验证效果**：应用Profile...
SQL PROFILE的用法
weixin_33885253的博客
02-05 210
一般只需要步骤三、四就可以完成执行计划的修改和固定，而outline和baseline则需要N多个步骤。 SQL PROFILE使用简单,不区分大小写，回车，空格，但是对DBA写HINT的能力要求比较高，因为SQL PROFILE要求HINT必须写明查询块名, SQL PROFILE还有其他一些牛逼的特性。 我的示例里教了大家偷懒的做法，但是有时间我们还是最好认真把...
SQL Profile介绍
数据库天地
11-13 607
什么是SQL Profile SQL Profile在性能优化中占有一个重要的位置。 MOS里这么描述SQL Profile： SQL Profile是10g中的新特性，作为自动SQL调整过程的一部分，由Oracle企业管理器来管理。除了OEM，SQL Profile可以通过DBMS_SQLTUNE包来进行管理。 查询优化器有时候会因为缺乏足...
sql_profile的使用
jamesge的博客
02-19 1277
今天看了老熊关于sql_profile的讲解，受益匪浅，自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。准备的数据如下，创建两个表，一个大，一个小，然后做表分析 SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000; Table created. SQL> create table t2 as select * from dba_obj.
sql profile使用说明
zuixinnet的专栏
08-14 1161
sql profile实验： create table binbin (id number); insert into binbin values('1'); commit; begin for i in 1..1000 loop insert into binbin values('2'); end loop commit; end; SQL>  begin   2  f
sqlprofile
kennyblues的博客
02-25 247
https://blog.csdn.net/launch_225/article/details/23843745?utm_source=blogxgwz3 https://www.cnblogs.com/ayumie/p/6882404.html --spm
Oracle固定执行计划之SQL PROFILE概要文件详解
09-09
SQL PROFILE适用于多种SQL语句类型，包括SELECT、UPDATE、INSERT（当使用SELECT时）、DELETE、CREATE（当使用SELECT时）和MERGE（作为UPDATE和INSERT操作时）。 在实际应用中，创建和应用SQL PROFILE需要相应的系统...
使用SQL PROFILE固定SHARED POOL中的执行计划
08-03
2. **手动创建**：使用`DBMS_SQLTUNE`包的`CREATE_SQL_PROFILE`过程，DBA可以手工创建SQL Profile，指定所需的执行计划。 3. **脚本生成**：例如，提供的`coe_xfr_sql_profile.sql`脚本可能包含一系列SQL Profile的...
Sqlserver2008时间探查器SqlServer Profile及工具的使用
09-28
SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI(美国国家标准协会)的规定，SQL被作为关系型数据库管理系统的标准语言。SQL ...
SQL Profile的使用
文档搬运工
06-04 409
## 2020-06-04 测试 SQL PROFILE 。 ## 步骤 ## 1 创建SQL Tuning Task ## 2 执行SQL Tuning Task ## 3 查看SQL Tuning进度 ## 4 查看SQL Tuning结果 ## 5 接受SQL Tuning给的SQL PROFILE建议 --创建测试表t1 conn hr/hr create table t1 (n number); declare begin for i in 1..10000 loop inser
mysql sqlprofile_MySQLSQL剖析(SQLprofile)
weixin_39548606的博客
01-19 165
分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优...
SQL Profile 总结（一）
oradh的专栏
06-10 6499
在介绍SQL Profile之前，不得不说的一个工具就是SQL Tuning Advisor；这个工具是从Oracle 10g开始引入，它的任务就是分析一个指定的SQL语句，并建议如何使用一些方法来提高指定语句的性能，例如：收集缺失的对象统计信息、或者收集过时的对象统计信息、创建新的索引、调整SQL语句结构、采用SQL Profile等等方式。
SQL PROFILE
rgb_rgb的博客
12-23 6065
参考： http://www.360doc.com/content/13/1223/00/15203274_339380234.shtml http://www.laoxiong.net/sql-profiles-partii.html http://blog.chinaunix.net/uid-20185205-id-3053875.html 我们经常会碰到一些线上的SQL问
SQL PROFILES
翔之天空的专栏
10-27 955
本次演示sql profile 的基本用法， 平台基于rhel6 +oracle11.2 版本   第一节建立测试用例 第二节查看sql的执行计划，本次所有试验也是通过sql profile 来使得第二节1部分的sql执行计划变成第二节2部分的执行计划 第三节是通过sql tuning advisor 生成sql profile ，使得sql的执行计划改变 第四节是通过sql tunin
Sql profiles用法介绍
老徐的博客只有干货
07-08 2404
DBMS_SQLTUNE优化SQL 是在oracle10g才出来的新特性，使用它能很大程度上方便对sql的分析和优化。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限： grant advisor to user; 使用DBMS_SQLTUNE包进行SQL优化，大致可以分为四个步骤： 创建优化任务 执行优化任务 显示优化任务的结果 按照建议执行优化 以下以sys
sqlserver 2000 profile
最新发布
12-27
使用SQL Server 2000 Profile，用户可以创建和管理事件跟踪会话，以便跟踪数据库服务器上发生的各种事件。用户可以选择跟踪特定的数据库对象，如表、存储过程、触发器等，还可以指定要跟踪的事件类型，如查询执行、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值