查看: 3560|回复: 10

如何优化HASH JOIN?

[复制链接]
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-1-27 11:35 | 显示全部楼层 |阅读模式
|*  7 |HASH JOIN                      |                      |      1 | 808 |   2630 |00:03:12.95 |   29630 |

|   8 |     MERGE JOIN CARTESIAN          |                      |      1 | 13655 |  32725 |00:00:00.51 |   26245 |

|*  9 |           TABLE ACCESS FULL            | PRPJPOLICYPAYMENT    |      1 |  846 |   1925 |00:00:00.48 |   26238 |

|  10 |           BUFFER SORT                  |                      |   1925 |  16 |  32725 |00:00:00.01 |       7 |

|* 11 |              TABLE ACCESS FULL           | PRPJPAYWAY           |      1 | 16 |     17 |00:00:00.01 |       7 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 12 |    TABLE ACCESS FULL             | PRPJPAYMENTDETAIL    |      1 | 46215 |    124K|00:00:00.12 |    3385 |

如上第12步和第8步做了HASH JION,时间为3分多。
我想问问这里HASH如果想办法优化?或者重哪里入手,查看HASH JION用了3分多钟的原因
论坛徽章:
51
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:22铁扇公主
日期:2012-02-21 15:03:13最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-05-22 15:10:11ITPUB 11周年纪念徽章
日期:2012-10-09 18:13:332013年新春福章
日期:2013-02-25 14:51:24ITPUB社区12周年站庆徽章
日期:2013-08-12 09:34:36itpub13周年纪念徽章
日期:2014-09-28 10:55:55
发表于 2011-1-27 11:41 | 显示全部楼层

回复 #1 gaopengtttt 的帖子

这个问题我觉得问的有点矛盾

首先是为什么要优化HASH JOIN 3 分钟慢是吗?
那么快的是什么啊?
你试过别的连接方式吗?

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-1-27 11:50 | 显示全部楼层
语句出问题片段我已经找到了就是
select  /*+  gather_plan_statistics */ a.payrefdate,
                               a.appliname,
                               a.certino,
                               a.policyno,
                               a.currency2,
                               case
                                 when b.attribute4 = '1' then
                                  b.payreffee
                                 else
                                  a.payreffee
                               end payreffee,
                               0 bankComm,
                               c.paywaycname || '-' || case
                                 when c.paywaytype = '1' then
                                  '收'
                                 when c.paywaytype = '2' then
                                  '付'
                               end payway,
                               case
                                 when a.poatype = '1' then
                                  a.poainfo
                                 else
                                  ''
                               end,
                               a.attribute2,
                               b.accountno,
                               b.accountname,
                               a.rencnt,
                               a.edrno,
                               a.payrefcode,
                               case
                                 when a.realpayrefno = b.realpayrefno then
                                  a.voucherno
                                 else
                                  a.tovoucherno
                               end,
                               b.checkNo,
                               a.comcode,
                               a.planserialno
                          from prod.test1 a,
                               prod.test2 b,
                               prod.test3        c
                         where (a.torealpayrefno = b.realpayrefno or
                               a.realpayrefno = b.realpayrefno)
                           and b.paytype not in ('50', '51', '60', '61')
                           and c.remark = '1'
                           and c.validstatus = '1'
                           and b.payway = c.paywaycode
                           and b.payway <> '212'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') >=
                               '2011-01-11'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') <=
                               '2011-01-12';

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-1-27 11:53 | 显示全部楼层
或者说如何找到HASH JION 好用3分钟的原因

SQL> select count(*) from test1;

  COUNT(*)
----------
    346987

SQL> select count(*) from test2;

  COUNT(*)
----------
    128072

SQL> select count(*) from test3;

  COUNT(*)
----------
        32

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-1-27 11:55 | 显示全部楼层
我倒使用物理临时表空间。
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3443708996
--------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes |TempSpc| Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   808 |   183K|       |  775
|*  1 |  HASH JOIN            |                   |   808 |   183K|  2648K|  775

