11g和10g环境下执行计划的奇怪的差别
本帖最后由 timothyzhn 于 2014-5-22 21:09 编辑先抛问题：
为什么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.
为了能够看到准确格式的执行计划，附上以上内容的文本文件，打开后能看到准确的执行计划缩进格式。
盼大侠指点，多谢！ 没有时间亲自测试，但就实验过程看问题不大。
简单来说 所谓RBO从10g后从不被改进，看来也仅仅是一句空话，因为内部递归SQL很多还是依赖于RBO的。我相信如果去看RBO在11g、12c的代码的话 还是会有少量更新的。 Maclean Liu(刘相兵 发表于 2014-5-22 21:35 static/image/common/back.gif
没有时间亲自测试，但就实验过程看问题不大。
简单来说 所谓RBO从10g后从不被改进，看来也仅仅是一句空话 ...
刘大，您说的就实验过程看问题不大的意思是：我列出的11g的执行计划也是没问题的吗？
那结合您视频里所讲的读执行计划的顺序，4、5的父节点都是3，TABLE ACCESS FULL和INDEX UNIQUE SCAN两个可以做NESTED LOOPS？
望刘大能给详细点的提示。谢谢！ 为什么不可以做NESTED LOOP? 这是11.1引入的New Implementation for Nested Loop Joins clevernby 发表于 2014-5-23 15:41 static/image/common/back.gif
这是11.1引入的New Implementation for Nested Loop Joins
哦。。。还真不知道这个，去学习一下，多谢大侠指点！ 下一步学习有方向了，多谢刘大和clevernby的指点！问题可以关闭了。
页:
[1]