oracle 隐含参数导致执行计划走 merge join cartesian，导致cpu 100%

    抓取线上系统一个慢sql分析时，发现执行计划里有 merge join cartesian，cpu占用 100%。根据以往经验，基本为统计信息不准确造成的，且大概率为不合理的执行计划。
详细分析表之间的关联时，发现确实会导致超大结果集后再和其它表关联

--增加hit
select /*+ ordered use_nl(a b) */

修改 _optimizer_mjc_enabled
alter system set "_optimizer_mjc_enabled" = false;


--问题执行计划 
|  23 |  NESTED LOOPS OUTER                           |                          |     8 |  4696 |    83G  (1)|904:23:32 |       |       |
|  24 |   NESTED LOOPS OUTER                          |                          |     8 |  4376 |    83G  (1)|904:23:32 |       |       |
|  25 |    NESTED LOOPS                               |                          |     6 |  3108 |    83G  (1)|904:23:32 |       |       |
|  26 |     MERGE JOIN CARTESIAN                      |                          |    13M|  2432M|    24M  (1)| 00:16:13 |       |       |
|  27 |      PARTITION LIST SINGLE                    |                          |    12M|  1700M|   114K  (1)| 00:00:05 |   KEY |   KEY |
|* 28 |       TABLE ACCESS FULL                       | REP_CUS_INFO_M           |    12M|  1700M|   114K  (1)| 00:00:05 |   KEY |   KEY |
|  29 |      BUFFER SORT                              |                          |     1 |    45 |    24M  (1)| 00:16:08 |       |       |
|  30 |       TABLE ACCESS BY INDEX ROWID BATCHED     | S_BCH_VIEW               |     1 |    45 |     2   (0)| 00:00:01 |       |       |
|* 31 |        INDEX RANGE SCAN                       | I_S_BCH_VIEW_003         |     1 |       |     1   (0)| 00:00:01 |       |       |
|  32 |     PARTITION LIST SINGLE                     |                          |     1 |   329 | 13885   (1)| 00:00:01 |   KEY |   KEY |
|* 33 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| REP_LOAN_INFO_M          |     1 |   329 | 13885   (1)| 00:00:01 |   KEY |   KEY |



--正确的执行计划

|  23 |  NESTED LOOPS OUTER                            |                          |     8 |  4696 | 13970   (1)| 00:00:01 |       |       |
|  24 |   NESTED LOOPS OUTER                           |                          |     8 |  4376 | 13930   (1)| 00:00:01 |       |       |
|  25 |    NESTED LOOPS                                |                          |     6 |  3108 | 13906   (1)| 00:00:01 |       |       |
|  26 |     NESTED LOOPS                               |                          |     6 |  2244 | 13888   (1)| 00:00:01 |       |       |
|  27 |      TABLE ACCESS BY INDEX ROWID BATCHED       | S_BCH_VIEW               |     1 |    45 |     3   (0)| 00:00:01 |       |       |
|* 28 |       INDEX RANGE SCAN                         | I_S_BCH_VIEW_003         |     1 |       |     2   (0)| 00:00:01 |       |       |
|  29 |      PARTITION LIST SINGLE                     |                          |     5 |  1645 | 13885   (1)| 00:00:01 |   KEY |   KEY |
|* 30 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| REP_LOAN_INFO_M          |     5 |  1645 | 13885   (1)| 00:00:01 |   KEY |   KEY |
|* 31 |        INDEX RANGE SCAN                        | IDX_REP_LOAN_INFO_M_BRID | 15331 |       |    58   (0)| 00:00:01 |   KEY |   KEY |
|  32 |     PARTITION LIST SINGLE                      |                          |     1 |   144 |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 33 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | REP_CUS_INFO_M           |     1 |   144 |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 34 |       INDEX RANGE SCAN                         | IDX_REP_CUS_INFO_M_02    |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
复制