ITPub博客

首页 > 数据库 > Oracle > 【优化案例】一次笛卡尔乘积的优化

【优化案例】一次笛卡尔乘积的优化

原创 Oracle 作者:azzotest 时间:2015-09-24 22:52:19 0 删除 编辑
今天一个网友妹子发来一段SQL说执行非常慢,让我看下,其实问题很简单,2分钟搞定,我们平时也会经常遇到,SQL及执行计划如下

点击(此处)折叠或打开

  1. --跑了7分钟
  2. SELECT T.*, A.*, B.LOGIN_ID, B.USER_NAME
  3.   FROM MID.T_RPT_RES_PRO_PRE_ALL_LISTTEMP T,
  4.        KPICODE.ODS_CB_ORGAN_GRID_ALL_MV A,
  5.        KPI.PURE_USER B
  6.  WHERE T.FIVE_LEVEL_OWN = B.LOGIN_ID
  7.    AND B.USER_ORG = A.LEVEL_ID;
  8.    
  9. SELECT COUNT(1) FROM MID.T_RPT_RES_PRO_PRE_ALL_LISTTEMP T; --4733
  10. SELECT COUNT(1) FROM KPICODE.ODS_CB_ORGAN_GRID_ALL_MV A; --16719
  11. SELECT COUNT(1) FROM KPI.PURE_USER B; --26497

  12. 执行计划
  13. ----------------------------------------------------------
  14. Plan hash value: 2012136974

  15. --------------------------------------------------------------------------------

  16. -------------------------------

  17. | Id | Operation | Name | Rows |
  18. Bytes | Cost (%CPU)| Time |

  19. --------------------------------------------------------------------------------

  20. -------------------------------

  21. | 0 | SELECT STATEMENT | | 1 |
  22.  1259 | 144 (1)| 00:00:02 |

  23. | 1 | NESTED LOOPS | | 1 |
  24.  1259 | 144 (1)| 00:00:02 |

  25. | 2 | MERGE JOIN CARTESIAN | | 1 |
  26.  1236 | 144 (1)| 00:00:02 |

  27. | 3 | TABLE ACCESS FULL | T_RPT_RES_PRO_PRE_ALL_LISTTEMP | 1 |
  28.  1138 | 2 (0)| 00:00:01 |

  29. | 4 | BUFFER SORT | | 16690 |
  30.  1597K| 142 (1)| 00:00:02 |

  31. | 5 | MAT_VIEW ACCESS FULL | ODS_CB_ORGAN_GRID_ALL_MV | 16690 |
  32.  1597K| 142 (1)| 00:00:02 |

  33. |* 6 | TABLE ACCESS BY INDEX ROWID| PURE_USER | 1 |
  34.    23 | 0 (0)| 00:00:01 |

  35. |* 7 | INDEX UNIQUE SCAN | UK_PURE_USER_LOGIN_ID | 1 |
  36.       | 0 (0)| 00:00:01 |

  37. --------------------------------------------------------------------------------

  38. -------------------------------


  39. Predicate Information (identified by operation id):
  40. ---------------------------------------------------

  41.    6 - filter("B"."USER_ORG"="A"."LEVEL_ID")
  42.    7 - access("T"."FIVE_LEVEL_OWN"="B"."LOGIN_ID")


  43. 统计信息
  44. ----------------------------------------------------------
  45.           1 recursive calls
  46.           0 db block gets
  47.   177858134 consistent gets
  48.           0 physical reads
  49.           0 redo size
  50.     1436253 bytes sent via SQL*Net to client
  51.        2157 bytes received via SQL*Net from client
  52.         276 SQL*Net roundtrips to/from client
  53.           1 sorts (memory)
  54.           0 sorts (disk)
  55.        4125 rows processed
可以看到2个表和一个物化视图关联,数据量都不大(几万条数据),却执行了7分钟,1.7亿的逻辑读!!
看下执行计划,ID=2的MERGE JOIN CARTESIAN,
看到这个就基本明白怎么回事了,MERGE JOIN CARTESIAN是笛卡尔乘积的意思,这样的一个SQL里为什么会出现笛卡尔积呢?

看下where条件
  FROM MID.T_RPT_RES_PRO_PRE_ALL_LISTTEMP T,
       KPICODE.ODS_CB_ORGAN_GRID_ALL_MV   A,
       KPI.PURE_USER                      B
 WHERE T.FIVE_LEVEL_OWN = B.LOGIN_ID
   AND B.USER_ORG = A.LEVEL_ID;
3表关联T和B关联,然后B和A关联,没有漏关联条件,而走了笛卡尔积。我们可以看到是T表和A表做了笛卡尔积,再跟B表做了NL
然而关联条件确实T和B关联 B和A关联,T和A并没有直接的关联关系,
所以可以看出这里CBO错误的评估让T和A没有关联关系的表先做了关联,当然产生了笛卡尔积,想解决这个问题也很简单,
加hint让执行计划按照我想要的路径走,use_hash(T,B,A)即可,改后秒出数据。

止于为什么Oracle会选择走这样的执行计划,可以说是Oracle的一个BUG,错误的评估导致了没有关联的表关联了起来。
有人说在10g02版本此bug已经修复,但是仍旧经常出现这个问题,所以大家如果遇到此问题可以直接指定执行路径即可。

还有人说可以直接修改当前session禁用笛卡尔积,使用/*+ OPT_PARAM('_optimizer_mjc_enabled','false') */,于是我也让妹子试了试发现依旧很慢,
我看了下执行计划,执行计划中虽然去掉了笛卡尔积,但路径没有改变,依旧是T和A先关联,不过使用的是NL关联。所以依旧很慢


晚安。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26224278/viewspace-1808660/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-08-21

  • 博文量
    22
  • 访问量
    46441