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.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
