1分钟搞定超慢SQL
前几天，一个用户的研发人员找到我了，说他们有个SQL语句非常慢，我说多慢？他们说：半个小时也没出结果。于是问他们要了SQL语句和执行计划，SQL语句就不能再这里贴出来了，下面是调整前的执行计划（略去某些细节信息）：
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出结果，用户说这样的效果可以，问题到此搞定，总共过程不过几分钟，面对这么大的一个执行计划，肯定需要经验才可以判断问题所在，进而想出相应的办法解决，大家可以对比参考学习，禁止转载。
