ITPub博客

首页 > 数据库 > Oracle > vector i/o and BATCHED TABLE ACCESS

vector i/o and BATCHED TABLE ACCESS

原创 Oracle 作者:warmbreeze 时间:2017-03-29 08:39:07 0 删除 编辑
从11g开始, Nested Loop Joins 的i/o操作有了优化, 叫做vector i/o
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.

执行计划出现了两次"NESTED LOOPS"
------------------------------------------------------------------------------------------------
| 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")

12C开始出现了BATCHED TABLE ACCESS,
The BATCHED access means that the database retrieves a few rowids from
the index, and then attempts to access rows in block order to improve the clustering
and reduce the number of times that the database must access a block.

执行计划:
--------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |2(100)| |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|EMPLOYEES |16|1104|2 (0)|00:00:01|
|*2| INDEX RANGE SCAN |EMP_EMP_ID_PK|16| |1 (0)|00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID">190)






















ss

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37279/viewspace-2136230/,如需转载,请注明出处,否则将追究法律责任。

上一篇: abou AMM
请登录后发表评论 登录
全部评论

注册时间:2012-02-15

  • 博文量
    45
  • 访问量
    42202