Re: Vanishing table in 11.2.0.3

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 2 Feb 2012 09:47:48 -0000
Message-ID: <gPidnau-8pYnxrfSnZ2dnUVZ8jednZ2d_at_bt.com>


So many questions in such a short space.

Table Elimination -

    The IN subquery is transformed into EXISTS     The EXISTS subquery is single table based on a declared primary key, therefore is subject "uncosted unnesting" (I think, that's the rule applied here)

    The resulting JOIN is from a declared foreign key to a declared primary key, and no non-key columns appear in the query so the join will always succeed and return a row from the EMP table if the deptno is not null, and will fail to return a row only if the deptno is null - so the join can be replaced by a simple "is not null" predicate.

Add the /*+ no_unnest */ hint and the subquery doesn't turn into a join therefore join elimination cannot occur. (The fact that no_unnest was obeyed makes me wonder whether the "uncosted unnesting" above applied in this case - I have a vague memory that the hint can't override an unconditional unnest, so I'll have to check that.) Notice that you have an explicit FILTER operation, and the subquery is behaving as an (EXISTS) filter subquery.

There is no "push_subq" in this plan, by the way. Push_subq (in principle) means that Oracle will run the subquery at the earliest possible moment rather than leaving it to the end of the plan. Since there is only one table in the driving query the earliest moment and the end of the plan are the same point in the plan.

In fact, the push_subq path does (at least sometimes) operate differently, by concealing the filter OPERATOR and leaving the subquery as a filter PREDICATE to an object access operator. This is exactly what you've got in your next example where you've explicitly supplied the hint. The filter OPERATOR from the previous plan has been removed, and the TABLE ACCESS FULL has slipped one place to the left - nevertheless the INDEX UNIQUE SCAN is indented at the position it would have been if the FILTER operator had been in place. You can still see the filter predicate in the predicates section in line 1 however, since you pulled the plan from memory, and since Oracle makes a mess of writing subquery predicates into memory, the subquery text has gone missing from the predicate

I think that answers all the significant questions - the remainder of your post shows some side effects relating to the behaviour I've desrcibed above. Randolf Geist has some nice information about the progression of the nested loop join on his blog:
http://oracle-randolf.blogspot.com/2011/07/logical-io-evolution-part-1-baseline.html http://oracle-randolf.blogspot.com/2011/07/logical-io-evolution-part-2-9i-10g.html

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em><a href="http://www.apress.com/9781430239543">Oracle Core</a></em></b>

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message 
news: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)
| /
|
| 14 rows selected.
|
| That is nothing unusual. What is unusual is the execution plan:
| SQL> select * from table(dbms_xplan.display_cursor);
|
| --------------------------------------------------------------------------
|| 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)
|
|
|
| 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)
| /
| ....
| ------------------------------------------------------------------------------
|| 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):
| -------------------------------------------------------------
|
| Predicate Information (identified by operation id):
| ---------------------------------------------------
| 1 - filter( IS NOT NULL)
| 3 - access("DEPTNO"=:B1)
|
| 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 Thu Feb 02 2012 - 03:47:48 CST

Original text of this message