使用Oracle sql profile固定执行计划


使用sql profile固定执行计划实验

Oracle 10g之前有outlines,10g之后sql profile作为新特性之一出现。

如果针对非绑定变量的sql,outlines则力不从心。

下面是实验过程

  1. --1.准备阶段
  2. SQL> select * from v$version;
  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
  12. SQL> createtable test_raugher asselect * from dba_objects;
  14. 表已创建。
  16. SQL> createindex ind_objectid on test_raugher(object_id);
  18. 索引已创建。
  20. SQL> select object_id from test_raugher where rownum<2;
  22. OBJECT_ID
  23. ----------
  24. 20
  26. SQL> exec dbms_stats.gather_table_stats(user,'TEST_RAUGHER',cascade=>true);
  28. PL/SQL 过程已成功完成。
  30. --原sql执行计划
  31. SQL> set autot trace explain
  32. SQL> select * from test_raugher where object_id=20;
  34. 执行计划
  35. ----------------------------------------------------------
  36. Plan hash value: 800879874
  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 BYINDEX ROWID| TEST_RAUGHER | 1 | 95 | 2 (0)| 00:00:01 |
  43. |* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
  44. --------------------------------------------------------------------------------------------
  46. Predicate Information (identified by operation id):
  47. ---------------------------------------------------
  49. 2 - access("OBJECT_ID"=20)
  51. SQL>
  52. --新sql执行计划
  53. SQL> select /*+ full(test_raugher) */ * from test_raugher where object_id=20;
  55. 执行计划
  56. ----------------------------------------------------------
  57. Plan hash value: 3725671026
  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. ----------------------------------------------------------------------------------
  66. Predicate Information (identified by operation id):
  67. ---------------------------------------------------
  69. 1 - filter("OBJECT_ID"=20)
  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)%';
  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'FORselect /*+ full(test_raugher) */ * from test_raug
  81. her where object_id=20
  83. --3.获取新sql的outline
  84. SQL> set pagesize 1000
  85. SQL> select * fromtable(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline'));
  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
  94. Plan hash value: 3725671026
  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. ----------------------------------------------------------------------------------
  103. Outline Data
  104. -------------
  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. */
  116. Predicate Information (identified by operation id):
  117. ---------------------------------------------------
  119. 1 - filter("OBJECT_ID"=20)
  122. 已选择31行。
  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 /
  143. PL/SQL 过程已成功完成。
  145. --5.查看是否使用sql profile
  146. SQL> set autot trace explain
  147. SQL> select * from test_raugher where object_id=20;
  149. 执行计划
  150. ----------------------------------------------------------
  151. Plan hash value: 3725671026
  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. ----------------------------------------------------------------------------------
  160. Predicate Information (identified by operation id):
  161. ---------------------------------------------------
  163. 1 - filter("OBJECT_ID"=20)
  165. Note
  166. -----
  167. - SQL profile "SQLPROFILE_001" used for this statement
  169. SQL> select * from test_raugher where object_id=200;
  171. 执行计划
  172. ----------------------------------------------------------
  173. Plan hash value: 3725671026
  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. ----------------------------------------------------------------------------------
  182. Predicate Information (identified by operation id):
  183. ---------------------------------------------------
  185. 1 - filter("OBJECT_ID"=200)
  187. Note
  188. -----
  189. - SQL profile "SQLPROFILE_001" used for this statement
  • 1
  • 2
  • 下一页

相关内容