Oracle SQL调优：使用outline稳固sql执行计划


为指定的sql创建outline

USE_STORED_OUTLINES
Syntax:
USE_STORED_OUTLINES = { TRUE | FALSE | category_name }
this parameters are not initialization parameters, so you cannot set them in a pfile or spfile. However, you can set them using an ALTER SYSTEM statement.重启后需要重新设置。

lau为创建outline的用户，即我们的应用用户。

  1. SQL> conn sys/Oracle@orcl as sysdba  
  2. 已连接。  
  3.   
  4. --1.为创建outline用户赋权CREATE ANY OUTLINE   
  5. SQL> grant CREATE ANY OUTLINE to lau;  
  6.   
  7. 授权成功。  
  8.   
  9. SQL> conn lau/lau@orcl  
  10. 已连接。  
  11.   
  12. SQL> create table t (id int);  
  13.   
  14. 表已创建。  
  15.   
  16. SQL> insert into t select level from dual connect by level <=10000;  
  17.   
  18. 已创建10000行。  
  19.   
  20. SQL> commit;  
  21.   
  22. 提交完成。  
  23.   
  24. SQL> set autot traceonly  
  25. SQL> select * from t where id=1;  
  26.   
  27.   
  28. 执行计划  
  29. ----------------------------------------------------------   
  30. Plan hash value: 1601196873  
  31.   
  32. --------------------------------------------------------------------------   
  33. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  34. --------------------------------------------------------------------------   
  35. |   0 | SELECT STATEMENT  |      |     1 |    13 |     6   (0)| 00:00:01 |  
  36. |*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     6   (0)| 00:00:01 |  
  37. --------------------------------------------------------------------------   
  38.   
  39. Predicate Information (identified by operation id):  
  40. ---------------------------------------------------   
  41.   
  42.    1 - filter("ID"=1)  
  43.   
  44. Note  
  45. -----   
  46.    - dynamic sampling used for this statement  
  47.   
  48.   
  49. 统计信息  
  50. ----------------------------------------------------------   
  51.           5  recursive calls  
  52.           0  db block gets  
  53.          48  consistent gets  
  54.           0  physical reads  
  55.           0  redo size  
  56.         402  bytes sent via SQL*Net to client  
  57.         385  bytes received via SQL*Net from client  
  58.           2  SQL*Net roundtrips to/from client  
  59.           0  sorts (memory)  
  60.           0  sorts (disk)  
  61.           1  rows processed  
  62.   
  63. --2.创建两个测试outline   
  64. SQL> create or replace outline  test_outline1 for category cate_outline  
  65.   2   on select * from t where id=1;  
  66.   
  67. 大纲已创建。  
  68.   
  69. SQL> create or replace outline  test_outline2 for category cate_outline  
  70.   2   on select * from t where id=2;  
  71.   
  72. 大纲已创建。  
  73.   
  74. --3.查看该用户下创建的outline。   
  75. SQL> col name for a20  
  76. SQL> col sql_text for a50  
  77. SQL> col used for a10  
  78. SQL> set autot off  
  79. SQL> set linesize 200  
  80. SQL> select name, sql_text ,used,category  
  81.   2    from user_outlines  
  82.   3   where category=upper('cate_outline');  
  83.   
  84. NAME                 SQL_TEXT                                           USED       CATEGORY  
  85. -------------------- -------------------------------------------------- ---------- ------------------------------   
  86. TEST_OUTLINE2        select * from t where id=2                         UNUSED     CATE_OUTLINE  
  87. TEST_OUTLINE1        select * from t where id=1                         UNUSED     CATE_OUTLINE  
  88.   
  89. --4.使cate_outline下的outline生效   
  90. SQL> alter system set  USE_STORED_OUTLINES =cate_outline;  
  91.   
  92. 系统已更改。  
  93.   
  94. SQL> select name, sql_text ,used,category  
  95.   2    from user_outlines  
  96.   3   where category=upper('cate_outline');  
  97.   
  98. NAME                 SQL_TEXT                                           USED       CATEGORY  
  99. -------------------- -------------------------------------------------- ---------- ------------------------------   
  100. TEST_OUTLINE2        select * from t where id=2                         UNUSED     CATE_OUTLINE  
  101. TEST_OUTLINE1        select * from t where id=1                         UNUSED     CATE_OUTLINE  
  102.   
  103. SQL> set autot explain  
  104. 用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]  
  105. SQL> set autot on explain  
  106. SQL> select * from t where id=1;  
  107.   
  108.         ID  
  109. ----------   
  110.          1  
  111.   
  112.   
  113. 执行计划  
  114. ----------------------------------------------------------   
  115. Plan hash value: 1601196873  
  116.   
  117. --------------------------------------------------------------------------   
  118. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  119. --------------------------------------------------------------------------   
  120. |   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |  
  121. |*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |  
  122. --------------------------------------------------------------------------   
  123.   
  124. Predicate Information (identified by operation id):  
  125. ---------------------------------------------------   
  126.   
  127.    1 - filter("ID"=1)  
  128.   
  129. Note  
  130. -----   
  131.    - outline "TEST_OUTLINE1" used for this statement  --说明已经使用了我们创建的outline.   
  132.   
  133. SQL> select * from t where id=2;  
  134.   
  135.         ID  
  136. ----------   
  137.          2  
  138.   
  139.   
  140. 执行计划  
  141. ----------------------------------------------------------   
  142. Plan hash value: 1601196873  
  143.   
  144. --------------------------------------------------------------------------   
  145. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  146. --------------------------------------------------------------------------   
  147. |   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |  
  148. |*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |  
  149. --------------------------------------------------------------------------   
  150.   
  151. Predicate Information (identified by operation id):  
  152. ---------------------------------------------------   
  153.   
  154.    1 - filter("ID"=2)  
  155.   
  156. Note  
  157. -----   
  158.    - outline "TEST_OUTLINE2" used for this statement  
  159.   
  160. --以下没有使用outline,因为没有绑定变量。   
  161. SQL> select * from t where id=3;  
  162.   
  163.         ID  
  164. ----------   
  165.          3  
  166.   
  167.   
  168. 执行计划  
  169. ----------------------------------------------------------   
  170. Plan hash value: 1601196873  
  171.   
  172. --------------------------------------------------------------------------   
  173. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  174. --------------------------------------------------------------------------   
  175. |   0 | SELECT STATEMENT  |      |     1 |    13 |     6   (0)| 00:00:01 |  
  176. |*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     6   (0)| 00:00:01 |  
  177. --------------------------------------------------------------------------   
  178.   
  179. Predicate Information (identified by operation id):  
  180. ---------------------------------------------------   
  181.   
  182.    1 - filter("ID"=3)  
  183.   
  184. Note  
  185. -----   
  186.    - dynamic sampling used for this statement  
  187.   
  188. --创建索引，验证outline的使用，sql依然使用全表扫描。   
  189. SQL> create index ind_t_id on t(id);  
  190.   
  191. 索引已创建。  
  192.   
  193. SQL> select * from t where id=1;  
  194.   
  195.         ID  
  196. ----------   
  197.          1  
  198.   
  199.   
  200. 执行计划  
  201. ----------------------------------------------------------   
  202. Plan hash value: 1601196873  
  203.   
  204. --------------------------------------------------------------------------   
  205. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  206. --------------------------------------------------------------------------   
  207. |   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |  
  208. |*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |  
  209. --------------------------------------------------------------------------   
  210.   
  211. Predicate Information (identified by operation id):  
  212. ---------------------------------------------------   
  213.   
  214.    1 - filter("ID"=1)  
  215.   
  216. Note  
  217. -----   
  218.    - outline "TEST_OUTLINE1" used for this statement  
  219.   
  220. --禁用outline之后，sql使用了索引   
  221. SQL> alter system set  USE_STORED_OUTLINES =false;  
  222.   
  223. 系统已更改。  
  224.   
  225. SQL> select * from t where id=1;  
  226.   
  227.         ID  
  228. ----------   
  229.          1  
  230.   
  231.   
  232. 执行计划  
  233. ----------------------------------------------------------   
  234. Plan hash value: 3343177607  
  235.   
  236. -----------------------------------------------------------------------------   
  237. | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  238. -----------------------------------------------------------------------------   
  239. |   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |  
  240. |*  1 |  INDEX RANGE SCAN| IND_T_ID |     1 |    13 |     1   (0)| 00:00:01 |  
  241. -----------------------------------------------------------------------------   
  242.   
  243. Predicate Information (identified by operation id):  
  244. ---------------------------------------------------   
  245.   
  246.    1 - access("ID"=1)  
  247.   
  248. Note  
  249. -----   
  250.    - dynamic sampling used for this statement  
  • 1
  • 2
  • 3
  • 4
  • 下一页

相关内容