Home > oracle > 对Hash Join的一次优化

对Hash Join的一次优化

March 17th, 2008
Goto comments Leave a comment

前两天解决了一个优化SQL的case，SQL语句如下，big_table为150G大小，small_table很小，9000多条记录，不到1M大小
hash_area_size, sort_area_size均设置足够大，可以进行optimal hash join和memory sort

  1. select /*+ leading(b) use_hash(a b) */ distinct a.ID
  2. from BIG_TABLE a, SMALL_TABLE b
  3. where (a.category  = b.from_cat or
  4.        a.category2 = b.from_cat) and
  5.        a.site_id  = b.site_id and
  6.        a.sale_end >= sysdate;
  7.  
  8. --------------------------------------------------------------------------
  9. | Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
  10. --------------------------------------------------------------------------
  11. |   0 | SELECT STATEMENT     |              |     2 |   174 |    18  (17)|
  12. |   1 |  SORT UNIQUE         |              |     2 |   174 |    18  (17)|
  13. |*  2 |   HASH JOIN          |              |     2 |   174 |    17  (12)|
  14. |   3 |    TABLE ACCESS FULL | SMALL_TABLE  |  1879 | 48854 |    14   (8)|
  15. |*  4 |    TABLE ACCESS FULL | BIG_TABLE    |     4 |   244 |     3  (34)|
  16. --------------------------------------------------------------------------
  17.  
  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------
  20.  
  21.    2 - access("A"."SITE_ID"="B"."SITE_ID")
  22.        filter("A"."CATEGORY"="B"."FROM_CAT" OR
  23.               "A"."CATEGORY2"="B"."FROM_CAT")
  24.    4 - filter("A"."SALE_END">=SYSDATE@!)

粗略来看，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的过程。

  1. SQL> alter session set events '10104 trace name context forever, level 2';
  2.  
  3. Session altered.
  4.  
  5. select /*+ leading(b) use_hash(a b) */ distinct a.ID
  6. from BIG_TABLE a, SMALL_TABLE b
  7. where (a.category  = b.from_cat or
  8.        a.category2 = b.from_cat) and
  9.        a.site_id  = b.site_id and
  10.        a.sale_end >= sysdate;

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

  1. ### Hash table ###
  2. # NOTE: The calculated number of rows in non-empty buckets may be smaller
  3. #       than the true number.
  4. Number of buckets with   0 rows:      16373
  5. Number of buckets with   1 rows:          0
  6. Number of buckets with   2 rows:          0
  7. Number of buckets with   3 rows:          1
  8. Number of buckets with   4 rows:          0
  9. Number of buckets with   5 rows:          0
  10. Number of buckets with   6 rows:          0
  11. Number of buckets with   7 rows:          1
  12. Number of buckets with   8 rows:          0
  13. Number of buckets with   9 rows:          0
  14. Number of buckets with between  10 and  19 rows:          1
  15. Number of buckets with between  20 and  29 rows:          1
  16. Number of buckets with between  30 and  39 rows:          3
  17. Number of buckets with between  40 and  49 rows:          0
  18. Number of buckets with between  50 and  59 rows:          0
  19. Number of buckets with between  60 and  69 rows:          0
  20. Number of buckets with between  70 and  79 rows:          0
  21. Number of buckets with between  80 and  89 rows:          0
  22. Number of buckets with between  90 and  99 rows:          0
  23. Number of buckets with 100 or more rows:          4
  24. ### Hash table overall statistics ###
  25. Total buckets: 16384 Empty buckets: 16373 Non-empty buckets: 11
  26. Total number of rows: 9232
  27. Maximum number of rows in a bucket: 2531
  28. 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. 1. Build hash table based on (from_cat,site_id):
  2.  
  3. SQL> select site_id,from_cat,count(*) from SMALL_TABLE group by site_id,from_cat having count(*)>100;
  4.  
  5. no rows selected
  6.  
  7. 2. Build hash table based on (from_cat):
  8.  
  9. SQL> select from_cat,count(*) from SMALL_TABLE group by from_cat having count(*)>100;
  10.  
  11. no rows selected
  12.  
  13. 3. Build hash table based on (site_id):
  14.  
  15. SQL> select site_id,count(*) from SMALL_TABLE group by site_id having count(*)>100;
  16.  
  17.    SITE_ID   COUNT(*)
  18. ---------- ----------
  19.          0       2531
  20.          2       2527
  21.        146       1490
  22.        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改写

  1. select /*+ leading(b) use_hash(a b) */ distinct a.ID
  2. from BIG_TABLE a, SMALL_TABLE b
  3. where  a.category  = b.from_cat and
  4.        a.site_id  = b.site_id and
  5.        a.sale_end >= sysdate
  6. UNION
  7. select /*+ leading(b) use_hash(a b) */ distinct a.ID
  8. from BIG_TABLE a, SMALL_TABLE b
  9. where  a.category2 = b.from_cat and
  10.        a.site_id  = b.site_id and
  11.        a.sale_end >= sysdate;
  12.       
  13. --------------------------------------------------------------------------
  14. | Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
  15. --------------------------------------------------------------------------
  16. |   0 | SELECT STATEMENT     |              |     2 |   148 |    36  (59)|
  17. |   1 |  SORT UNIQUE         |              |     2 |   148 |    36  (59)|
  18. |   2 |   UNION-ALL          |              |       |       |            |
  19. |*  3 |    HASH JOIN         |              |     1 |    74 |    17  (12)|
  20. |   4 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
  21. |*  5 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   192 |     3  (34)|
  22. |*  6 |    HASH JOIN         |              |     1 |    74 |    17  (12)|
  23. |   7 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
  24. |*  8 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   192 |     3  (34)|
  25. --------------------------------------------------------------------------
  26.  
  27. Predicate Information (identified by operation id):
  28. ---------------------------------------------------
  29.  
  30.    3 - access("A"."CATEGORY"="B"."FROM_CAT" AND
  31.               "A"."SITE_ID"="B"."SITE_ID")
  32.    5 - filter("A"."SALE_END">=SYSDATE@!)
  33.    6 - access("A"."CATEGORY2"="B"."FROM_CAT" AND
  34.               "A"."SITE_ID"="B"."SITE_ID")
  35.    8 - filter("A"."SALE_END">=SYSDATE@!)

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

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

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

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

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

Eagle Fan oracle

  1. adam
    March 18th, 2008 at 13:10 | #1
    Reply | Quote

    其实通过PLAN的access path就可以知道hash key，然后在去考虑key的选择性就好了。

    你的例子里的两个plan已经比较清楚了：
    access(“A”.”SITE_ID”=”B”.”SITE_ID”) 和
    access(“A”.”CATEGORY”=”B”.”FROM_CAT” AND
    “A”.”SITE_ID”=”B”.”SITE_ID”)

  2. microsoft_fly
    June 4th, 2008 at 13:32 | #2
    Reply | Quote

    有没有用rule提示,可能有意想不到的效果

  1. No trackbacks yet.