Vanishing table in 11.2.0.3
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)| |(0)| 00:00:01 |
| 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
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.comReceived on Wed Feb 01 2012 - 22:08:20 CST