首页 / 数据库 / MySQL / Oracle SQL调优：使用outline稳固sql执行计划

为指定的sql创建outlineUSE_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 下一页

MySQL权限管理ORA:12541:TNS:无监听程序相关资讯      Oracle SQL  Oracle 基础  Oracle SQL调优 

Oracle高级SQL培训与讲解 PDF  （06月01日） SQL在Oracle内部的具体处理流程  （05/06/2015 10:43:43） Oracle使用WITH AS和HINT   （07/18/2014 15:55:31）

Oracle SQL语句追踪  （05/09/2015 09:42:25） Oracle执行SQL查询语句的步骤  （09/26/2014 19:40:59） 获取Oracle SQL语句中绑定变量值的  （07/17/2014 08:07:40）

本文评论 查看全部评论 （0）

表情： 姓名： 匿名字数 <div id="footbar"> 版权所有©石家庄振强科技有限公司2024 <a href="https://beian.miit.gov.cn" target="_blank">冀ICP备08103738号-5</a> <a href="/storage/sitemap.xml">网站地图</a> </div> </div> <script type="text/javascript" src="//cpro.baidustatic.com/cpro/ui/cm.js"></script> <script> var _mtj = _mtj || []; (function () { var mtj = document.createElement("script"); mtj.src = "https://node12.aizhantj.com:21233/tjjs/?k=p2tceukth5c"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(mtj, s); })(); </script> </body> </html>