11g和10g环境下执行计划的奇怪的差别

timothyzhn

先抛问题：
为什么11g R2环境下的执行计划是这样的-请关注Id=4、5、6：
-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |      0 |00:00:00.01 |      85 |
|*  1 |  FILTER                       |          |      1 |      0 |00:00:00.01 |      85 |
|   2 |   NESTED LOOPS                |          |      1 |     14 |00:00:00.01 |      25 |
|   3 |    NESTED LOOPS               |          |      1 |     14 |00:00:00.01 |      11 |
|   4 |     TABLE ACCESS FULL         | EMP      |      1 |     14 |00:00:00.01 |       7 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     14 |     14 |00:00:00.01 |       4 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     14 |     14 |00:00:00.01 |      14 |
|*  7 |   TABLE ACCESS FULL           | SALGRADE |     12 |     12 |00:00:00.01 |      60 |
-------------------------------------------------------------------------------------------

而10g R2环境下的执行计划是这样的-请关注Id=3、4、5
-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|*  1 |  FILTER                       |          |      1 |      0 |00:00:00.01 |      95 |
|   2 |   NESTED LOOPS                |          |      1 |     14 |00:00:00.01 |      23 |
|   3 |    TABLE ACCESS FULL          | EMP      |      1 |     14 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     14 |     14 |00:00:00.01 |      16 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     14 |     14 |00:00:00.01 |       2 |
|*  6 |   TABLE ACCESS FULL           | SALGRADE |     12 |     12 |00:00:00.01 |      72 |
-------------------------------------------------------------------------------------------

来龙去脉是这样的：
11gR2环境及步骤是：
SYS @ PROD>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SYS @ PROD>show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.4

SYS @ PROD>set linesize 200 pagesize 1400
SYS @ PROD>select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname
  2  from scott.emp,scott.dept where dept.deptno = emp.deptno and not exists (select *
  3  from scott.salgrade where emp.sal between losal and hisal);

no rows selected

SYS @ PROD>select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  arf57sj1rrz1u, child number 1
-------------------------------------
select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname
from scott.emp,scott.dept where dept.deptno = emp.deptno and not exists
(select * from scott.salgrade where emp.sal between losal and hisal)

Plan hash value: 243245009

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |      0 |00:00:00.01 |      85 |
|*  1 |  FILTER                       |          |      1 |      0 |00:00:00.01 |      85 |
|   2 |   NESTED LOOPS                |          |      1 |     14 |00:00:00.01 |      25 |
|   3 |    NESTED LOOPS               |          |      1 |     14 |00:00:00.01 |      11 |
|   4 |     TABLE ACCESS FULL         | EMP      |      1 |     14 |00:00:00.01 |       7 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     14 |     14 |00:00:00.01 |       4 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     14 |     14 |00:00:00.01 |      14 |
|*  7 |   TABLE ACCESS FULL           | SALGRADE |     12 |     12 |00:00:00.01 |      60 |
-------------------------------------------------------------------------------------------

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

   1 - filter( IS NULL)
   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   7 - filter(("HISAL">=:B1 AND "LOSAL"<=:B2))

Note
-----
   - rule based optimizer used (consider using cbo)


32 rows selected.

--如果把11gR2的环境里的参数optimizer_features_enable改为10.2.0.1后，执行计划就会变得与下面的10g环境一样了

10gR2环境及步骤是：
SYS@EMREP>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SYS@EMREP>show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      10.2.0.1
SYS@EMREP>alter session set statistics_level=ALL;

Session altered.

SYS@EMREP>set linesize 200 pagesize 1400
SYS@EMREP>select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname
  2  from scott.emp,scott.dept where dept.deptno = emp.deptno and not exists
  3  (select * from scott.salgrade where emp.sal between losal and hisal);

no rows selected

SYS@EMREP>select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  824d2w5p1vzrp, child number 0
-------------------------------------
select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname from
scott.emp,scott.dept where dept.deptno = emp.deptno and not exists (select * from
scott.salgrade where emp.sal between losal and hisal)

Plan hash value: 1175760222

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|*  1 |  FILTER                       |          |      1 |      0 |00:00:00.01 |      95 |
|   2 |   NESTED LOOPS                |          |      1 |     14 |00:00:00.01 |      23 |
|   3 |    TABLE ACCESS FULL          | EMP      |      1 |     14 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     14 |     14 |00:00:00.01 |      16 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     14 |     14 |00:00:00.01 |       2 |
|*  6 |   TABLE ACCESS FULL           | SALGRADE |     12 |     12 |00:00:00.01 |      72 |
-------------------------------------------------------------------------------------------

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

   1 - filter( IS NULL)
   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   6 - filter(("HISAL">=:B1 AND "LOSAL"<=:B2))

Note
-----
   - rule based optimizer used (consider using cbo)


30 rows selected.


为了能够看到准确格式的执行计划，附上以上内容的文本文件，打开后能看到准确的执行计划缩进格式。
盼大侠指点，多谢！

Maclean Liu(刘相兵

没有时间亲自测试，但就实验过程看问题不大。

简单来说 所谓RBO从10g后从不被改进，看来也仅仅是一句空话，因为内部递归SQL很多还是依赖于RBO的。我相信如果去看RBO在11g、12c的代码的话 还是会有少量更新的。

timothyzhn

Maclean Liu(刘相兵 发表于 2014-5-22 21:35
没有时间亲自测试，但就实验过程看问题不大。

简单来说 所谓RBO从10g后从不被改进，看来也仅仅是一句空话 ...

刘大，您说的就实验过程看问题不大的意思是：我列出的11g的执行计划也是没问题的吗？
那结合您视频里所讲的读执行计划的顺序，4、5的父节点都是3，TABLE ACCESS FULL和INDEX UNIQUE SCAN两个可以做NESTED LOOPS？
望刘大能给详细点的提示。谢谢！

Maclean Liu(刘相兵

为什么不可以做NESTED LOOP?

clevernby

这是11.1引入的New Implementation for Nested Loop Joins

timothyzhn

clevernby 发表于 2014-5-23 15:41
这是11.1引入的New Implementation for Nested Loop Joins

哦。。。还真不知道这个，去学习一下，多谢大侠指点！

timothyzhn

下一步学习有方向了，多谢刘大和clevernby的指点！问题可以关闭了。