请大家帮忙看看为什么语句的执行计划会变来变去

Question

本帖最后由 chiyingluolei 于 2012-07-09 09:48:01 编辑

Answer 1

explain plan for:
一个是：
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                            |     1 |   185 |    14  (15)|       |       |
|   1 |  SORT ORDER BY                            |                            |     1 |   185 |    14  (15)|       |       |
|   2 |   HASH GROUP BY                           |                            |     1 |   185 |    14  (15)|       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID            | WBF_NODEPROCESSMAN         |     1 |    25 |     4   (0)|       |       |
|   4 |     NESTED LOOPS                          |                            |     1 |   185 |    12   (0)|       |       |
|   5 |      NESTED LOOPS                         |                            |     1 |   160 |     8   (0)|       |       |
|   6 |       NESTED LOOPS                        |                            |     1 |   130 |     6   (0)|       |       |
|   7 |        NESTED LOOPS                       |                            |     1 |   113 |     5   (0)|       |       |
|   8 |         PARTITION RANGE ITERATOR          |                            |     1 |    82 |     3   (0)|    16 |    20 |
|   9 |          TABLE ACCESS FULL                | WBF_NODEPROCESS            |     1 |    82 |     3   (0)|    16 |    20 |
|  10 |         TABLE ACCESS BY GLOBAL INDEX ROWID| WBF_NODEINSTANCE           |     1 |    31 |     2   (0)| ROWID | ROWID |
|  11 |          INDEX UNIQUE SCAN                | PK_WBF_NODEINSTANCE        |     1 |       |     1   (0)|       |       |
|  12 |        TABLE ACCESS BY INDEX ROWID        | WBF_NODEFLOW               |     1 |    17 |     1   (0)|       |       |
|  13 |         INDEX UNIQUE SCAN                 | PK_WBF_NODEFLOW            |     1 |       |     0   (0)|       |       |
|  14 |       TABLE ACCESS BY INDEX ROWID         | WBF_FORM                   |     1 |    30 |     2   (0)|       |       |
|  15 |        INDEX UNIQUE SCAN                  | PK_WBF_FORM                |     1 |       |     1   (0)|       |       |
|  16 |      INDEX RANGE SCAN                     | IDX_NODEPMAN_NODEPROCESSID |     1 |       |     3   (0)|       |       |
-----------------------------------------------------------------------------------------------------------------------------
另一个是：
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |   290 | 53650 |       |   128K (13)|       |       |
|   1 |  SORT ORDER BY                 |                         |   290 | 53650 |       |   128K (13)|       |       |
|   2 |   HASH GROUP BY                |                         |   290 | 53650 |       |   128K (13)|       |       |
|   3 |    HASH JOIN                   |                         | 96565 |    17M|       |   128K (13)|       |       |
|   4 |     VIEW                       | index$_join$_005        |   145 |  2465 |       |     3  (34)|       |       |
|   5 |      HASH JOIN                 |                         |       |       |       |            |       |       |
|   6 |       INDEX FAST FULL SCAN     | IDX_WBF_NODEFLOW_NODEID |   145 |  2465 |       |     1   (0)|       |       |
|   7 |       INDEX FAST FULL SCAN     | PK_WBF_NODEFLOW         |   145 |  2465 |       |     1   (0)|       |       |
|   8 |     HASH JOIN                  |                         | 95898 |    15M|    13M|   128K (13)|       |       |
|   9 |      HASH JOIN                 |                         | 95898 |    12M|    10M| 78649  (20)|       |       |
|  10 |       HASH JOIN                |                         | 95898 |     9M|  8808K| 55589  (27)|       |       |
|  11 |        PARTITION RANGE ITERATOR|                         | 95898 |  7679K|       |  1008   (3)|    15 |    20 |
|  12 |         TABLE ACCESS FULL      | WBF_NODEPROCESS         | 95898 |  7679K|       |  1008   (3)|    15 |    20 |
|  13 |        TABLE ACCESS FULL       | WBF_NODEPROCESSMAN      |  1144K|    27M|       | 52099  (29)|       |       |
|  14 |       TABLE ACCESS FULL        | WBF_FORM                |  2725K|    77M|       | 16986   (3)|       |       |
|  15 |      PARTITION RANGE ALL       |                         |  8021K|   237M|       | 32567   (2)|     1 |     8 |
|  16 |       TABLE ACCESS FULL        | WBF_NODEINSTANCE        |  8021K|   237M|       | 32567   (2)|     1 |     8 |
-----------------------------------------------------------------------------------------------------------------------
 

Answer 2

用hint提示,比如 /*+ index(tab_alias, index_name) */
不过,万不得已不建议这样做.

Answer 3

Oracle会根据表的统计信息做出最优的执行计划,所以不建议强制oracle按某个计划去执行.
如果非要按某计划去执行,请用hints. 具体如何用,请教白老师或谷老师吧.