SQL>
select /*+use_merge(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;已选择865行。
执行计划
----------------------------------------------------------
Plan hash value: 3475644097
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 354K| | 144 (2)| 00:00:02 |
| 1 | MERGE JOIN | | 990 | 354K| | 144 (2)| 00:00:02 |
| 2 | SORT JOIN | | 968 | 229K| 712K| 65 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABS | 968 | 229K| | 11 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 2267 | 274K| 824K| 79 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL| SEGS | 2267 | 274K| | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
统计信息
----------------------------------------------------------
2010 recursive calls
0 db block gets
378 consistent gets
0 physical reads
0 redo size
72346 bytes sent via SQL*Net to client
1003 bytes received via SQL*Net from client
59 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
865 rows processed
//使用嵌套循环;
SQL>select /*+use_nl(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;已选择865行。
执行计划
----------------------------------------------------------
Plan hash value: 840690564
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 354K| 11075 (1)| 00:02:13 |
| 1 | NESTED LOOPS | | 990 | 354K| 11075 (1)| 00:02:13 |
| 2 | TABLE ACCESS FULL| TABS | 968 | 229K| 11 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SEGS | 1 | 124 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
统计信息
----------------------------------------------------------
1930 recursive calls
0 db block gets
43978 consistent gets
0 physical reads
0 redo size
70556 bytes sent via SQL*Net to client
1003 bytes received via SQL*Net from client
59 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
865 rows processed
上面代码示例中给出了两个执行计划,给我们如下的信息。
首先,我们观察使用use_merge提示的SQL,在Hint的作用下,CBO生成的执行计划中使用Merge Sort Join连接方式。在执行计划中Oracle对两个数据表进行Sort操作,之后对排序过的结果进行Merge连接。其中Oracle对两个数据表进行的都是全表扫描操作。
另一个执行计划是使用use_nl控制的Nest Loop Join连接方式。中间同样也是没有使用索引等方式。其中,产生了大量逻辑读。见下表对比:
对比项目
Merge Sort Join
NestLoopJoin
逻辑读consistent gets
378
43978
排序空间sort
10
6
通过数据信息的对比,我们可以明显的看出两个相同结果集合的SQL,由于不同的连接方式而带来的差异。Merge Sort Join可以大大消除由于Nest Loop Join带来的随机读过多的情况。而由于进行的排序操作,Merge Sort Join也要付出相应的排序空间损耗。
2、Merge Sort Join与排序空间
对Oracle熟悉的朋友们通常对Sort和Group操作是比较敏感的。Sort和Group by都是需要单独对数据集进行的操作,要消耗额外的CPU和内存资源。CPU资源主要消耗在算法排序和结果集合整合上。而内存资源的消耗更加需要关注,排序操作要在专门的PGA排序区内完成。如果PGA中特定的排序大小(pga_aggregat_target:sort_area_size)不足以进行排序操作,也就是说需要排序分组的数据集合特别大的时候,Oracle需要调用Temp表空间的容量来进行操作。
这也就是问题的所在。Temp表空间数据存储位于磁盘中,速度与内存相差很多。所以,当进行排序操作的数据集合很大,会出现性能急剧的下降可能。在实际业务场景中,对海量数据集合的处理、Data Warehouse应用的操作,都可能出现这种情况。
回到Merge Sort Join来,就可以理解这种连接方式的缺陷之处了。要进行Merge Sort Join,其中的Sort过程不可避免。而使用Sort操作带来的优势就是不需要进行过多的随机读。在数据集合量很大的时候,Merge Sort Join的效率可能会很差。
3、对索引路径的借用
在Nest Loop Join中,对连接列进行索引处理,可以很大程度上提升执行计划效率,减少随机读的数量。道理就是借用了索引排序这个现实。而Merge Sort Join对索引的应用效果远不如Nest Loop Join。
索引环境构建:
//索引构建
SQL> create index idx_tabs_name on tabs(table_name);
Index created
SQL> create index idx_segs_name on segs(segment_name);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'SEGS',cascade => true);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user,'TABS',cascade => true);
PL/SQL procedure successfully completed
执行计划如下:
SQL> explain plan for select/*+use_merge(tabs,segs)*/* from segs,tabs where segs.segment_name=tabs.table_name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------
Plan hash value: 3475644097
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 354K| | 144 (2)| 00:00:
| 1 | MERGE JOIN | | 990 | 354K| | 144 (2)| 00:00:
| 2 | SORT JOIN | | 968 | 229K| 712K| 65 (2)| 00:00:
| 3 | TABLE ACCESS FULL| TABS | 968 | 229K| | 11 (0)| 00:00:
|* 4 | SORT JOIN | | 2267 | 274K| 824K| 79 (2)| 00:00:
| 5 | TABLE ACCESS FULL| SEGS | 2267 | 274K| | 13 (0)| 00:00:
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
18 rows selected
由于Merge Sort Join本身就带有排序的特性,而且返回的结果集合中包括所有字段。所以通常的执行计划中,即使连接列存在索引,也不会进入到执行计划中。除非进行一些特定列处理。
SQL> explain plan for select/*+use_merge(tabs,segs)*/segs.segment_name,tabs.table_namefrom segs,tabs where segs.segment_name=tabs.table_name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------
Plan hash value: 712326860
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 37620 | 9 (23)| 00
| 1 | MERGE JOIN | | 990 | 37620 | 9 (23)| 00
| 2 | SORT JOIN | | 968 | 17424 | 4 (25)| 00
| 3 | INDEX FAST FULL SCAN| IDX_TABS_NAME | 968 | 17424 | 3 (0)| 00
|* 4 | SORT JOIN | | 2267 | 45340 | 5 (20)| 00
| 5 | INDEX FAST FULL SCAN| IDX_SEGS_NAME | 2267 | 45340 | 4 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
18 rows selected
在对返回结果进行处理的情况下,索引路径会出现的。
4、结论
Merge Sort Join是一种古老经典的排序模型,类似于数据结构时代的合并排序算法。Merge Sort Join引入的最大优势是避免同Nest Loop Join类似的大量随机读现象,但是同时也引入了Sort空间变化的问题。
随着海量数据处理场景的增多,Merge Sort Join暴露出缺陷的机会越来越多。而Nest Loop Join的大量随机读问题,也是可以通过索引等常规手段加以优化。
分享到:
2011-12-26 16:24
浏览 3647
分类:数据库
评论