2012 年，一位DBA 请求协助优化如下 SQL。

    1. SELECT "A1"."CODE", "A1"."DEVICE_ID", "A1"."SIDEB_PORT_ID", "A1"."VERSION"  
    2.   FROM (SELECT   
    3.          "A2"."CODE" "CODE",  
    4.          "A2"."DEVICE_ID" "DEVICE_ID",  
    5.          "A2"."SIDEB_PORT_ID" "SIDEB_PORT_ID",  
    6.          "A3"."VERSION" "VERSION",  
    7.          ROW_NUMBER() OVER(PARTITION BY "A4"."PROD_ID" ORDER BY "A4"."HIST_TIME" DESC) "RN"  
    8.           FROM "RM"."H_PROD_2_RF_SERV"            "A4",  
    9.                "RM"."H_RSC_FACING_SERV_LINE_ITEM" "A3",  
    10.                "RM"."CONNECTOR"                   "A2"  
    11.          WHERE "A4"."SERV_ID" = "A3"."SERV_ID"  
    12.            AND "A3"."LINE_ID" = "A2"."CONNECTOR_ID"  
    13.            AND EXISTS (SELECT 0  
    14.                   FROM "RM"."DEVICE_ITEM" "A5"  
    15.                  WHERE "A5"."DEVICE_ID" = "A2"."DEVICE_ID"  
    16.                    AND "A5"."ITEM_SPEC_ID" = 200006  
    17.                    AND "A5"."VALUE" ='7')  
    18.            AND "A4"."PROD_ID" = 313) "A1"  
    19.  WHERE "A1"."RN" = 1;

    执行计划如下。

    1. -------------------------------------------------------------------------------------
    2. | Id |Operation                        |Name                       |Rows|Bytes| Cost (%CPU)|
    3. -------------------------------------------------------------------------------------
    4. |  0 |SELECT STATEMENT                 |                           |   1|  175|    20  (10)|
    5. |* 1 | VIEW                            |                           |   1|  175|    20  (10)|
    6. |* 2 |  WINDOW SORT PUSHED RANK        |                           |   1|  109|    20  (10)|
    7. |  3 |   NESTED LOOPS                  |                           |   1|  109|    19   (6)|
    8. |  4 |    NESTED LOOPS                 |                           |   1|   80|    17   (6)|
    9. |  5 |     MERGE JOIN CARTESIAN        |                           |   1|   60|    13   (8)|
    10. |  6 |      SORT UNIQUE                |                           |   1|   36|     6   (0)|
    11. |* 7 |      TABLE ACCESS BY INDEX ROWID|DEVICE_ITEM                |   1|   36|     6   (0)|
    12. |* 8 |        INDEX RANGE SCAN         |IDX_DEVICE_ITEM_VALE       |   9|     |     4   (0)|
    13. |  9 |      BUFFER SORT                |                           |   4|   96|     7  (15)|
    14. | 10 |      TABLE ACCESS BY INDEX ROWID|H_PROD_2_RF_SERV           |   4|   96|     6   (0)|
    15. |*11 |        INDEX RANGE SCAN         |IDX_HP2RS_PRODID_SERVID    |   4|     |     2   (0)|
    16. | 12 |     TABLE ACCESS BY INDEX ROWID |H_RSC_FACING_SERV_LINE_ITEM|   2|   40|     4   (0)|
    17. |*13 |      INDEX RANGE SCAN           |IDX_HRFSLI_SERV            |   2|     |     2   (0)|
    18. |*14 |    TABLE ACCESS BY INDEX ROWID  |CONNECTOR                  |   1|   29|     2   (0)|
    19. |*15 |     INDEX UNIQUE SCAN           |PK_CONNECTOR               |   1|     |     1   (0)|
    20. -------------------------------------------------------------------------------------
    22. Predicate Information (identified by operation id):  
    23. ---------------------------------------------------  
    25.    1 - filter("A1"."RN"=1)  
    26.    2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A4"."PROD_ID" ORDER BY  
    27.               INTERNAL_FUNCTION("A4"."HIST_TIME") DESC )<=1)  
    28.    7 - filter("A5"."ITEM_SPEC_ID"=200006)  
    29.    8 - access("A5"."VALUE"='7')  
    30.   11 - access("A4"."PROD_ID"=313)  
    31.   13 - access("A4"."SERV_ID"="A3"."SERV_ID")  
    32.   14 - filter("A5"."DEVICE_ID"="A2"."DEVICE_ID")  
    33.   15 - access("A3"."LINE_ID"="A2"."CONNECTOR_ID")  
    35. Statistics
    36. ----------------------------------------------------------  
    37.           0  recursive calls  
    38.           0  db block gets  
    39.     2539920  consistent gets  
    40.           0  physical reads  
    41.           0  redo size  
    42.         735  bytes sent via SQL*Net to client  
    43.         492  bytes received via SQL*Net from client  
    44.           2  SQL*Net roundtrips to/from client  
    45.           3  sorts (memory)  
    46.           0  sorts (disk)  
    47.           1  rows processed

    该 SQL 要执行 9.437 秒，只返回一行数据，其中 A5 有 48 194 511 行数据，A2 有 35 467 304 行数据，其余表都是小表。

    首先，笔者运用 SQL 三段分拆方法，检查 SQL 写法，经过检查，SQL 写法没有问题。

    其次笔者检查执行计划。执行计划中 Id=5 出现了 MERGE JOIN CARTESIAN，这一般都是统计信息收集不准确，将离 MERGE JOIN CARTESIAN 关键字最近的表（Id=7）Rows 估算为 1 导致。

    正常情况下，应该先检查 SQL 中所有表的统计信息是否过期，如果统计信息过期了应该立即收集。因为做了太多的 SQL 优化，遇到 SQL 出现了性能问题，已经形成条件反射想要立刻优化它，所以，当时没有立即对表收集统计信息。

    如果想要从执行计划入手优化 SQL，我们一般要从执行计划的入口开始检查，检查 Rows 估算是否准确。当然了，如果执行计划中有明显值得怀疑的地方，我们也可以直接检查值得怀疑之处。

    执行计划的入口是 Id=8，Id=8 是索引范围扫描，通过 Id=7 回表。于是让朋友运行如下 SQL。

    1. SELECT COUNT(*)
    2.   FROM "RM"."DEVICE_ITEM" "A5"
    3.  WHERE "A5"."ITEM_SPEC_ID" = 200006
    4.    AND "A5"."VALUE" = '7';

    得到反馈，上面查询返回 68 384 行数据。其次，查询执行计划中 Id=11 和 Id=10 应该返回多少数据（A4），运行如下 SQL。

    1. select count(*) from H_PROD_2_RF_SERV where prod_id = 313;

    得到反馈，上面查询返回 6 行数据。根据以上信息我们知道应该怎么优化上述 SQL 了。我们再来查看原始 SQL 的部分代码。

    1. FROM "RM"."H_PROD_2_RF_SERV"            "A4",  
    2.                "RM"."H_RSC_FACING_SERV_LINE_ITEM" "A3",  
    3.                "RM"."CONNECTOR"                   "A2"  
    4.          WHERE "A4"."SERV_ID" = "A3"."SERV_ID"  
    5.            AND "A3"."LINE_ID" = "A2"."CONNECTOR_ID"  
    6.            AND EXISTS (SELECT 0  
    7.                   FROM "RM"."DEVICE_ITEM" "A5"  
    8.                  WHERE "A5"."DEVICE_ID" = "A2"."DEVICE_ID"  
    9.                    AND "A5"."ITEM_SPEC_ID" = 200006  
    10.                    AND "A5"."VALUE" ='7')  
    11.            AND "A4"."PROD_ID" = 313)

    A4 过滤后只返回 6 行数据，A3 是小表，A2 有 35 467 304 行数据，A5 过滤后返回 6 万行数据，其中 A3，A2 都没有过滤条件。

    SQL 语句中 A4 与 A3 进行关联，因为 A4 过滤后返回 6 行数据，A3 是小表，所以让 A4 作为驱动表 leading（a4），与 A3 使用嵌套循环 use_nl（a4,a3）方式进行关联，关联之后得到一个结果集，因为 A4 与 A3 返回数据量都很小，所以关联之后的结果集也必然很小。

    因为 A2 表很大，而且 A2 没有过滤条件，所以我们不能让 A2 走 HASH 连接，因为没有过滤条件，使用 HASH 进行关联只能走全表扫描。如果让 A2 走嵌套循环，作为嵌套循环被驱动表，那么我们可以让 A2 走连接列的索引，这样就避免了大表 A2 因为没有过滤条件而走全表扫描。因此，我们将 A4 与 A3 关联之后的结果集作为嵌套循环驱动表，然后再与 A2 使用嵌套循环进行关联：use_nl（a3,a2）。

    因为 A5 过滤后有 6 万行数据，所以我们让 A5 与 A2 进行 HASH 连接，最终添加如下 HINT。

    1. SELECT "A1"."CODE", "A1"."DEVICE_ID", "A1"."SIDEB_PORT_ID", "A1"."VERSION"
    2.    FROM (SELECT /*+ leading(a4) use_nl(a4,a3) use_nl(a3,a2) */
    3.           "A2"."CODE" "CODE",
    4.           "A2"."DEVICE_ID" "DEVICE_ID",
    5.           "A2"."SIDEB_PORT_ID" "SIDEB_PORT_ID",
    6.           "A3"."VERSION" "VERSION",
    7.           ROW_NUMBER() OVER(PARTITION BY "A4"."PROD_ID" ORDER BY "A4"."HIST_TIME" DESC) "RN"
    8.            FROM "RM"."H_PROD_2_RF_SERV"            "A4",
    9.                 "RM"."H_RSC_FACING_SERV_LINE_ITEM" "A3",
    10.                 "RM"."CONNECTOR"                   "A2"
    11.           WHERE "A4"."SERV_ID" = "A3"."SERV_ID"
    12.             AND "A3"."LINE_ID" = "A2"."CONNECTOR_ID"
    13.             AND EXISTS (SELECT /*+ hash_sj */ 0
    14.                    FROM "RM"."DEVICE_ITEM" "A5"
    15.                   WHERE "A5"."DEVICE_ID" = "A2"."DEVICE_ID"
    16.                     AND "A5"."ITEM_SPEC_ID" = 200006
    17.                     AND "A5"."VALUE" ='7')
    18.             AND "A4"."PROD_ID" = 313) "A1"
    19.   WHERE "A1"."RN" = 1;

    执行计划如下。

    1. -------------------------------------------------------------------------------------
    2. | Id|Operation                       |Name                       |Rows|Bytes|Cost(%CPU)|
    3. ----------------------------------------------------------------------------------------
    4. |  0|SELECT STATEMENT                |                           |   1|  175|  40   (3)|
    5. |* 1| VIEW                           |                           |   1|  175|  40   (3)|
    6. |* 2|  WINDOW SORT PUSHED RANK       |                           |   1|  109|  40   (3)|
    7. |* 3|   HASH JOIN SEMI               |                           |   1|  109|  39   (0)|
    8. |  4|    NESTED LOOPS                |                           |   7|  511|  33   (0)|
    9. |  5|     NESTED LOOPS               |                           |   7|  308|  19   (0)|
    10. |  6|     TABLE ACCESS BY INDEX ROWID|H_PROD_2_RF_SERV           |   4|   96|   7   (0)|
    11. |* 7|       INDEX RANGE SCAN         |IDX_HP2RS_PRODID_SERVID    |   4|     |   3   (0)|
    12. |  8|     TABLE ACCESS BY INDEX ROWID|H_RSC_FACING_SERV_LINE_ITEM|   2|   40|   4   (0)|
    13. |* 9|       INDEX RANGE SCAN         |IDX_HRFSLI_SERV            |   2|     |   2   (0)|
    14. | 10|     TABLE ACCESS BY INDEX ROWID|CONNECTOR                  |   1|   29|   2   (0)|
    15. |*11|      INDEX UNIQUE SCAN         |PK_CONNECTOR               |   1|     |   1   (0)|
    16. |*12|    TABLE ACCESS BY INDEX ROWID |DEVICE_ITEM                |   1|   36|   6   (0)|
    17. |*13|     INDEX RANGE SCAN           |IDX_DEVICE_ITEM_VALE       |   9|     |   4   (0)|
    18. -------------------------------------------------------------------------------------
    20. Predicate Information (identified by operation id):
    21. ---------------------------------------------------
    23.    1 - filter("A1"."RN"=1)  
    24.    2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A4"."PROD_ID" ORDER BY
    25.               INTERNAL_FUNCTION("A4"."HIST_TIME") DESC )<=1)
    26.    3 - access("A5"."DEVICE_ID"="A2"."DEVICE_ID")
    27.    7 - access("A4"."PROD_ID"=313)
    28.    9 - access("A4"."SERV_ID"="A3"."SERV_ID")
    29.   11 - access("A3"."LINE_ID"="A2"."CONNECTOR_ID")
    30.   12 - filter("A5"."ITEM_SPEC_ID"=200006)
    31.   13 - access("A5"."VALUE"='7')  
    33. Statistics  
    34. ----------------------------------------------------------  
    35.           0  recursive calls  
    36.           0  db block gets  
    37.       14770  consistent gets  
    38.           0  physical reads  
    39.           0  redo size  
    40.         735  bytes sent via SQL*Net to client  
    41.         492  bytes received via SQL*Net from client  
    42.           2  SQL*Net roundtrips to/from client  
    43.           1  sorts (memory)  
    44.           0  sorts (disk)  
    45.           1  rows processed

    最终该 SQL 只需 0.188 秒就能出结果，逻辑读从最开始的 2 539 920 下降到 14 770。

    当具备一定优化理论知识之后，我们可以不看执行计划，直接根据 SQL 写法找到 SQL 语句中返回数据量最小的表作为驱动表，然后看它与谁进行关联，根据关联返回的数据量判断走 NL 还是 HASH，然后一直这样进行下去，直到 SQL 语句中所有表都关联完毕。如果大家长期采用此方法进行锻炼，久而久之，你自己的脑袋就是 CBO。