NLJ_BATCHING，NO_NLJ_BATCHING

引用：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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-697199/