此脚本不依赖统计信息。

    排序合并连接一般用于非等值关联，如果两表是等值关联，我们建议使用 HASH 连接代替排序合并连接，因为 HASH 连接只需要将驱动表放入 PGA 中，而排序合并连接要么是将两个表放入 PGA 中，要么是将一个表放入 PGA 中、另外一个表走 INDEX FULL SCAN，然后回表。如果两表是等值关联并且两表比较大，这时应该走 HASH 连接而不是排序合并连接。下面脚本抓出两表等值关联但是走了排序合并连接的 SQL，同时显示离 MERGE JOIN 关键字较远的表的段大小（太大 PGA 放不下）。

    1. select c.sql_id, c.sql_text, d.owner, d.segment_name, d.mb
    2.   from v$sql_plan a,
    3.        v$sql_plan b,
    4.        v$sql c,
    5.        (select owner, segment_name, sum(bytes / 1024 / 1024) mb
    6.           from dba_segments
    7.          group by owner, segment_name) d
    8.  where a.sql_id = b.sql_id
    9.    and a.child_number = b.child_number
    10.    and b.operation = 'SORT'
    11.    and b.options = 'JOIN'
    12.    and b.access_predicates like '%"="%'
    13.    and a.parent_id = b.id
    14.    and a.object_owner = 'SCOTT'
    15.    and b.sql_id = c.sql_id
    16.    and b.child_number = c.child_number
    17.    and a.object_owner = d.owner
    18.    and a.object_name = d.segment_name
    19.  order by 4 desc;

    我们在 Scott 账户中运行下面 SQL 并且查看执行计划。

    1.  select /*+ use_merge(e,d) */ *
    2.     from emp e, dept d
    3.    where e.deptno = d.deptno;
    1. 14 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 844388907
    7. -------------------------------------------------------------------------------------
    8. | Id | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|Time    |
    9. -------------------------------------------------------------------------------------
    10. |  0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)|00:00:01|
    11. |  1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)|00:00:01|
    12. |  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)|00:00:01|
    13. |  3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)|00:00:01|
    14. |* 4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)|00:00:01|
    15. |  5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)|00:00:01|
    16. -------------------------------------------------------------------------------------
    18. Predicate Information (identified by operation id):
    19. ---------------------------------------------------
    21.    4 - access("E"."DEPTNO"="D"."DEPTNO")
    22.        filter("E"."DEPTNO"="D"."DEPTNO")

    我们使用脚本将走了排序合并连接的 SQL 抓出，同时显示离 MERGE JOIN 关键字较远的表的段大小。

    1.  select c.sql_id, c.sql_text, d.owner, d.segment_name, d.mb
    2.     from v$sql_plan a,
    3.          v$sql_plan b,
    4.          v$sql c,
    5.          (select owner, segment_name, sum(bytes / 1024 / 1024) mb
    6.             from dba_segments
    7.            group by owner, segment_name) d
    8.    where a.sql_id = b.sql_id
    9.      and a.child_number = b.child_number
    10.      and b.operation = 'SORT'
    11.      and b.options = 'JOIN'
    12.      and b.access_predicates like '%"="%'
    13.      and a.parent_id = b.id
    14.      and a.object_owner = 'SCOTT'
    15.      and b.sql_id = c.sql_id
    16.      and b.child_number = c.child_number
    17.     and a.object_owner = d.owner
    18.     and a.object_name = d.segment_name
    19.    order by 4 desc;
    1. SQL_ID        SQL_TEXT                                OWNER   SEGMENT_NAME         MB
    2. ------------- ---------------------------------------- ------- ------------- --------
    3. c7gd7wn0gx4vq select /*+ use_merge(e,d) */ *  from emp e,  SCOTT    EMP         .0625
    4.               dept d  where e.deptno = d.deptno