快速SQL调优/优化（SQL TUNING）——1分钟搞定超慢SQL

前几天，一个用户的研发人员找到我了，说他们有个SQL语句非常慢，我说多慢？他们说：半个小时也没出结果。于是问他们要了SQL语句和执行计划，SQL语句就不能再这里贴出来了，下面是调整前的执行计划（略去某些细节信息，如需了解细节，请到本人QQ空间浏览）：

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                   | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                        |     1 |   435 | 15664 |
|   1 |  HASH JOIN OUTER                            |                        |     1 |   435 | 15664 |
|   2 |   NESTED LOOPS OUTER                        |                        |     1 |   413 | 14780 |
|   3 |    NESTED LOOPS                             |                        |     1 |   406 | 14780 |
|   4 |     NESTED LOOPS OUTER                      |                        |     1 |   386 | 14779 |
|   5 |      NESTED LOOPS                           |                        |     1 |   382 | 12788 |
|   6 |       HASH JOIN RIGHT ANTI                  |                        |     1 |   369 | 12788 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|   7 |        VIEW                                 |                        |   277K|  3521K|  2420 |
|   8 |         NESTED LOOPS OUTER                  |                        |   277K|    33M|  2420 |
|   9 |          HASH JOIN                          |                        |   277K|    31M|  2392 |
|  10 |           INDEX FULL SCAN                   |                                |    26 |   104 |     1 |
|  11 |           HASH JOIN                         |                        |   277K|    30M|  2389 |
|  12 |            TABLE ACCESS FULL                |                        |   705 |  8460 |     5 |
|  13 |            HASH JOIN                        |                        |   277K|    27M|  2382 |
|  14 |             INDEX FAST FULL SCAN            |                        |    47 |   611 |     2 |
|  15 |             HASH JOIN                       |                        |   277K|    23M|  2378 |
|  16 |              INDEX FULL SCAN                |                        |   366 |  1830 |     1 |
|  17 |              HASH JOIN                      |                        |   287K|    23M|  2375 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  18 |               HASH JOIN                     |                        | 64258 |  2886K|   699 |
|  19 |                TABLE ACCESS FULL            |                        |    46 |   460 |     3 |
|  20 |                TABLE ACCESS FULL            |                        | 64528 |  2268K|   695 |
|  21 |               TABLE ACCESS FULL             |                        |   288K|    10M|   800 |
|  22 |          INDEX UNIQUE SCAN                  |                                 |     1 |     7 |     0 |
|  23 |        HASH JOIN                            |                        |   243K|    82M|  5799 |
|  24 |         HASH JOIN                           |                        | 62017 |  4360K|   710 |
|  25 |          TABLE ACCESS FULL                  |                        |   366 |  3660 |     3 |
|  26 |          HASH JOIN                          |                        | 64305 |  3893K|   706 |
|  27 |           TABLE ACCESS FULL                 |                        |    46 |   460 |     3 |
|  28 |           HASH JOIN                         |                        | 64575 |  3279K|   702 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  29 |            HASH JOIN                        |                        |   705 | 11280 |     7 |
|  30 |             INDEX FULL SCAN                 |        |    26 |   104 |     1 |
|  31 |             TABLE ACCESS FULL               |         |   705 |  8460 |     5 |
|  32 |            TABLE ACCESS FULL                |          | 64575 |  2270K|   695 |
|  33 |         TABLE ACCESS FULL                   |        |   288K|    78M|   800 |
|  34 |       INDEX UNIQUE SCAN                     |   |     1 |    13 |     0 |
|  35 |      VIEW PUSHED PREDICATE                  |   |     1 |     4 |  1992 |
|  36 |       NESTED LOOPS                          |                        |   595 |   105K|  1992 |
|  37 |        NESTED LOOPS OUTER                   |                        |   617 |   106K|  1992 |
|  38 |         NESTED LOOPS SEMI                   |                        |     1 |   154 |  1109 |
|  39 |          NESTED LOOPS                       |                        |     1 |   141 |     5 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  40 |           NESTED LOOPS                      |                        |     1 |   137 |     5 |
|  41 |            NESTED LOOPS OUTER               |                        |     1 |   125 |     4 |
|  42 |             NESTED LOOPS                    |                        |     1 |   118 |     4 |
|  43 |              NESTED LOOPS                   |                        |     1 |   111 |     4 |
|  44 |               NESTED LOOPS                  |                        |     1 |   101 |     3 |
|  45 |                NESTED LOOPS                 |                        |     1 |    88 |     3 |
|  46 |                 TABLE ACCESS BY INDEX ROWID |              |     1 |    52 |     2 |
|  47 |                  INDEX UNIQUE SCAN          |         |     1 |       |     1 |
|  48 |                 TABLE ACCESS BY INDEX ROWID |              | 64575 |  2270K|     1 |
|  49 |                  INDEX UNIQUE SCAN          |          |     1 |       |     0 |
|  50 |                INDEX UNIQUE SCAN            |      |   409 |  5317 |     0 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  51 |               TABLE ACCESS BY INDEX ROWID   |      |    46 |   460 |     1 |
|  52 |                INDEX UNIQUE SCAN            |        |     1 |       |     0 |
|  53 |              INDEX UNIQUE SCAN              |        |  1305 |  9135 |     0 |
|  54 |             INDEX UNIQUE SCAN               |      | 73457 |   502K|     0 |
|  55 |            TABLE ACCESS BY INDEX ROWID      |         |   705 |  8460 |     1 |
|  56 |             INDEX UNIQUE SCAN               |       |     1 |       |     0 |
|  57 |           INDEX UNIQUE SCAN                 |     |    26 |   104 |     0 |
|  58 |          VIEW                               |              |  2774 | 36062 |  1104 |
|  59 |           NESTED LOOPS                      |                        |  2774 |   341K|  1104 |
|  60 |            NESTED LOOPS OUTER               |                        |  2774 |   306K|  1104 |
|  61 |             NESTED LOOPS                    |                        |  2774 |   287K|  1103 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  62 |              NESTED LOOPS                   |                        |  2876 |   283K|  1103 |
|  63 |               HASH JOIN                     |                        |  2876 |   272K|  1103 |
|  64 |                TABLE ACCESS FULL            |                        |    46 |   460 |     3 |
|  65 |                HASH JOIN                    |                        |  2889 |   245K|  1099 |
|  66 |                 TABLE ACCESS FULL           |                        |   705 |  8460 |     5 |
|  67 |                 HASH JOIN                   |                        |  2889 |   211K|  1094 |
|  68 |                  TABLE ACCESS BY INDEX ROWID|                        |  2886 |   109K|   398 |
|  69 |                   INDEX RANGE SCAN          |                        |  1161 |       |     7 |
|  70 |                  TABLE ACCESS FULL          |                        | 64528 |  2268K|   695 |
|  71 |               INDEX UNIQUE SCAN             |                        |     1 |     4 |     0 |
|  72 |              INDEX RANGE SCAN               |                        |     1 |     5 |     0 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  73 |             INDEX UNIQUE SCAN               |                        |     1 |     7 |     0 |
|  74 |            INDEX UNIQUE SCAN                |                        |     1 |    13 |     0 |
|  75 |         VIEW                                |                        |   617 | 13574 |   883 |
|  76 |          NESTED LOOPS                       |                        | 61721 |  4942K|   883 |
|  77 |           HASH JOIN RIGHT OUTER             |                        | 61721 |  4520K|   876 |
|  78 |            TABLE ACCESS FULL                |                        | 47281 |   646K|   168 |
|  79 |            HASH JOIN                        |                        | 61721 |  3676K|   707 |
|  80 |             INDEX FULL SCAN                 |                        |    26 |   104 |     1 |
|  81 |             HASH JOIN                       |                        | 61721 |  3435K|   705 |
|  82 |              TABLE ACCESS FULL              |                        |   705 |  8460 |     5 |
|  83 |              HASH JOIN                      |                        | 61721 |  2712K|   700 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  84 |               INDEX FAST FULL SCAN          |                        |  1305 |  9135 |     2 |
|  85 |               HASH JOIN                     |                        | 62017 |  2301K|   697 |
|  86 |                NESTED LOOPS                 |                        |    44 |   660 |     3 |
|  87 |                 TABLE ACCESS FULL           |                        |    46 |   460 |     3 |
|  88 |                 INDEX RANGE SCAN            |                        |     1 |     5 |     0 |
|  89 |                TABLE ACCESS FULL            |                        | 64575 |  1450K|   693 |
|  90 |           INDEX UNIQUE SCAN                 |                        |     1 |     7 |     0 |
|  91 |        INDEX RANGE SCAN                     |                        |     1 |     5 |     0 |
|  92 |     TABLE ACCESS BY INDEX ROWID             |                        |     1 |    20 |     1 |
|  93 |      INDEX UNIQUE SCAN                      |                        |     1 |       |     0 |
|  94 |    INDEX UNIQUE SCAN                        |                        |     1 |     7 |     0 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
|  95 |   VIEW                                      |                        | 61721 |  1326K|   883 |
|  96 |    NESTED LOOPS                             |                        | 61721 |  4942K|   883 |
|  97 |     HASH JOIN RIGHT OUTER                   |                        | 61721 |  4520K|   876 |
|  98 |      TABLE ACCESS FULL                      |                        | 47281 |   646K|   168 |
|  99 |      HASH JOIN                              |                        | 61721 |  3676K|   707 |
| 100 |       INDEX FULL SCAN                       |                        |    26 |   104 |     1 |
| 101 |       HASH JOIN                             |                        | 61721 |  3435K|   705 |
| 102 |        TABLE ACCESS FULL                    |                        |   705 |  8460 |     5 |
| 103 |        HASH JOIN                            |                        | 61721 |  2712K|   700 |
| 104 |         INDEX FAST FULL SCAN                |                        |  1305 |  9135 |     2 |
| 105 |         HASH JOIN                           |                        | 62017 |  2301K|   697 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
| 106 |          NESTED LOOPS                       |                        |    44 |   660 |     3 |
| 107 |           TABLE ACCESS FULL                 |                        |    46 |   460 |     3 |
| 108 |           INDEX RANGE SCAN                  |                        |     1 |     5 |     0 |
| 109 |          TABLE ACCESS FULL                  |                        | 64575 |  1450K|   693 |
| 110 |     INDEX UNIQUE SCAN                       |                        |     1 |     7 |     0 |
------------------------------------------------------------------------------------------------------

