Question: What do I do to use the no_nlj_batching hint?  Can you show an example of using the no_nlj_batching hint to improve SQL performance?

Answer:  The no_nlj_batching hint is used to turn-off nested-loops batching operations in table joins. 

Background on nested loops joins

The nested loops table join is one of the original table join plans and it remains the most common.  In a nested loops join, we have two tables a driving table and a secondary table.  The rows are usually accessed from a driving table index range scan, and the driving table result set is then nested within a probe of the second table, normally using an index range scan method.  Here is pseudo code that mimics what a nested loop join is doing:

for x in ( select * from T1 )
loop
     for y in ( select * from t2
                where t2.joins = t1.joins )
     loop
          output records
     end loop
end loop

Two ways to do nested loops joins

Nested loops joins changed starting in Oracle 11g and we now have full control over the Nested Loop plan "shapes".  The nested loops change was introduced  to reduce overall latency for physical disk I/O when the required data for nested loop join is not in the data buffer cache.  Oracle describes the optimization as a performance enhancement:

 “to reduce overall latency for physical I/O” by “batch[ing] multiple physical I/O requests and process[ing] them using a vector I/O instead of processing them one at a time.”

We have the "classic" Nested Loop Join, and the new Table Prefetching and Nested Loop Join Batching.

This is controlled via the NO_]NLJ_BATCHING and the NO_]NLJ_PREFETCH hints.

 To turn-off the new nested loops batching, use the hidden parameter  "_nlj_batching_enabled" .

End users report that testing SQL by using hint no_nlj_batching will reduce the response time significantly.

Oracle reduces the overall physical I/O request latency by batching multiple physical I/O requests, thereby improving the  performance. You can see the new batching operation with a  10053 trace or by running the dbms_xplan.display_cursor procedure.

Here is an example of using the no_nlj_batching hint to resort to the older nested loops functionality:

select /*+ use_nl(a b) opt_param('_nlj_batching_enabled', 1) no_nlj_prefetch(b) */

Here we deliberately invoke the new nested loops functionality.

select /*+ opt_param('_nlj_batching_enabled', 0) no_nlj_prefetch(b) */ col1, col1 . . .

In sum, playing with both nested loops join methods may result in significantly fsster response time for SQL statements that perform nested loops joins.'

Oracle guru Randolph Geist has these conclusions on the new nested loops functionality:

"Oracle 11g extends the logical I/O optimizations that could already been seen in Oracle 10g when using the Table Prefetching Nested Loop shape - and it is available without any further optimizations like Table Prefetching or Nested Loop Join Batching. It is also not depending on the new "fastpath" consistent gets introduced with 11g.

The efficiency of the optimization largely depends on the order of the data, so predicting it is not that easy - a bit similar to predicting the efficiency of the Subquery / Filter caching feature that also depends on data patterns.

However this knowledge might offer additional options how to take advantage of this optimization.

Of course introducing additional sort operations might easily outweigh the benefits achieved, but there might be cases where a sort is not that costly and allows to improve scalability/concurrency in extreme cases."

---

Oracle Training from Don Burleson  The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2016 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.