Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







no_nlj_batching hint tips

Oracle Database Tips by Donald BurlesonMay 17, 2016

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 )
     for y in ( select * from t2
                where t2.joins = t1.joins )
          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!

Oracle training

  Oracle consulting and training


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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster