使用sql profile固定执行计划实验
10g之前有outlines,10g之后sql profile作为新特性之一出现。
如果针对非绑定变量的sql,outlines则力不从心。

下面是实验过程

 
  
  1. --1.准备阶段 
  2. SQL> select * from v$version; 
  3.  
  4. BANNER 
  5. ---------------------------------------------------------------- 
  6. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
  7. PL/SQL Release 10.2.0.1.0 - Production 
  8. CORE    10.2.0.1.0      Production 
  9. TNS for 32-bit Windows: Version 10.2.0.1.0 - Production 
  10. NLSRTL Version 10.2.0.1.0 - Production 
  11.  
  12. SQL> create table test_raugher as select * from dba_objects; 
  13.  
  14. 表已创建。 
  15.  
  16. SQL> create index ind_objectid on test_raugher(object_id); 
  17.  
  18. 索引已创建。 
  19.  
  20. SQL> select object_id from test_raugher where rownum<2; 
  21.  
  22.  OBJECT_ID 
  23. ---------- 
  24.         20 
  25.  
  26. SQL> exec dbms_stats.gather_table_stats(user,'TEST_RAUGHER',cascade=>true); 
  27.  
  28. PL/SQL 过程已成功完成。 
  29.  
  30. --原sql执行计划 
  31. SQL> set autot trace explain 
  32. SQL> select * from test_raugher where object_id=20; 
  33.  
  34. 执行计划 
  35. ---------------------------------------------------------- 
  36. Plan hash value: 800879874 
  37.  
  38. -------------------------------------------------------------------------------------------- 
  39. | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
  40. -------------------------------------------------------------------------------------------- 
  41. |   0 | SELECT STATEMENT            |              |     1 |    95 |     2   (0)| 00:00:01 | 
  42. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_RAUGHER |     1 |    95 |     2   (0)| 00:00:01 | 
  43. |*  2 |   INDEX RANGE SCAN          | IND_OBJECTID |     1 |       |     1   (0)| 00:00:01 | 
  44. -------------------------------------------------------------------------------------------- 
  45.  
  46. Predicate Information (identified by operation id): 
  47. --------------------------------------------------- 
  48.  
  49.    2 - access("OBJECT_ID"=20) 
  50.  
  51. SQL> 
  52. --新sql执行计划 
  53. SQL> select /*+ full(test_raugher) */ * from test_raugher where object_id=20; 
  54.  
  55. 执行计划 
  56. ---------------------------------------------------------- 
  57. Plan hash value: 3725671026 
  58.  
  59. ---------------------------------------------------------------------------------- 
  60. | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
  61. ---------------------------------------------------------------------------------- 
  62. |   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
  63. |*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
  64. ---------------------------------------------------------------------------------- 
  65.  
  66. Predicate Information (identified by operation id): 
  67. --------------------------------------------------- 
  68.  
  69.    1 - filter("OBJECT_ID"=20) 
  70.  
  71. --2.获取新sql的sql_id 
  72. SQL> col sql_id for a20 
  73. SQL> col sql_text for a100 
  74. SQL> select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%'; 
  75.  
  76. SQL_ID               SQL_TEXT 
  77. -------------------- ---------------------------------------------------------------------------------------------------- 
  78. 5nkhk378705z3        select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%' 
  79. g23hbdmcsdahc        select /*+ full(test_raugher) */ * from test_raugher where object_id=20 
  80. dqp79vx5pmw0k        EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ full(test_raugher) */ * from test_raug 
  81.                      her where object_id=20 
  82.                       
  83. --3.获取新sql的outline 
  84. SQL> set pagesize 1000 
  85. SQL> select * from table(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline')); 
  86.  
  87. PLAN_TABLE_OUTPUT 
  88. ----------------------------------------------------------------------------------------------- 
  89. ----------------------------------------------------------------------------------------------- 
  90. SQL_ID  g23hbdmcsdahc, child number 0 
  91. ------------------------------------- 
  92. select /*+ full(test_raugher) */ * from test_raugher where object_id=20 
  93.  
  94. Plan hash value: 3725671026 
  95.  
  96. ---------------------------------------------------------------------------------- 
  97. | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
  98. ---------------------------------------------------------------------------------- 
  99. |   0 | SELECT STATEMENT  |              |       |       |   166 (100)|          | 
  100. |*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
  101. ---------------------------------------------------------------------------------- 
  102.  
  103. Outline Data 
  104. ------------- 
  105.  
  106.   /*+ 
  107.       BEGIN_OUTLINE_DATA 
  108.       IGNORE_OPTIM_EMBEDDED_HINTS 
  109.       OPTIMIZER_FEATURES_ENABLE('10.2.0.1') 
  110.       ALL_ROWS 
  111.       OUTLINE_LEAF(@"SEL$1") 
  112.       FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1") 
  113.       END_OUTLINE_DATA 
  114.   */ 
  115.  
  116. Predicate Information (identified by operation id): 
  117. --------------------------------------------------- 
  118.  
  119.    1 - filter("OBJECT_ID"=20) 
  120.  
  121.  
  122. 已选择31行。 
  123.  
  124. --4.创建sql profile(SQLPROFILE_001)                     
  125. SQL> declare 
  126.   2   v_hints sys.sqlprof_attr; 
  127.   3   begin 
  128.   4   v_hints:=sys.sqlprof_attr( 
  129.   5      'BEGIN_OUTLINE_DATA', 
  130.   6     'IGNORE_OPTIM_EMBEDDED_HINTS', 
  131.   7     'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')', 
  132.   8     'ALL_ROWS', 
  133.   9     'OUTLINE_LEAF(@"SEL$1")', 
  134.  10     'FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")', 
  135.  11     'END_OUTLINE_DATA'); 
  136.  12   dbms_sqltune.import_sql_profile( 
  137.  13     'select * from test_raugher where object_id=20', 
  138.  14     v_hints,'SQLPROFILE_001', 
  139.  15     force_match=>true,replace=>false); 
  140.  16  end; 
  141.  17  / 
  142.  
  143. PL/SQL 过程已成功完成。 
  144.  
  145. --5.查看是否使用sql profile 
  146. SQL> set autot trace explain 
  147. SQL> select * from test_raugher where object_id=20; 
  148.  
  149. 执行计划 
  150. ---------------------------------------------------------- 
  151. Plan hash value: 3725671026 
  152.  
  153. ---------------------------------------------------------------------------------- 
  154. | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
  155. ---------------------------------------------------------------------------------- 
  156. |   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
  157. |*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
  158. ---------------------------------------------------------------------------------- 
  159.  
  160. Predicate Information (identified by operation id): 
  161. --------------------------------------------------- 
  162.  
  163.    1 - filter("OBJECT_ID"=20) 
  164.  
  165. Note 
  166. ----- 
  167.    - SQL profile "SQLPROFILE_001" used for this statement 
  168.  
  169. SQL> select * from test_raugher where object_id=200; 
  170.  
  171. 执行计划 
  172. ---------------------------------------------------------- 
  173. Plan hash value: 3725671026 
  174.  
  175. ---------------------------------------------------------------------------------- 
  176. | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
  177. ---------------------------------------------------------------------------------- 
  178. |   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
  179. |*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
  180. ---------------------------------------------------------------------------------- 
  181.  
  182. Predicate Information (identified by operation id): 
  183. --------------------------------------------------- 
  184.  
  185.    1 - filter("OBJECT_ID"=200) 
  186.  
  187. Note 
  188. ----- 
  189.    - SQL profile "SQLPROFILE_001" used for this statement 
  190.     
 
  
 
 
  
创建sql profile
 
  
 
  
  1. DBMS_SQLTUNE.IMPORT_SQL_PROFILE( 
  2. sql_text => 'FULL QUERY TEXT',  
  3. profile => sqlprof_attr('HINT SPECIFICATION WITH FULL OBJECT ALIASES'),  
  4. name => 'PROFILE NAME', 
  5. force_match => TRUE/FALSE, 
  6. replace=> TRUE/FALSE);    
 
  
sql_text用于指定sql的全文本，可查询V$SQLAREA.SQL_FULLTEXT或DBA_HIST_SQLTEXT.SQL_TEXT获得。 
 
  
删除sql proflie
 
  
 
  
  1. BEGIN 
  2.   DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME'); 
  3. END; 
  4. / 
 
  
sql profile相关视图
 
  
 
  
  1. SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC; 
  2.  
  3. SELECT sql_attr.attr_val outline_hints  
  4. FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr  
  5. WHERE sql_profiles.signature = sql_attr.signature 
  6. AND sql_profiles.name = 'SQLPROFILE_001' 
  7. ORDER BY sql_attr.attr# ASC;