[ 本帖最后由 gaopengtttt 于 2011-1-27 11:57 编辑 ]

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-1-27 12:03 | 显示全部楼层
我给出原表执行计划
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3443708996
--------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes |TempSpc| Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   808 |   183K|       |  775
|*  1 |  HASH JOIN            |                   |   808 |   183K|  2648K|  775
|   2 |   MERGE JOIN CARTESIAN|                   | 13655 |  2480K|       |  677
|*  3 |    TABLE ACCESS FULL  | PRPJPOLICYPAYMENT |   846 |   128K|       |  584
|   4 |    BUFFER SORT        |                   |    16 |   480 |       |   93
|*  5 |     TABLE ACCESS FULL | PRPJPAYWAY        |    16 |   480 |       |
|*  6 |   TABLE ACCESS FULL   | PRPJPAYMENTDETAIL | 46215 |  2121K|       |   51
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."PAYWAY"="C"."PAYWAYCODE")
       filter("A"."TOREALPAYREFNO"="B"."REALPAYREFNO" AND "A"."TOREALPAYREFNO" I
              OR "A"."REALPAYREFNO"="B"."REALPAYREFNO")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - filter(TO_CHAR(INTERNAL_FUNCTION("A"."PAYREFDATE"),'yyyy-mm-dd')>='2011-0
              TO_CHAR(INTERNAL_FUNCTION("A"."PAYREFDATE"),'yyyy-mm-dd')<='2011-0
   5 - filter("C"."REMARK"='1' AND "C"."VALIDSTATUS"='1' AND "C"."PAYWAYCODE"<>'
   6 - filter("B"."PAYTYPE"<>'50' AND "B"."PAYTYPE"<>'51' AND "B"."PAYTYPE"<>'60
              "B"."PAYTYPE"<>'61' AND "B"."PAYWAY"<>'212')

25 rows selected

我感觉这里
where (a.torealpayrefno = b.realpayrefno or
                               a.realpayrefno = b.realpayrefno)

有问题,这里本来是来做连接条件的,但是执行计划把他用为了过滤条件,产生了笛卡尔及。。。

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2011-1-27 12:07 | 显示全部楼层
select  /*+  use_concat */ a.payrefdate,
                               a.appliname,
                               a.certino,
                               a.policyno,
                               a.currency2,
                               case
                                 when b.attribute4 = '1' then
                                  b.payreffee
                                 else
                                  a.payreffee
                               end payreffee,
                               0 bankComm,
                               c.paywaycname || '-' || case
                                 when c.paywaytype = '1' then
                                  '收'
                                 when c.paywaytype = '2' then
                                  '付'
                               end payway,
                               case
                                 when a.poatype = '1' then
                                  a.poainfo
                                 else
                                  ''
                               end,
                               a.attribute2,
                               b.accountno,
                               b.accountname,
                               a.rencnt,
                               a.edrno,
                               a.payrefcode,
                               case
                                 when a.realpayrefno = b.realpayrefno then
                                  a.voucherno
                                 else
                                  a.tovoucherno
                               end,
                               b.checkNo,
                               a.comcode,
                               a.planserialno
                          from prod.test1 a,
                               prod.test2 b,
                               prod.test3        c
                         where (a.torealpayrefno = b.realpayrefno or
                               a.realpayrefno = b.realpayrefno)
                           and b.paytype not in ('50', '51', '60', '61')
                           and c.remark = '1'
                           and c.validstatus = '1'
                           and b.payway = c.paywaycode
                           and b.payway <> '212'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') >=
                               '2011-01-11'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') <=
                               '2011-01-12';

试试

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-1-27 12:10 | 显示全部楼层
解决了 看来问题找倒了。。
现在没有笛卡尔及了,速度提高了90%
使用UNION 代替了OR
select                         a.payrefdate,
                               a.appliname,
                               a.certino,
                               a.policyno,
                               a.currency2,
                               case
                                 when b.attribute4 = '1' then
                                  b.payreffee
                                 else
                                  a.payreffee
                               end payreffee,
                               0 bankComm,
                               c.paywaycname || '-' || case
                                 when c.paywaytype = '1' then
                                  '收'
                                 when c.paywaytype = '2' then
                                  '付'
                               end payway,
                               case
                                 when a.poatype = '1' then
                                  a.poainfo
                                 else
                                  ''
                               end,
                               a.attribute2,
                               b.accountno,
                               b.accountname,
                               a.rencnt,
                               a.edrno,
                               a.payrefcode,
                               case
                                 when a.realpayrefno = b.realpayrefno then
                                  a.voucherno
                                 else
                                  a.tovoucherno
                               end,
                               b.checkNo,
                               a.comcode,
                               a.planserialno
                          from prod.prpjpolicypayment a,
                               prod.prpjpaymentdetail b,
                               prod.prpjpayway        c
                         where a.torealpayrefno = b.realpayrefno                       
                           and b.paytype not in ('50', '51', '60', '61')
                           and c.remark = '1'
                           and c.validstatus = '1'
                           and b.payway = c.paywaycode
                           and b.payway <> '212'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') >=
                               '2011-01-11'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') <=
                               '2011-01-12'                 
                         union ALL
  select                       a.payrefdate,
                               a.appliname,
                               a.certino,
                               a.policyno,
                               a.currency2,
                               case
                                 when b.attribute4 = '1' then
                                  b.payreffee
                                 else
                                  a.payreffee
                               end payreffee,
                               0 bankComm,
                               c.paywaycname || '-' || case
                                 when c.paywaytype = '1' then
                                  '收'
                                 when c.paywaytype = '2' then
                                  '付'
                               end payway,
                               case
                                 when a.poatype = '1' then
                                  a.poainfo
                                 else
                                  ''
                               end,
                               a.attribute2,
                               b.accountno,
                               b.accountname,
                               a.rencnt,
                               a.edrno,
                               a.payrefcode,
                               case
                                 when a.realpayrefno = b.realpayrefno then
                                  a.voucherno
                                 else
                                  a.tovoucherno
                               end,
                               b.checkNo,
                               a.comcode,
                               a.planserialno
                          from prod.prpjpolicypayment a,
                               prod.prpjpaymentdetail b,
                               prod.prpjpayway        c
                         where a.realpayrefno = b.realpayrefno                        
                           and b.paytype not in ('50', '51', '60', '61')
                           and c.remark = '1'
                           and c.validstatus = '1'
                           and b.payway = c.paywaycode
                           and b.payway <> '212'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') >=
                               '2011-01-11'
                           AND to_char(a.payRefDate, 'yyyy-mm-dd') <=
                               '2011-01-12';

[ 本帖最后由 gaopengtttt 于 2011-1-27 12:17 编辑 ]

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-1-27 12:12 | 显示全部楼层
原因就是执行计划没有很好的使用连接条件,结果A和B表做了笛卡尔及。
然后HASH完了再做的过滤。那肯定慢啊。HASH超过了HASR_AREA_SIZE的大小,使用TEMPSPACE那肯定慢了。
2M啊

使用道具 举报

回复
论坛徽章:
1
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59
发表于 2012-10-22 12:25 | 显示全部楼层
  1. SQL> select a.*,b.* from test1 a ,test2 b,test3 c where (a.id=b.id or a.key=b.id)  and b.id=c.id and a.id <100;

  2. 99 rows selected.


  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 2695691022

  6. ------------------------------------------------------------------------------
  7. | Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
  8. ------------------------------------------------------------------------------
  9. |   0 | SELECT STATEMENT     |       |     4 |   260 |    20  (10)| 00:00:01 |
  10. |   1 |  CONCATENATION       |       |       |       |            |          |
  11. |*  2 |   HASH JOIN          |       |     3 |   195 |    10  (10)| 00:00:01 |
  12. |*  3 |    HASH JOIN         |       |     8 |   416 |     7  (15)| 00:00:01 |
  13. |*  4 |     TABLE ACCESS FULL| TEST1 |    20 |   520 |     3   (0)| 00:00:01 |
  14. |   5 |     TABLE ACCESS FULL| TEST2 |   409 | 10634 |     3   (0)| 00:00:01 |
  15. |   6 |    TABLE ACCESS FULL | TEST3 |   409 |  5317 |     3   (0)| 00:00:01 |
  16. |*  7 |   HASH JOIN          |       |     1 |    65 |    10  (10)| 00:00:01 |
  17. |*  8 |    HASH JOIN         |       |     1 |    52 |     7  (15)| 00:00:01 |
  18. |*  9 |     TABLE ACCESS FULL| TEST1 |    20 |   520 |     3   (0)| 00:00:01 |
  19. |  10 |     TABLE ACCESS FULL| TEST2 |   409 | 10634 |     3   (0)| 00:00:01 |
  20. |  11 |    TABLE ACCESS FULL | TEST3 |   409 |  5317 |     3   (0)| 00:00:01 |
  21. ------------------------------------------------------------------------------

  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------

  24.    2 - access("B"."ID"="C"."ID")
  25.    3 - access("A"."KEY"="B"."ID")
  26.    4 - filter("A"."ID"<100)
  27.    7 - access("B"."ID"="C"."ID")
  28.    8 - access("A"."ID"="B"."ID")
  29.        filter(LNNVL("A"."KEY"="B"."ID"))
  30.    9 - filter("A"."ID"<100)

  31. Note
  32. -----
  33.    - dynamic sampling used for this statement
复制代码
没做出你的效果来

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表