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.
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
- 1st nested loop to join data from outer loop table and index on the inner loop table.
- 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.
