oracle执行计划(Explain Plan for

如果要分析某条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 当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数<