## April 23, 2014

### NL History

Filed under: Execution plans,Oracle — Jonathan Lewis @ 6:43 pm BST Apr 23,2014

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)

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 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 seem to be reporting the “traditional” 8i values 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 statistics_level set to all.

No change from 9i.

```-------------------------------------------------------------------------------------------------
| 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 statisics_level set to all

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

```-------------------------------------------------------------------------------------------------
| 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 but disappeared by 12c [Ed: wrong, it wasn’t listed in v\$sql_hints in the beta, but is in the production version] 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.

1. Nice!

Comment by Nikolay Kovachev — April 23, 2014 @ 8:50 pm BST Apr 23,2014

2. 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 - frodef: T2 no blocking operation found.
CBRID - frodef: T1 no blocking operation found.
```

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

3. […] 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

4. 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:

```alter table t2 add filler1 varchar2(4000);
alter table t2 add filler2 varchar2(4000);
```

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.n1
```

Comment 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

5. […] 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

6. […] 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

7. […] 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.