ITPub博客

首页 > Linux操作系统 > Linux操作系统 > NLJ_BATCHING,NO_NLJ_BATCHING

NLJ_BATCHING,NO_NLJ_BATCHING

原创 Linux操作系统 作者:wei-xh 时间:2011-06-05 10:28:51 0 删除 编辑

引用:http://www.itpub.net/viewthread.php?tid=1254018&highlight=hint

2.NLJ_BATCHING,NO_NLJ_BATCHING
这是一种11G新的内部优化算法。目前在网上几乎没有任何参考资料讲如何实现的。
从《Troubleshooting Oracle Performance》书上的Chapter 10 Optimizing Joins我可以找到只言片语:
“As of Oracle Database 11g, the following execution plan might be observed instead of the
previous one. Note that even if the query is always the same (that is, a two-table join), the
execution plan contains two nested loop joins! A simple performance test showed an improvement
of about 10 percent using it. This is probably because of a new internal optimization
that applies only to the new execution plan. To control this new execution plan, the hints
nlj_batching and no_nlj_batching are available.”

让我举个例,一个很简单的两个表的neested loop join:
create table t1 as select * from dba_objects;
create index t1idx on t1(object_id);
create table t2 as select * from dba_objects;
create index t2idx on t2(object_id);

select /*+use_nl(t1 t2) index(t1) index(t2) ordered NO_NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 19511 |  4191K|   282   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |   140 | 28980 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       | 19511 |  4191K|   282   (1)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN         | T1IDX |   140 |  1820 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2IDX |    56 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."OBJECT_ID"=999)
   4 - access("T2"."OBJECT_ID"=999)



接着看看如果使用NLJ_BATCHING后SQL PLAN会变成什么样:
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       | 19511 |  4191K|   282   (1)| 00:00:04 |
|   1 |  NESTED LOOPS                |       |       |       |            |          |
|   2 |   NESTED LOOPS               |       | 19511 |  4191K|   282   (1)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | T1IDX |   140 |  1820 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2IDX |    56 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2    |   140 | 28980 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."OBJECT_ID"=999)
   4 - access("T2"."OBJECT_ID"=999)

可以看到,当我们使用NLJ_BATCHING后,一个两个表的neested loop join在执行计划里会显示两个neested loops。
经过我的若干大数据量的实验,并没有发现使用NLJ_BATCHING有特别大的如前面文档中所述的10%的improvement。
对于如下的小实验,使用NLJ_BATCHING反而有微小的performance degradation。
create table t1 as select * from dba_objects where rownum<=1000;
create table t2 as select * from dba_objects where rownum<=1000;
update t1 set object_id=999;
update t2 set object_id=999;
create index t1idx on t1(object_id);
create index t2idx on t2(object_id);
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NO_NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;

Elapsed: 00:00:56.73

Statistics
----------------------------------------------------------
        324  recursive calls
          0  db block gets
     149706  consistent gets
          6  physical reads
          0  redo size
   45993532  bytes sent via SQL*Net to client
     733849  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

select /*+use_nl(t1 t2) index(t1) index(t2) ordered NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;


Elapsed: 00:00:57.15
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     149674  consistent gets
          0  physical reads
          0  redo size
   45993532  bytes sent via SQL*Net to client
     733849  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

BTW,这里有个隐藏参数控制是否默认开启NLJ_BATCHING:
_nlj_batching_enabled : enable batching of the RHS IO in NLJ

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-697199/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2341338