nested loop执行计划分析

yanxiao_12

carcase 发表于 2017-7-18 16:41
你第二个图哪里来的？ 是真实的执行计划吗？

第二个图是基于oracle11g做的实验，截图的

latch_free

yanxiao_12 发表于 2017-7-18 18:34
内层的EMP查询为什么连接着DEPT的主键PK_DEPT？其结果集作为外层的驱动表，为什么不是直接EMP查询完毕直 ...

当然效率更高，不用每次执行nest loop的时候执行dept的回表，而是最后进行一次回表

ZALBB

楼主，请把两 sql 语句贴出来，

yanxiao_12

ZALBB 发表于 2017-7-18 20:43
楼主，请把两 sql 语句贴出来，

两个SQL语句一样，都是如下：select /*+ RULE */ * from emp, dept where emp.deptno= dept.deptno and emp.comm is null and dept.dname!= 'SALES';
不过，第一个图示教材给出的执行计划，第二个图是在我自己的数据库上操作输出的，看见了不同，感到好奇，所以求教。

cuicuigo

哪个的逻辑读物理读低 哪个就快

carcase

这个是oracle的11g新特性造成的
新内部优化算法Batching NLJ对执行计划的影响
如果你关掉这个新特性，执行计划就会回到原来的执行计划。
在session里临时关闭的方法是：
Alter session set "_nlj_batching_misses_enabled" = 0 ;
Alter session set "_nlj_batching_enabled" = 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3235125262

-----------------------------------------------
| Id  | Operation                   | Name    |
-----------------------------------------------
|   0 | SELECT STATEMENT            |         |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |
|   2 |   NESTED LOOPS              |         |
|*  3 |    TABLE ACCESS FULL        | EMP     |
|*  4 |    INDEX UNIQUE SCAN        | PK_DEPT |
-----------------------------------------------

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

   1 - filter("DEPT"."DNAME"<>'SALES')
   3 - filter("EMP"."COMM" IS NULL)
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

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

SQL>

实际上是nested loop和filter之间的性能差异，11g oracle认为自己的批量nestloop比较有优势，所以选择了nestloop

latch_free

11.3.3.1.2 New Implementation for Nested Loop Joins

latch_free

latch_free 发表于 2017-7-18 16:42
第2张图的外层的nest loop是一次统一的回表操作，oracle执行计划只是这样标注而已

11.3.3.1.2 New Implementation for Nested Loop Joins



Oracle Database 11g introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. Oracle Database 11g can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time.

As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.

Consider the query in "Original Implementation for Nested Loop Joins". In Oracle Database 11g, with the new implementation for nested loop joins, the execution plan for this query might appear similar to the following execution plan:
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost(%CPU)| Time      |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    19 |   722 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |    19 |   722 |     3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     2 |    32 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

yanxiao_12

latch_free 发表于 2017-7-19 09:48
11.3.3.1.2 New Implementation for Nested Loop Joins

谢谢指点，感觉我有点愚钝，针对oracle11g的这个新特性，现在还有些疑问：
1、如你所说，oracle11g为了减少IO操作，在批量操作时“Oracle Database 11g can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time”，能否请教下vector I/O和at a time的区别是什么？
2、在红色标注部分，这个内层嵌套循环返回的结果集是什么？是基于DEPARTMENTS表查询基础上的EMP_DEPARTMENT_IX索引集？还是?
谢谢了！

yanxiao_12

latch_free 发表于 2017-7-19 09:46
11.3.3.1.2 New Implementation for Nested Loop Joins

针对oracle11g的NLJ新特性，我不是太明白，有些困惑，能否给我一个比较简单的解释？谢了