Vanishing table in 11.2.0.3

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Thu, 2 Feb 2012 04:08:20 +0000 (UTC)
Message-ID: <pan.2012.02.02.04.08.17_at_gmail.com>



Query is extremely simple and the result is expected:

select ename,job,mgr,deptno,sal from emp where deptno in (select deptno from dept) /
ENAME JOB MGR DEPTNO SAL
---------- --------- ---------- ---------- ----------

SMITH	   CLERK	   7902 	20	  800
ALLEN	   SALESMAN	   7698 	30	 1600
WARD	   SALESMAN	   7698 	30	 1250
JONES	   MANAGER	   7839 	20	 2975
MARTIN	   SALESMAN	   7698 	30	 1250
BLAKE	   MANAGER	   7839 	30	 2850
CLARK	   MANAGER	   7839 	10	 2450
SCOTT	   ANALYST	   7566 	20	 3000
KING	   PRESIDENT			10	 5000
TURNER	   SALESMAN	   7698 	30	 1500
ADAMS	   CLERK	   7788 	20	 1100
JAMES	   CLERK	   7698 	30	  950
FORD	   ANALYST	   7566 	20	 3000
MILLER	   CLERK	   7782 	10	 1300

14 rows selected.

Elapsed: 00:00:00.01

That is nothing unusual. What is unusual is the execution plan: SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT



SQL_ID fbqjucf3xdmjb, child number 0

select ename,job,mgr,deptno,sal from emp where deptno in (select deptno from dept)

Plan hash value: 3956160932



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


| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)| 00:00:01 |

Predicate Information (identified by operation id):


   1 - filter("DEPTNO" IS NOT NULL)

19 rows selected.

Elapsed: 00:00:00.04
SQL> Question: where the _at_#$%! is the table DEPT? The original query contains 2 tables, the execution plan is only showing me only one. I can get both tables if I modify the query like this:

select /*+ gather_plan_statistics no_unnest(_at_sub1) */ ename,job,mgr,deptno,sal from emp
where deptno in (select /*+ qb_name(sub1) */

             deptno from dept)
/
....
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));

PLAN_TABLE_OUTPUT



SQL_ID d6vrypfrfb3w9, child number 0

select /*+ gather_plan_statistics no_unnest(_at_sub1) */ ename,job,mgr,deptno,sal from emp where deptno in (select /*+ qb_name(sub1) */ deptno from dept)

Plan hash value: 1783302997



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

| 0 | SELECT STATEMENT | | | | 4
(100)|	     |
|*  1 |  FILTER 	   |	     |	     |	     |		  
|	     |

| 2 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)|
00:00:01 | |* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| | ------------------------------------------------------------------------------

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


   1 - SEL$1
   2 - SEL$1 / EMP_at_SEL$1
   3 - SUB1  / DEPT_at_SUB1

Outline Data


  /*+

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(_at_"SUB1")
      OUTLINE_LEAF(_at_"SEL$1")
      OUTLINE(_at_"SUB1")
      FULL(_at_"SEL$1" "EMP"@"SEL$1")
      INDEX(_at_"SUB1" "DEPT"@"SUB1" ("DEPT"."DEPTNO"))
      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):


   1 - filter( IS NOT NULL)
   3 - access("DEPTNO"=:B1)

Column Projection Information (identified by operation id):


   1 - "ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "MGR"[NUMBER,22],
       "SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]
   2 - "ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "MGR"[NUMBER,22],
       "SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]


56 rows selected.

It seems that Oracle 11.2.0.3 is doing push_subq and unnest without being told to. EMP and DEPT are highly unlikely to cause a performance problem, but a more complex sub-query might. Even more important, it would be possible to miss it entirely. There is also another anomaly. I tried, just for fun. to get the classic nested loop plan from that statement. I got the strangest thing. Here is the query:

select
/*+ gather_plan_statistics

   no_unnest(_at_sub1)
   push_subq(_at_sub1)
   index(_at_SUB1 DEPT_at_SUB1(DEPTNO)
*/
ename,job,mgr,deptno,sal from emp e
where deptno in (select /*+ qb_name(sub1) */

             deptno from dept)
/

Here is the plan:
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));

PLAN_TABLE_OUTPUT



SQL_ID g3z43q5patbjg, child number 1

select /*+ gather_plan_statistics no_unnest(_at_sub1) push_subq(_at_sub1) index(@SUB1 DEPT_at_SUB1(DEPTNO) */ ename,job,mgr,deptno,sal from emp e where deptno in (select /*+ qb_name(sub1) */ deptno from dept)

Plan hash value: 1130626194



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

| 0 | SELECT STATEMENT | | | | 4
(100)|	     |
|*  1 |  TABLE ACCESS FULL | EMP     |	  14 |	 350 |	   4   (0)| 
00:00:01 |
|*  2 |   INDEX UNIQUE SCAN| PK_DEPT |	   1 |	   3 |	   0   
(0)|	     |
------------------------------------------------------------------------------

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


   1 - SEL$1 / E_at_SEL$1
   2 - SUB1 / DEPT_at_SUB1

