oracle执行计划(Explain Plan for
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle执行计划(Explain Plan for，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13725字，纯文字阅读大概需要20分钟。
内容图文
如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。 如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。
2
3
4
5 看懂执行计划也就成了SQL优化的先决条件。 这里的SQL优化指的是SQL性能问题的定位，定位后就可以解决问题。
6
7
8
9
10
11 一． 查看执行计划的三种方法
12
13 1.1 设置autotrace
14
15 序号
16
17 命令
18
19 解释
20
21 1
22
23 SET AUTOTRACE OFF
24
25 此为默认值，即关闭Autotrace
26
27 2
28
29 SET AUTOTRACE ON EXPLAIN
30
31 只显示执行计划
32
33 3
34
35 SET AUTOTRACE ON STATISTICS
36
37 只显示执行的统计信息
38
39 4
40
41 SET AUTOTRACE ON
42
43 包含2,3两项内容
44
45 5
46
47 SET AUTOTRACE TRACEONLY
48
49 与ON相似，但不显示语句的执行结果
50
51
52
53 SQL> set autotrace on
54
55 SQL> select * from dave;
56
57 ID NAME
58
59 ---------- ----------
60
61 8 安庆
62
63 1 dave
64
65 2 bl
66
67 1 bl
68
69 2 dave
70
71 3 dba
72
73 4 sf-express
74
75 5 dmm
76
77
78
79 已选择8行。
80
81
82
83 执行计划
84
85 ----------------------------------------------------------
86
87 Plan hash value: 3458767806
88
89 --------------------------------------------------------------------------
90
91 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
92
93 --------------------------------------------------------------------------
94
95 | 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |
96
97 | 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |
98
99 --------------------------------------------------------------------------
100
101
102
103 统计信息
104
105 ----------------------------------------------------------
106
107 0 recursive calls
108
109 0 db block gets
110
111 4 consistent gets
112
113 0 physical reads
114
115 0 redo size
116
117 609 bytes sent via SQL*Net to client
118
119 416 bytes received via SQL*Net from client
120
121 2 SQL*Net roundtrips to/from client
122
123 0 sorts (memory)
124
125 0 sorts (disk)
126
127 8 rows processed
128
129
130
131 SQL>
132
133
134
135 1.2 使用SQL
136
137 SQL>EXPLAIN PLAN FOR sql语句;
138
139 SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE‘));
140
141
142
143 示例：
144
145 SQL> EXPLAIN PLAN FOR SELECT * FROM DAVE;
146
147 已解释。
148
149 SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE‘));
150
151 或者：
152
153 SQL> select * from table(dbms_xplan.display);
154
155 PLAN_TABLE_OUTPUT
156
157 --------------------------------------------------------------------------------
158
159 Plan hash value: 3458767806
160
161
162
163 --------------------------------------------------------------------------
164
165 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
166
167 --------------------------------------------------------------------------
168
169 | 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |
170
171 | 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |
172
173 --------------------------------------------------------------------------
174
175 已选择8行。
176
177 执行计划
178
179 ----------------------------------------------------------
180
181 Plan hash value: 2137789089
182
183 --------------------------------------------------------------------------------
184
185 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
186
187 ---------------------------------------------------------------------------------------------
188
189 | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
190
191 | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
192
193 ---------------------------------------------------------------------------------------------
194
195
196
197 统计信息
198
199 ----------------------------------------------------------
200
201 25 recursive calls
202
203 12 db block gets
204
205 168 consistent gets
206
207 0 physical reads
208
209 0 redo size
210
211 974 bytes sent via SQL*Net to client
212
213 416 bytes received via SQL*Net from client
214
215 2 SQL*Net roundtrips to/from client
216
217 1 sorts (memory)
218
219 0 sorts (disk)
220
221 8 rows processed
222
223 SQL>
224
225
226
227 1.3 使用Toad,PL/SQL Developer工具
228
229
230
231
232
233 二． Cardinality（基数）/ rows
234
235 Cardinality值表示CBO预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。 在Oracle 9i中的执行计划中，Cardinality缩写成Card。 在10g中，Card值被rows替换。
236
237
238
239 这是9i的一个执行计划，我们可以看到关键字Card：
240
241 执行计划
242
243 ----------------------------------------------------------
244
245 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=402)
246
247 1 0 TABLE ACCESS (FULL) OF ‘TBILLLOG8‘ (Cost=2 Card=1 Bytes=402)
248
249
250
251 Oracle 10g的执行计划，关键字换成了rows：
252
253 执行计划
254
255 ----------------------------------------------------------
256
257 Plan hash value: 2137789089
258
259 --------------------------------------------------------------------------------
260
261 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
262
263 ---------------------------------------------------------------------------------------------
264
265 | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
266
267 | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
268
269 ---------------------------------------------------------------------------------------------
270
271
272
273 Cardinality的值对于CBO做出正确的执行计划来说至关重要。 如果CBO获得的Cardinality值不够准确（通常是没有做分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致CBO错误的制定出执行计划。
274
275
276
277 在多表关联查询或者SQL中有子查询时，每个关联表或子查询的Cardinality的值对主查询的影响都非常大，甚至可以说，CBO就是依赖于各个关联表或者子查询Cardinality值计算出最后的执行计划。
278
279
280
281 对于多表查询，CBO使用每个关联表返回的行数（Cardinality）决定用什么样的访问方式来做表关联（如Nested loops Join 或 hash Join）。
282
283 多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP
284
285 http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx
286
287
288
289 对于子查询，它的Cardinality将决定子查询是使用索引还是使用全表扫描的方式访问数据。
290
291
292
293
294
295
296
297 三． SQL 的执行计划
298
299 生成SQL的执行计划是Oracle在对SQL做硬解析时的一个非常重要的步骤，它制定出一个方案告诉Oracle在执行这条SQL时以什么样的方式访问数据：索引还是全表扫描，是Hash Join还是Nested loops Join等。 比如说某条SQL通过使用索引的方式访问数据是最节省资源的，结果CBO作出的执行计划是全表扫描，那么这条SQL的性能必然是比较差的。
300
301 Oracle SQL的硬解析和软解析
302
303 http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx
304
305
306
307 示例：
308
309 SQL> SET AUTOTRACE TRACEONLY; -- 只显示执行计划，不显示结果集
310
311 SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;
312
313 已选择13行。
314
315
316
317 执行计划
318
319 ----------------------------------------------------------
320
321 Plan hash value: 992080948
322
323 ---------------------------------------------------------------------------------------
324
325 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
326
327 ---------------------------------------------------------------------------------------
328
329 | 0 | SELECT STATEMENT | | 13 | 988 | 6 (17)| 00:00:01 |
330
331 | 1 | MERGE JOIN | | 13 | 988 | 6 (17)| 00:00:01 |
332
333 | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
334
335 | 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
336
337 |* 4 | SORT JOIN | | 13 | 494 | 4 (25)| 00:00:01 |
338
339 |* 5 | TABLE ACCESS FULL | EMP | 13 | 494 | 3 (0)| 00:00:01 |
340
341 ---------------------------------------------------------------------------------------
342
343
344
345 Predicate Information (identified by operation id):
346
347 ---------------------------------------------------
348
349 4 - access("A"."EMPNO"="B"."MGR")
350
351 filter("A"."EMPNO"="B"."MGR")
352
353 5 - filter("B"."MGR" IS NOT NULL)
354
355
356
357 统计信息
358
359 ----------------------------------------------------------
360
361 0 recursive calls
362
363 0 db block gets
364
365 11 consistent gets
366
367 0 physical reads
368
369 0 redo size
370
371 2091 bytes sent via SQL*Net to client
372
373 416 bytes received via SQL*Net from client
374
375 2 SQL*Net roundtrips to/from client
376
377 1 sorts (memory)
378
379 0 sorts (disk)
380
381 13 rows processed
382
383 SQL>
384
385
386
387
388
389
390
391
392
393 图片是Toad工具查看的执行计划。 在Toad 里面，很清楚的显示了执行的顺序。 但是如果在SQLPLUS里面就不是那么直接。 但我们也可以判断：一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。
394
395
396
397
398
399 3.1 执行计划中字段解释：
400
401 ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。
402
403 Operation： 当前操作的内容。
404
405 Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。
406
407 Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。
408
409 Time：Oracle 估计当前操作的时间。
410
411
412
413 3.2 谓词说明：
414
415 Predicate Information (identified by operation id):
416
417 ---------------------------------------------------
418
419 4 - access("A"."EMPNO"="B"."MGR")
420
421 filter("A"."EMPNO"="B"."MGR")
422
423 5 - filter("B"."MGR" IS NOT NULL)
424
425
426
427 Access: 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。
428
429 Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。
430
431
432
433 在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。
434
435
436
437
438
439 3.3 统计信息说明：
440
441
442
443
444
445
446
447 db block gets ： 从buffer cache中读取的block的数量
448
449 consistent gets： 从buffer cache中读取的undo数据的block的数量
450
451 physical reads： 从磁盘读取的block的数量
452
453 redo size： DML生成的redo的大小
454
455 sorts (memory) ：在内存执行的排序量
456
457 sorts (disk) ：在磁盘上执行的排序量
458
459
460
461 Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增 加相关的索引，优化SQL语句来解决。
462
463
464
465 关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：
466
467 数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。
468
469
470
471 用以下语句可以查看数据缓冲区的命中率：
472
473 SQL>SELECT name, value FROM v$sysstat WHERE name IN (‘db block gets‘, ‘consistent gets‘,‘physical reads‘);
474
475 查询出来的结果Buffer Cache的命中率应该在90％以上，否则需要增加数据缓冲区的大小。
476
477
478
479 Recursive Calls： Number of recursive calls generated at both the user and system level.
480
481 Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。
482
483
484
485 DB Block Gets： Number of times a CURRENT block was requested.
486
487 Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them. (DB Block Gets:请求的数据块在buffer能满足的个数)
488 当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数
本文系统来源：https://www.cnblogs.com/bigdata-6/p/12894216.html
内容总结
以上是互联网集市为您收集整理的oracle执行计划(Explain Plan for全部内容，希望文章能够帮你解决oracle执行计划(Explain Plan for所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】