NLJ(Nested Loop Join) Batching

We have recently upgraded our production databases from 10.2.0.4 to 11.2.0.4. Though the whole transition was smooth after a month we started hearing drop in performance. Most of the issue was reported from ETL loads and am planning to discuss few of the cases here.

While analysing the plan of one of a query we could see that there is a difference in plan between 10g & 11g. The difference was a additional nested loop.

Execution Plan
----------------------------------------------------------
Plan hash value: 2756736908

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |  4938 |    38M|  9681   (2)|00:01:57 |

|   1 |  NESTED LOOPS                |           |       |       |            |         |

|   2 |   NESTED LOOPS               |           |  4938 |    38M|  9681   (2)|00:01:57 |

|   3 |    TABLE ACCESS FULL         | T2        |   929K|  3585M|  4655   (1)|00:00:56 |

|*  4 |    INDEX UNIQUE SCAN         | T3_IDX_ID |     1 |       |     0   (0)|00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| T3        |     1 |  4043 |     1   (0)|00:00:01 |

-----------------------------------------------------------------------------------------
When the same query is executed with 10g optimizer the plan is as follows.

SQL> select /*+ optimizer_features_enable('10.2.0.5')*/* From t2, t3 where t2.id=t3.id;

4999 rows selected.

Elapsed: 00:00:01.29

Execution Plan
----------------------------------------------------------
Plan hash value: 4148881243

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |           |  4938 |    38M|  9681   (2)|00:01:57 |

|   1 |  NESTED LOOPS                |           |  4938 |    38M|  9681   (2)|00:01:57 |

|   2 |   TABLE ACCESS FULL          | T2        |   929K|  3585M|  4655   (1)|00:00:56 |

|   3 |   TABLE ACCESS BY INDEX ROWID| T3        |     1 |  4043 |     1   (0)|00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | T3_IDX_ID |     1 |       |     0   (0)|00:00:01 |

------------------------------------------------------------------------------------------

What is NLJ Batching?

From the documentation "When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. In Oracle Database 11g Release 1 (11.1), Oracle Database can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time. As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join"

Going by the definition in this case:

2   NESTED LOOPS              
 3 |    TABLE ACCESS FULL         | T2        |   929K|  3585M|  4655   (1)|
 4 |    INDEX UNIQUE SCAN

one NESTED LOOPS[2] row source to join the values from the table(Table T2)[3] on the outer side of the join with the index[4] on the inner side.

 1 |  NESTED LOOPS               
|   2 |   NESTED LOOPS           
|   5 |   TABLE ACCESS BY INDEX ROWID| T3        |     1 |  4043 |     1   (0)|

A second row source is allocated to join the result of the first join[2], which includes the rowids stored in the index, with the table[5] on the inner side of the join

To find out how effective the IO batching has been/If IO batching is really happening the session statistics can be checked.

SQL> select name,value from v$mystat natural join v$statname where name like '%Ba%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
Batched IO vector read count                                              0
Batched IO vector block count                                             0
Batched IO single block count                                             0
Batched IO zero block count                                               0
Batched IO block miss count                                               0
Batched IO double miss count                                              0
Batched IO (full) vector count                                            0
Batched IO (space) vector count                                           0
Batched IO (bound) vector count                                           0
Batched IO same unit count                                                0
Batched IO buffer defrag count                                            0
Batched IO slow jump count                                                0
The behaviour of the NL batching can be controlled by Hints(no_nlj_batching), parameters(_nlj_batching_enabled), it also gets disabled with optimizer version when set to pre 11.2.

more to come....