查看: 54267|回复: 160

[精华] 对Hash Join的一次优化

[复制链接]
论坛徽章:
27
授权会员
日期:2005-10-30 17:05:33管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:36优秀写手
日期:2013-12-18 09:29:13马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
发表于 2008-3-17 11:47 | 显示全部楼层 |阅读模式
前两天解决了一个优化SQL的case,SQL语句如下,big_table为150G大小,small_table很小,9000多条记录,不到1M大小
hash_area_size, sort_area_size均设置足够大,可以进行optimal hash join和memory sort


select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category  = b.from_cat or
       a.category2 = b.from_cat) and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate;

[PHP]
--------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     2 |   174 |    18  (17)|
|   1 |  SORT UNIQUE         |              |     2 |   174 |    18  (17)|
|*  2 |   HASH JOIN          |              |     2 |   174 |    17  (12)|
|   3 |    TABLE ACCESS FULL | SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  4 |    TABLE ACCESS FULL | BIG_TABLE    |     4 |   244 |     3  (34)|
--------------------------------------------------------------------------

[/PHP]
粗略来看,PLAN非常的完美,SQL HINT写的也很到位,小表在内build hash table,大表在外进行probe操作,
根据经验来看,整个SQL执行的时间应该和FTS BIG_TABLE的时间差不多

但是FTS BIG_TABLE的时间大约是8分钟,而真个SQL执行的时间长达3~4小时

那么问题究竟出在哪里?

FTS时间应该不会有太大变化,那么问题应该在hash join,设置event来trace一下hash join的过程。

SQL> alter session set events '10104 trace name context forever, level 2';

Session altered.

select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category  = b.from_cat or
       a.category2 = b.from_cat) and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate;

从trace file中Hash Table中这一段找出了问题所在:


### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:      16373
Number of buckets with   1 rows:          0
Number of buckets with   2 rows:          0
Number of buckets with   3 rows:          1
Number of buckets with   4 rows:          0
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          1
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          1
Number of buckets with between  20 and  29 rows:          1
Number of buckets with between  30 and  39 rows:          3
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          4
### Hash table overall statistics ###
Total buckets: 16384 Empty buckets: 16373 Non-empty buckets: 11
Total number of rows: 9232
Maximum number of rows in a bucket: 2531
Average number of rows in non-empty buckets: 839.272705

仔细看,在一个bucket中最多的行数竟然有2531行,因为bucket中是一个链表的结构,所以这几千行都是串在一个链表上。
由这一点想到这个Hash Table所依赖的hash key的distinct value可能太少,重复值太多。否则不应该会有这么多行在同一个bucket里面。

因为Join条件里面有两个列from_cat和site_id,穷举法有三种情况

1. Build hash table based on (from_cat,site_id):


SQL> select site_id,from_cat,count(*) from SMALL_TABLE group by site_id,from_cat having count(*)>100;

no rows selected

2. Build hash table based on (from_cat):

SQL> select from_cat,count(*) from SMALL_TABLE group by from_cat having count(*)>100;

no rows selected

3. Build hash table based on (site_id):

SQL> select site_id,count(*) from SMALL_TABLE group by site_id having count(*)>100;

   SITE_ID   COUNT(*)
---------- ----------
         0       2531
         2       2527
       146       1490
       210       2526

到这里可以发现,基于site_id这种情况和trace file中这两行很相符:

Number of buckets with 100 or more rows: 4
Maximum number of rows in a bucket: 2531

所以推断这个hash table是基于site_id而建的,而Big_Table中大量的行site_id=0,都落在这个linked list最长的bucket中.
而大部分行都会扫描完整个链表而最后被丢弃掉,所以这个Hash Join的操作效率非常差,几乎变为了Nest Loop操作

找到了根本原因,问题也就迎刃而解了。

理想状况下,hash table应当建立于(site_id,from_cat)上,那么问题肯定出在这个OR上,把OR用UNION改写


select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where  a.category  = b.from_cat and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate
UNION
select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where  a.category2 = b.from_cat and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate;

[PHP]     
--------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     2 |   148 |    36  (59)|
|   1 |  SORT UNIQUE         |              |     2 |   148 |    36  (59)|
|   2 |   UNION-ALL          |              |       |       |            |
|*  3 |    HASH JOIN         |              |     1 |    74 |    17  (12)|
|   4 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  5 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   192 |     3  (34)|
|*  6 |    HASH JOIN         |              |     1 |    74 |    17  (12)|
|   7 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  8 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   192 |     3  (34)|
--------------------------------------------------------------------------

