Unnesting Nested Loops

Lately I have been trying to help my non-DBA colleague to understand optimizer operations. Now that I am putting this effort , why not add it to my blog. I will be writing a series of blogs discussing optimizer operations.  Let me start the honors with NESTED LOOPS.

NESTED LOOPS:  As the name implies, this operation works similar to nested loops in programming language aka a loop within a loop.   For example, let’s use PL/SQL language that most of us are familiar

SQL> BEGIN

2       FOR  OuterLoop IN 1..2 LOOP

3            FOR InnerLoop  IN 1..3 LOOP

4                 DBMS_OUTPUT.PUT_LINE(‘OuterLoop=> ‘|| OuterLoop  || ‘   ‘||  ‘ InnerLoop=> ‘ || InnerLoop);

5            END LOOP;

6       END LOOP;

7  END;

8  /

OuterLoop=> 1    InnerLoop=> 1

OuterLoop=> 1    InnerLoop=> 2

OuterLoop=> 1    InnerLoop=> 3

OuterLoop=> 2    InnerLoop=> 1

OuterLoop=> 2    InnerLoop=> 2

OuterLoop=> 2    InnerLoop=> 3

 PL/SQL procedure successfully completed.

In this example, the outer loop executes 2 times and for each execution of the outer loop, the inner loop executes 3 times. Switching gears to nested loop between let’s say tables A and B; Assume table A has 50 rows and table B has 1000 rows.  If table A is used as the outer table, then for every row accessed in table a, inner table B will be accessed 1000 times.

So who makes the choice of deciding the outer and inner table? Well the Oracle optimizer does. Generally the rule of thumb is the table with less number of rows is used in the outer loop and table will more number of rows is used in the inner table.   This is where table statistics plays an important role, actually up-to-date statistics.  Number of IO operations and blocks retrieved also influences the decision in choosing the appropriate outer and inner tables. None of what is mentioned in this article is always true  as most of us don’t have access to actual Oracle logic .

A nested loops join is  very effective if the outer input is small and the inner input is indexed and large.  Generally nested loops perform better in OLTP environment where the result set tends to be small.

Changes in 11g

Execution plans with 11G might look different because of  changes in nested loop implementation. The change was introduced  to reduce overall latency for physical I/O  when the required data for nested loop join is not in the buffer cache.  Oracle reduces the overall physical I/O request latency by batching multiple physical I/O requests  improving the  performance (most likely). You can see the new batching operation with a  10053 trace or  DBMS_XPLAN.DISPLAY_CURSOR.

You will notice  two NESTED LOOPS  operations  in the execution plan instead of one because of this implementation change.   Two nested loop are required for

  1.   1st nested loop to  join data from outer loop table  and index on the inner loop table.
  2.   2nd nested loop to join the result set of  1st nested loop and table  data on the inner side of the join.

The above implementation change can be controlled with init.ora parameter optimizer_features_enable or  with a new hint “no_nlj_batching”

For example, Let us run the a query with optimizer_features_enable set to Oracle version 11 and then again rerun the same query with Oracle version 10.

Case-1 : optimizer_features_enable=11.2.0.2

In this case you will see 2  nested loops in the execution plan and new operation NLJ_BATCHING in the outline_data.

SQL> SELECT *   FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  apzagcxrnwka8, child number 0
-------------------------------------
SELECT   A.OBJECT_NAME ,  A. LAST_DDL_TIME , B.CREATED FROM A, B WHERE
b.OBJECT_ID=a.OBJECT_ID  AND  a.OBJECT_ID BETWEEN 1 AND 30

Plan hash value: 1521750179

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |     5 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |     3 |   135 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A      |    16 |   512 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_A  |    16 |       |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | INDX_B |     1 |       |     0   (0)|          |
|   6 |   TABLE ACCESS BY INDEX ROWID | B      |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / A@SEL$1
   4 - SEL$1 / A@SEL$1
   5 - SEL$1 / B@SEL$1
   6 - SEL$1 / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('query_rewrite_enabled' 'false')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID"))
      INDEX(@"SEL$1" "B"@"SEL$1" ("B"."OBJECT_ID"))
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_NL(@"SEL$1" "B"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "B"@"SEL$1")      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."OBJECT_ID">=1 AND "A"."OBJECT_ID"<=30)
   5 - access("B"."OBJECT_ID"="A"."OBJECT_ID")
       filter(("B"."OBJECT_ID"<=30 AND "B"."OBJECT_ID">=1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."LAST_DDL_TIME"[DATE,7],
       "B"."CREATED"[DATE,7]
   2 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."LAST_DDL_TIME"[DATE,7],
       "B".ROWID[ROWID,10]
   3 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."OBJECT_ID"[NUMBER,22],
       "A"."LAST_DDL_TIME"[DATE,7]
   4 - "A".ROWID[ROWID,10], "A"."OBJECT_ID"[NUMBER,22]
   5 - "B".ROWID[ROWID,10]
   6 - "B"."CREATED"[DATE,7]

Case-2 : optimizer_features_enable=10.2.0.3

In this case you will see 1  nested loops in the execution plan and new operation NLJ_BATCHING in missing in outline_data.

SQL> SELECT *   FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  apzagcxrnwka8, child number 1
-------------------------------------
SELECT   A.OBJECT_NAME ,  A. LAST_DDL_TIME , B.CREATED FROM A, B WHERE
b.OBJECT_ID=a.OBJECT_ID  AND  a.OBJECT_ID BETWEEN 1 AND 30

Plan hash value: 1900124691

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |       |       |     5 (100)|          |
|   1 |  NESTED LOOPS                |        |     3 |   135 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A      |    16 |   512 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_A  |    16 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| B      |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | INDX_B |     1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / A@SEL$1
   3 - SEL$1 / A@SEL$1
   4 - SEL$1 / B@SEL$1
   5 - SEL$1 / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('query_rewrite_enabled' 'false')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID"))
      INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("B"."OBJECT_ID"))
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_NL(@"SEL$1" "B"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OBJECT_ID">=1 AND "A"."OBJECT_ID"<=30)
   5 - access("B"."OBJECT_ID"="A"."OBJECT_ID")
       filter(("B"."OBJECT_ID"<=30 AND "B"."OBJECT_ID">=1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."LAST_DDL_TIME"[DATE,7],
       "B"."CREATED"[DATE,7]
   2 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."OBJECT_ID"[NUMBER,22],
       "A"."LAST_DDL_TIME"[DATE,7]
   3 - "A".ROWID[ROWID,10], "A"."OBJECT_ID"[NUMBER,22]
   4 - "B"."CREATED"[DATE,7]
   5 - "B".ROWID[ROWID,10]

63 rows selected.
 
Advertisements

Tagged: , , , , , , , , , , ,

%d bloggers like this: