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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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 )
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!

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

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