[/PHP]   
初看这个PLAN好像不如第一个PLAN,因为执行了两次BIG_TABLE的FTS,但是让我们在来看看HASH TABLE的结构

### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:       9306
Number of buckets with   1 rows:       5310
Number of buckets with   2 rows:       1436
Number of buckets with   3 rows:        285
Number of buckets with   4 rows:         43
Number of buckets with   5 rows:          4
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 16384 Empty buckets: 9306 Non-empty buckets: 7078
Total number of rows: 9232
Maximum number of rows in a bucket: 5
Average number of rows in non-empty buckets: 1.304323

这就是我们所需要的Hash Table,最长的链表只有五行数据

整个SQL的执行时间从三四个小时缩短为16分钟,大大超出了developer的预期

这个SQL单纯从PLAN上很难看出问题所在,需要了解Hash Join的机制,进行更深一步的分析

[ 本帖最后由 eagle_fan 于 2008-3-17 13:09 编辑 ]
论坛徽章:
38
2010新春纪念徽章
日期:2010-01-04 08:33:082012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25版主2段
日期:2012-05-15 15:24:11优秀写手
日期:2013-12-18 09:29:08马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
发表于 2008-3-17 12:10 | 显示全部楼层


楼主辛苦了,先支持,再看!

使用道具 举报

回复
论坛徽章:
0
发表于 2008-3-17 12:12 | 显示全部楼层
谢谢分享!

使用道具 举报

回复
论坛徽章:
125
ITPUB社区12周年站庆徽章
日期:2013-08-13 16:52:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15NBA季后赛纪念徽章
日期:2011-06-13 11:34:51欧洲冠军杯纪念徽章
日期:2011-05-30 17:39:52NBA常规赛纪念章
日期:2011-04-15 13:34:112011新春纪念徽章
日期:2011-02-18 11:43:342011新春纪念徽章
日期:2011-01-16 22:27:502011新春纪念徽章
日期:2011-01-04 10:24:022010广州亚运会纪念徽章:橄榄球
日期:2010-12-22 23:50:552010广州亚运会纪念徽章:乒乓球
日期:2010-11-03 12:50:58
发表于 2008-3-17 12:19 | 显示全部楼层
谢谢LZ分享!

使用道具 举报

回复
论坛徽章:
26
数据库板块每日发贴之星
日期:2006-09-04 01:02:512009日食纪念
日期:2009-07-22 09:30:00生肖徽章2007版:虎
日期:2009-08-12 13:08:002010新春纪念徽章
日期:2010-01-04 08:33:082011新春纪念徽章
日期:2011-02-18 11:43:35ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28凯迪拉克
日期:2013-11-20 21:13:48美羊羊
日期:2015-03-04 14:48:582015年新春福章
日期:2015-03-06 11:57:31双子座
日期:2015-09-25 14:44:15
发表于 2008-3-17 13:08 | 显示全部楼层
select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category  = b.from_cat or
       a.category2 = b.from_cat) and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate;


两点建议:

1.查看sale_end列,看是否有必要建索引。
2.改写成
select a.ID
from BIG_TABLE a
where a.sale_end >= sysdate
and exists(select null
             from  SMALL_TABLE b
            where a.site_id  = b.site_id
              and b.from_cat in(a.category,a.category2)
           )

使用道具 举报

回复
论坛徽章:
27
授权会员
日期:2005-10-30 17:05:33管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:36优秀写手
日期:2013-12-18 09:29:13马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
 楼主| 发表于 2008-3-17 13:12 | 显示全部楼层
编辑了一下plan的格式为PHPcode,贴段代码真不容易啊

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2008-3-17 13:17 | 显示全部楼层
我应该会想到把or改成union,但具体为什么速度能够加快就不能分析的这么透彻了

使用道具 举报

回复
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期: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:142013年新春福章
日期:2013-02-25 14:51:24
发表于 2008-3-17 13:24 | 显示全部楼层
这样如何?

select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category||' '||a.site_id  = b.from_cat||' '||b.site_id
   or a.category2||' '||a.site_id  = b.from_cat||' '||b.site_id
) and a.sale_end >= sysdate;

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
66
ITPUB元老
日期:2005-07-16 18:49:11授权会员
日期:2005-10-30 17:05:33ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44现任管理团队成员
日期:2011-05-07 01:45:08版主3段
日期:2012-05-15 15:24:11
发表于 2008-3-17 13:27 | 显示全部楼层
不错,解析的很透彻...

使用道具 举报

回复
论坛徽章:
97
ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04
发表于 2008-3-17 13:48 | 显示全部楼层
多谢楼主分享...
认真看看...

使用道具 举报

回复

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

本版积分规则 发表回复

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