看了下这个较长的计划，心里有数了，略施小计，再看执行计划，已变过来了，也贴在下面（略去某些细节）：

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |   131K|    27M| 15293 |
|   1 |  HASH JOIN RIGHT OUTER             |                        |   131K|    27M| 15293 |
|   2 |   VIEW                             |                        | 61721 |  1326K|   883 |
|   3 |    NESTED LOOPS                    |                        | 61721 |  4942K|   883 |
|   4 |     HASH JOIN RIGHT OUTER          |                        | 61721 |  4520K|   876 |
|   5 |      TABLE ACCESS FULL             |                        | 47281 |   646K|   168 |
|   6 |      HASH JOIN                     |                        | 61721 |  3676K|   707 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|   7 |       INDEX FULL SCAN              |                        |    26 |   104 |     1 |
|   8 |       HASH JOIN                    |                        | 61721 |  3435K|   705 |
|   9 |        TABLE ACCESS FULL           |                        |   705 |  8460 |     5 |
|  10 |        HASH JOIN                   |                        | 61721 |  2712K|   700 |
|  11 |         INDEX FAST FULL SCAN       |                        |  1305 |  9135 |     2 |
|  12 |         HASH JOIN                  |                        | 62017 |  2301K|   697 |
|  13 |          NESTED LOOPS              |                        |    44 |   660 |     3 |
|  14 |           TABLE ACCESS FULL        |                        |    46 |   460 |     3 |
|  15 |           INDEX RANGE SCAN         |                        |     1 |     5 |     0 |
|  16 |          TABLE ACCESS FULL         |                        | 64575 |  1450K|   693 |
|  17 |     INDEX UNIQUE SCAN              |                        |     1 |     7 |     0 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  18 |   HASH JOIN RIGHT OUTER            |                        |   131K|    24M| 13017 |
|  19 |    VIEW                            |                        | 75387 |   957K|  6876 |
|  20 |     HASH JOIN RIGHT OUTER          |                        | 75387 |    10M|  6876 |
|  21 |      VIEW                          |                        | 61721 |  1326K|   883 |
|  22 |       NESTED LOOPS                 |                        | 61721 |  4942K|   883 |
|  23 |        HASH JOIN RIGHT OUTER       |                        | 61721 |  4520K|   876 |
|  24 |         TABLE ACCESS FULL          |                        | 47281 |   646K|   168 |
|  25 |         HASH JOIN                  |                        | 61721 |  3676K|   707 |
|  26 |          INDEX FULL SCAN           |                        |    26 |   104 |     1 |
|  27 |          HASH JOIN                 |                        | 61721 |  3435K|   705 |
|  28 |           TABLE ACCESS FULL        |                        |   705 |  8460 |     5 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  29 |           HASH JOIN                |                        | 61721 |  2712K|   700 |
|  30 |            INDEX FAST FULL SCAN    |                        |  1305 |  9135 |     2 |
|  31 |            HASH JOIN               |                        | 62017 |  2301K|   697 |
|  32 |             NESTED LOOPS           |                        |    44 |   660 |     3 |
|  33 |              TABLE ACCESS FULL     |                        |    46 |   460 |     3 |
|  34 |              INDEX RANGE SCAN      |                        |     1 |     5 |     0 |
|  35 |             TABLE ACCESS FULL      |                        | 64575 |  1450K|   693 |
|  36 |        INDEX UNIQUE SCAN           |                        |     1 |     7 |     0 |
|  37 |      NESTED LOOPS OUTER            |                        | 75387 |  9644K|  5381 |
|  38 |       HASH JOIN                    |                        | 75387 |  9128K|  5373 |
|  39 |        INDEX FAST FULL SCAN        |                        |    47 |   611 |     2 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  40 |        HASH JOIN                   |                        | 75387 |  8171K|  5370 |
|  41 |         INDEX FAST FULL SCAN       |                        |  1305 |  9135 |     2 |
|  42 |         HASH JOIN                  |                        | 75750 |  7693K|  5367 |
|  43 |          INDEX FULL SCAN           |                        |   366 |  1830 |     1 |
|  44 |          HASH JOIN RIGHT SEMI      |                        | 78544 |  7593K|  5365 |
|  45 |           VIEW                     |                        |   208K|  2643K|  2011 |
|  46 |            NESTED LOOPS OUTER      |                        |   208K|    20M|  2011 |
|  47 |             HASH JOIN              |                        |   208K|    19M|  1990 |
|  48 |              INDEX FULL SCAN       |                        |    26 |   104 |     1 |
|  49 |              HASH JOIN             |                        |   208K|    18M|  1988 |
|  50 |               TABLE ACCESS FULL    |                        |   705 |  8460 |     5 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  51 |               HASH JOIN            |                        |   208K|    16M|  1981 |
|  52 |                INDEX FAST FULL SCAN|                        |    47 |   611 |     2 |
|  53 |                HASH JOIN           |                        |   208K|    13M|  1977 |
|  54 |                 INDEX FULL SCAN    |                        |   366 |  1830 |     1 |
|  55 |                 HASH JOIN          |                        |   215K|    12M|  1975 |
|  56 |                  HASH JOIN         |                        | 64258 |  2886K|   699 |
|  57 |                   TABLE ACCESS FULL|                        |    46 |   460 |     3 |
|  58 |                   TABLE ACCESS FULL|                        | 64528 |  2268K|   695 |
|  59 |                  TABLE ACCESS FULL |                        |   216K|  3595K|   798 |
|  60 |             INDEX UNIQUE SCAN      |                        |     1 |     7 |     0 |
|  61 |           HASH JOIN                |                        |   215K|    17M|  2102 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  62 |            HASH JOIN               |                        | 64305 |  3893K|   706 |
|  63 |             TABLE ACCESS FULL      |                        |    46 |   460 |     3 |
|  64 |             HASH JOIN              |                        | 64575 |  3279K|   702 |
|  65 |              HASH JOIN             |                        |   705 | 11280 |     7 |
|  66 |               INDEX FULL SCAN      |                        |    26 |   104 |     1 |
|  67 |               TABLE ACCESS FULL    |                        |   705 |  8460 |     5 |
|  68 |              TABLE ACCESS FULL     |                        | 64575 |  2270K|   695 |
|  69 |            TABLE ACCESS FULL       |                        |   216K|  5076K|   798 |
|  70 |       INDEX UNIQUE SCAN            |                        |     1 |     7 |     0 |
|  71 |    NESTED LOOPS OUTER              |                        |   131K|    22M|  6139 |
|  72 |     HASH JOIN                      |                        |   131K|    22M|  6126 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  73 |      INDEX FAST FULL SCAN          |                        |    47 |   611 |     2 |
|  74 |      HASH JOIN                     |                        |   131K|    20M|  6123 |
|  75 |       TABLE ACCESS FULL            |                        |  1305 | 26100 |     5 |
|  76 |       HASH JOIN RIGHT ANTI         |                        |   132K|    17M|  6117 |
|  77 |        VIEW                        |                        |   208K|  2643K|  2011 |
|  78 |         NESTED LOOPS OUTER         |                        |   208K|    20M|  2011 |
|  79 |          HASH JOIN                 |                        |   208K|    19M|  1990 |
|  80 |           INDEX FULL SCAN          |                        |    26 |   104 |     1 |
|  81 |           HASH JOIN                |                        |   208K|    18M|  1988 |
|  82 |            TABLE ACCESS FULL       |                        |   705 |  8460 |     5 |
|  83 |            HASH JOIN               |                        |   208K|    16M|  1981 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  84 |             INDEX FAST FULL SCAN   |                        |    47 |   611 |     2 |
|  85 |             HASH JOIN              |                        |   208K|    13M|  1977 |
|  86 |              INDEX FULL SCAN       |                        |   366 |  1830 |     1 |
|  87 |              HASH JOIN             |                        |   215K|    12M|  1975 |
|  88 |               HASH JOIN            |                        | 64258 |  2886K|   699 |
|  89 |                TABLE ACCESS FULL   |                        |    46 |   460 |     3 |
|  90 |                TABLE ACCESS FULL   |                        | 64528 |  2268K|   695 |
|  91 |               TABLE ACCESS FULL    |                        |   216K|  3595K|   798 |
|  92 |          INDEX UNIQUE SCAN         |                        |     1 |     7 |     0 |
|  93 |        HASH JOIN                   |                        |   208K|    25M|  2465 |
|  94 |         HASH JOIN                  |                        | 62017 |  4360K|   710 |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
|  95 |          TABLE ACCESS FULL         |                        |   366 |  3660 |     3 |
|  96 |          HASH JOIN                 |                        | 64305 |  3893K|   706 |
|  97 |           TABLE ACCESS FULL        |                        |    46 |   460 |     3 |
|  98 |           HASH JOIN                |                        | 64575 |  3279K|   702 |
|  99 |            HASH JOIN               |                        |   705 | 11280 |     7 |
| 100 |             INDEX FULL SCAN        |                        |    26 |   104 |     1 |
| 101 |             TABLE ACCESS FULL      |                        |   705 |  8460 |     5 |
| 102 |            TABLE ACCESS FULL       |                        | 64575 |  2270K|   695 |
| 103 |         TABLE ACCESS FULL          |                        |   216K|    11M|   798 |
| 104 |     INDEX UNIQUE SCAN              |                        |     1 |     7 |     0 |
---------------------------------------------------------------------------------------------

既然执行计划变过来了，那么试下效果：1~2s出结果，用户说这样的效果可以，问题到此搞定，总共过程不过几分钟，面对这么大的一个执行计划，肯定需要经验才可以判断问题所在，进而想出相应的办法解决，大家可以对比参考学习，禁止转载。