Outline Data


  /*+

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(_at_"SUB1")
      OUTLINE_LEAF(_at_"SEL$1")
      OUTLINE(_at_"SUB1")
      FULL(_at_"SEL$1" "E"@"SEL$1")
      PUSH_SUBQ(_at_"SUB1")
      INDEX(_at_"SUB1" "DEPT"@"SUB1" ("DEPT"."DEPTNO"))
      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):


   1 - filter( IS NOT NULL)
   2 - access("DEPTNO"=:B1)

Column Projection Information (identified by operation id):


   1 - "ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "MGR"[NUMBER,22],        "SAL"[NUMBER,22], "DEPTNO"[NUMBER,22] Note


  • cardinality feedback used for this statement

58 rows selected.

Elapsed: 00:00:00.04
SQL> Question: it does look like a nested loop, but it doesn't say so anywhere? Which operation is that? Furthermore, the classic nested loop is now mighty strange:

SQL> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

Elapsed: 00:00:00.02
SQL> select /*+ leading(e) full(e) use_nl(d) */ e.ename,e.job,e.mgr,d.deptno,e.sal
  2 from emp e,dept d
  3 where e.deptno=d.deptno;

ENAME JOB MGR DEPTNO SAL
---------- --------- ---------- ---------- ----------

SMITH	   CLERK	   7902 	20	  800
ALLEN	   SALESMAN	   7698 	30	 1600
WARD	   SALESMAN	   7698 	30	 1250
JONES	   MANAGER	   7839 	20	 2975
MARTIN	   SALESMAN	   7698 	30	 1250
BLAKE	   MANAGER	   7839 	30	 2850
CLARK	   MANAGER	   7839 	10	 2450
SCOTT	   ANALYST	   7566 	20	 3000
KING	   PRESIDENT			10	 5000
TURNER	   SALESMAN	   7698 	30	 1500
ADAMS	   CLERK	   7788 	20	 1100
JAMES	   CLERK	   7698 	30	  950
FORD	   ANALYST	   7566 	20	 3000
MILLER	   CLERK	   7782 	10	 1300

14 rows selected.

Elapsed: 00:00:00.00
SQL> save /tmp/3
Created file /tmp/3.sql
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));

PLAN_TABLE_OUTPUT



SQL_ID d2aaqmcwy7q1k, child number 0

select /*+ leading(e) full(e) use_nl(d) */ e.ename,e.job,e.mgr,d.deptno,e.sal from emp e,dept d where e.deptno=d.deptno

Plan hash value: 3074306753



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

| 0 | SELECT STATEMENT | | | | 4
(100)|	     |

| 1 | NESTED LOOPS | | 14 | 392 | 4 (0)|
00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)|
00:00:01 | |* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| | ------------------------------------------------------------------------------

Look what happens when I turn the optimizer features back to the original value:
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));

PLAN_TABLE_OUTPUT



SQL_ID d2aaqmcwy7q1k, child number 1

select /*+ leading(e) full(e) use_nl(d) */ e.ename,e.job,e.mgr,d.deptno,e.sal from emp e,dept d where e.deptno=d.deptno

Plan hash value: 3956160932



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


| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)| 00:00:01 |

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


   1 - SEL$F7859CDE / E_at_SEL$1

Outline Data


  /*+

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$F7859CDE")
      ELIMINATE_JOIN(_at_"SEL$1" "D"@"SEL$1")
      OUTLINE(_at_"SEL$1")
      FULL(_at_"SEL$F7859CDE" "E"@"SEL$1")
      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):


   1 - filter("E"."DEPTNO" IS NOT NULL)

Column Projection Information (identified by operation id):


   1 - "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],        "E"."MGR"[NUMBER,22], "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22] 48 rows selected.

Join is eliminated, despite the fact that I have not only explicitly written the query as a join, I've also hinted it to do the nested loops. The only way I was able to force the nested loop join was to use an undocumented hint "no_eliminate_join". I had no problems with forcing the nested loop join if I added "LOC" column to the select list. LOC is not indexed columns, so the join could not be eliminated. Interestingly enough, without any hints, that resulted in merge join:

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));

PLAN_TABLE_OUTPUT



SQL_ID a30hhd2x2b3vw, child number 0

select /*+ gather_plan_statistics */
e.ename,e.job,e.mgr,d.deptno,d.loc,e.sal from emp e,dept d where e.deptno=d.deptno

Plan hash value: 844388907



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

| 0 | SELECT STATEMENT | | | | 7
(100)|                                                  |

| 1 | MERGE JOIN | | 14 | 504 | 7
(15)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2
(0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1
(0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 350 | 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 350 | 4
(0)| 00:00:01 |

Using first_rows(1) hint resulted in the new NL join, what Jonathan called "double NL join" on his blog. I am completely confused now.

-- 
http://mgogala.byethost5.com
Received on Wed Feb 01 2012 - 22:08:20 CST

Original text of this message