Even the simplest things change – here’s a brief history of nested loop joins, starting from 8i, based on the following query (with some hints):
select
t2.n1, t1.n2
from
t2,t1
where
t2.n2 = 45
and t2.n1 = t1.n1
;
There’s an index to support the join from t2 to t1, and I’ve forced an (unsuitable) index scan for the predicate on t2.
Basic plan for 8i (8.1.7.4)
As reported by $ORACLE_HOME/rdbms/admin/utlxpls.sql.
Note the absence of a Predicate Information section.
Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 225 | 3K| 3038 | | | | NESTED LOOPS | | 225 | 3K| 3038 | | | | TABLE ACCESS BY INDEX RO|T2 | 15 | 120 | 3008 | | | | INDEX FULL SCAN |T2_I1 | 15 | | 8 | | | | TABLE ACCESS BY INDEX RO|T1 | 3K| 23K| 2 | | | | INDEX RANGE SCAN |T1_I1 | 3K| | 1 | | | --------------------------------------------------------------------------------
Basic plan for 9i (9.2.0.8)
As reported by a call to a home-grown version of dbms_xplan.display_cursor() with parameter statistics_level set to all.
Note the “prefetch” shape of the body of the plan but the inconsistency in the numbers reported for Rows, Bytes, and Cost which seem to be reporting the “traditional” 8i values simply transposed to match the new arrangement of the operations. There’s also a little oddity in the A-rows column in line 2 which looks as if it is the sum of its children plus 1 when the size of the rowsource is (presumably) the 225 rowids used to access the table.
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225 | 3600 | 3038 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 15 | 120 | 2 | 1 | 225 | 3061 |
| 2 | NESTED LOOPS | | 225 | 3600 | 3038 | 1 | 241 | 3051 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 15 | 120 | 3008 | 1 | 15 | 3017 |
| 4 | INDEX FULL SCAN | T2_I1 | 3000 | | 8 | 1 | 3000 | 17 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 | 15 | 225 | 34 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
5 - access("T2"."N1"="T1"."N1")
Basic plan for 10g (10.2.0.5)
As reported by a call to dbms_xplan.display_cursor() with parameter statistics_level set to all using format => ‘allstats last’
No change from 9i – even to the oddity in A-Rows.
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 15 | 225 |00:00:00.03 | 3061 |
| 2 | NESTED LOOPS | | 1 | 225 | 241 |00:00:00.03 | 3051 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 15 |00:00:00.03 | 3017 |
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 | 3000 |00:00:00.01 | 17 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 | 225 |00:00:00.01 | 34 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
5 - access("T2"."N1"="T1"."N1")
Basic plan for 11g (11.2.0.4)
As reported by a call to dbms_xplan.display_cursor() with parameter statisics_level set to all using format => ‘allstats last’
Note how the nested loop has now turned into two NESTED LOOP operations – potentially opening the way for a complete decoupling of index access and table access. This has an interesting effect on the number of starts of the table access by rowid for t1, of course. The number of buffer gets for this operation looks surprisingly low (given that it started 225 times) but can be explained by the pattern of the data distribution – and cross-checked by looking at the “buffer is pinned count” statistic which accounts for most of the table visits.
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 225 |00:00:00.01 | 3048 |
| 1 | NESTED LOOPS | | 1 | 225 | 225 |00:00:00.01 | 3048 |
| 2 | NESTED LOOPS | | 1 | 225 | 225 |00:00:00.01 | 3038 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 15 |00:00:00.01 | 3013 |
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 | 3000 |00:00:00.01 | 13 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 | 225 |00:00:00.01 | 25 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 225 | 15 | 225 |00:00:00.01 | 10 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
5 - access("T2"."N1"="T1"."N1")
There is, however, a second possible plan for 11g. The one above is the “NLJ Batching” plan, but I could have hinted the “NLJ prefetch” strategy, which takes us back to the 9i execution plan (with a very small variation in buffer visits, and the same old oddity in the A-Rows).
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 15 | 225 |00:00:00.01 | 3052 |
| 2 | NESTED LOOPS | | 1 | 225 | 241 |00:00:00.01 | 3042 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 15 |00:00:00.01 | 3017 |
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 | 3000 |00:00:00.01 | 17 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 | 225 |00:00:00.01 | 25 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
5 - access("T2"."N1"="T1"."N1")
Base plan for 12c (12.1.0.1)
As reported by a call to dbms_xplan.display_cursor() with statistics_level set to all.
Note that the table access to t2 in line 3 is described as “batched” (a feature that can be disabled by the /*+ no_batch_table_access_by_rowid(alias) */ hint) otherwise the plan matches the 11g plan.
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 225 |00:00:00.01 | 3052 |
| 1 | NESTED LOOPS | | 1 | | 225 |00:00:00.01 | 3052 |
| 2 | NESTED LOOPS | | 1 | 225 | 225 |00:00:00.01 | 3042 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 15 | 15 |00:00:00.01 | 3017 |
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 | 3000 |00:00:00.01 | 17 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 | 225 |00:00:00.01 | 25 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 225 | 15 | 225 |00:00:00.01 | 10 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
5 - access("T2"."N1"="T1"."N1")
Of course 12c also has the “prefetch” version of the plan available; and again “batched” access appears – for both tables in this case – and again the feature can be disabled individually by hints addressed at the tables:
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 15 | 225 |00:00:00.01 | 3052 |
| 2 | NESTED LOOPS | | 1 | 225 | 225 |00:00:00.01 | 3042 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 15 | 15 |00:00:00.01 | 3017 |
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 | 3000 |00:00:00.01 | 17 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 | 225 |00:00:00.01 | 25 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
5 - access("T2"."N1"="T1"."N1")
In these examples the difference in work done by the different variations and versions is negligible, but there may be cases where the pattern of data distribution may change the pattern of logical I/Os and buffer pins – which may affect the physical I/O. In this light it’s interesting to note the hint /*+ cluster_by_rowid(alias) */ that was introduced in 11.2.0.4 changing the 11g plan as follows:
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 15 | 225 |00:00:00.01 | 134 | | | |
| 2 | NESTED LOOPS | | 1 | 225 | 241 |00:00:00.01 | 124 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 15 |00:00:00.01 | 99 | | | |
| 4 | SORT CLUSTER BY ROWID | | 1 | 3000 | 3000 |00:00:00.01 | 8 | 142K| 142K| 126K (0)|
| 5 | INDEX FULL SCAN | T2_I1 | 1 | 3000 | 3000 |00:00:00.01 | 8 | | | |
|* 6 | INDEX RANGE SCAN | T1_I1 | 15 | 15 | 225 |00:00:00.01 | 25 | | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
6 - access("T2"."N1"="T1"."N1")
Note the effect appearing at line 4 – and the extraordinary effect this has on the buffer visits (so significant that I did a follow-up check on v$mystat to see if the figures were consistent). This type of rowid sorting is, of course, an important fix for an Exadata issue I described some time ago, and I had assumed that the “batched” concept in the 12c plan was in some way enabling it – although the 12c rowsource execution stats don’t seem to bear that idea out.
Footnote:
You may also want to read the following note by Timur Akhmadeev of Pythian on the 12c batched rowid.
Oracle Scratchpad 
Nice!
Comment by Nikolay Kovachev — April 23, 2014 @ 8:50 pm BST Apr 23,2014 |
Jonathan,
thank you for the historical overview.
In https://jonathanlewis.wordpress.com/2014/02/12/caution-hints/ you mentioned that _optimizer_cluster_by_rowid is true in 12.1 – so shouldn’t we see the optimized strategy (collect rowids, sort them, access t2 in a most efficient way) with much reduced buffer access there by default?
And another question: in his comment http://coskan.wordpress.com/2011/03/04/plan-stability-through-upgrade-to-11g-why-is-my-plan-changed-extra-nested-loop/#comment-4605 Randolf Geist mentioned that rowsource statistics seem to deactivate the Vector I/O feature in 11.1/11.2 – do you think this could have an impact on the results?
Regards
Martin
Comment by Martin Preiss — April 24, 2014 @ 12:50 pm BST Apr 24,2014 |
Martin,
Thanks for the comment – the link in Randolf’s comment is an important one.
In answer to your first point – although the parameter is set to true, this may simply enable a new costing option without forcing it to happen (and perhaps there is no hint to force it). I did note that the 10053 trace has the following note in it, which may be relevant although I haven’t tried working out a test for it yet:
CBRID = “cluster by rowid” ? Since the new operation is a sort, it’s possible that it will only be considered if the nested loop join is feeding some other blocking operation (such as a hash join or merge join) in a subsequent step, or being fed by a blocking operation. Not hard to test if the guess is correct – but terribly time-wasting if it’s wrong.
I did try the 12c test again without rowsource execution stats enabled, but the numbers for consistent gets didn’t change – i.e. we didn’t get an “invisible” cluster by rowid sort. It would be interesting to check whether the same disabling of feature occurs when SQL montoring (/*+ monitor */) is enabled – yet more things to test.
Comment by Jonathan Lewis — April 25, 2014 @ 6:04 pm BST Apr 25,2014 |
[…] could easily spend too much time on the topic, so I’ll refer you to an item I wrote recently on nested loops through the ages and pick up a few details in passing in future articles in this […]
Pingback by Execution Plans Part 4: Precision and Timing – All Things Oracle — May 14, 2014 @ 6:22 pm BST May 14,2014 |
Hi Jonathan, I love such “reference” blogs with some history. Really leaves a timeless piece of information on the Internet.
I want to contribute to it by sharing a recent discover. It appears that both NLJ prefetching and batching can be disabled if the select list of the outer table (driving table) has a potential size of more than ~8100 bytes.
Using your t1,t2 table creates from here: https://jonathanlewis.wordpress.com/2013/02/13/sts-ofe-and-spm/
select * from t2,t1 where t2.n2 = 45 and t2.n1 = t1.n1 ;Produces a NLJ_BATCHING plan. However if you add these:
Then neither a prefetch nor batching plan will show up. It reverts all the way back to 8i style plan :)
The query bellow still does not use NLJ batching, but if you remove only the “t2.v1” column from select list – it does switch to NLJ batching.
select t2.n1, t1.n2, t2.filler1, t2.filler2, t2.v1 from t2,t1 where t2.n2 = 45 and t2.n1 = t1.n1Comment by Christo Kutrovsky — September 18, 2015 @ 5:45 pm BST Sep 18,2015 |
Christo,
Thanks for that – an interesting little detail.
I wonder if there might be some connection with SDU size (or some other two-task configuration) or whether it’s a hard limit.
Comment by Jonathan Lewis — September 19, 2015 @ 9:41 am BST Sep 19,2015 |
[…] the link to the comment Christo made about the way that the mechanics of nested loop joins will change if the select list […]
Pingback by Just in case | Oracle Scratchpad — September 27, 2018 @ 7:45 am BST Sep 27,2018 |
[…] affect the arithmetic but it does mean the appearance of the nested loop goes back to the original pre-9i form that happens to make it a little easier to see costs and cardinalities adding and multiplying their […]
Pingback by opt_estimate 2 | Oracle Scratchpad — June 25, 2019 @ 8:22 pm BST Jun 25,2019 |
[…] something as simple as a serial nested loop join has a couple of surprises (largely thanks to the evolution of the mechanics – without a matching adjustment to the execution plans – over time). Here’s a […]
Pingback by E-rows / A-rows | Oracle Scratchpad — December 4, 2019 @ 1:17 pm GMT Dec 4,2019 |
[…] above a Statistics Collector before you get to the Hash Join, but that’s just because Oracle may implement a nested loop join in two steps, first to the index, second to the […]
Pingback by Adaptive Study | Oracle Scratchpad — October 11, 2021 @ 11:57 am BST Oct 11,2021 |
Dear Mr Lewis
Thanks for your brilliant blog posts.I have a question regarding this section ” Basic plan for 11g (11.2.0.4) ” , in which you say : potentially opening the way for a complete decoupling of index access and table access, I was wondering if you could explain a little more about about this part .
Thanks
Comment by pantea — January 22, 2022 @ 8:16 pm GMT Jan 22,2022 |
Pantea,
Thank you for the question.
It can be very difficult trying to find an answer for something like this as Oracle Corp. rarely tell us in any detail what they’re doing and why they’re doing it; and since I’ve never been called in to examine a case where this has seemed to be the source of a problem I’ve not tried to work out exactly what’s going on.
In this case, however, we might take the comments about “cluster by rowid” in the next section of the article as a clue, noticing how effective it seemed to be in reducing the buffer gets (which would have changed to “buffer is pinned count” visits). If in a “doubled” nested loop pattern like this Oracle fetches all the rowid data from the second table’s index before visiting the table at all it could (perhaps invisibly) put a “sort by rowid” between the index fetches and table fetches so that it only had to visit each table block at most once.
This is only speculation about Oracle Corp’s intention; and it may be that we never see a case where it happens because it might be of use only in very special cases; after all it would add the overhead of sorting to the run-time, and pass the data to its parent in a different order.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — January 24, 2022 @ 9:42 am GMT Jan 24,2022 |