逻辑读的两种类型

        在oracle中有两类命令需要读块，DML和DQL(select)。DML类命令将块读进Buffer是为了修改，此类命令的读，称为当前读。而select命令产生的读取操作，称为一致读，当前读和一致读，统称为逻辑读。

         一致读可以通过设置arraysize，实现批量读取，一次一致读可以读取多行。update与deleted时，当前读的数量不会小于所修改的行数。insert则不同，它所产生的当前读，是按所插入行占用块数计算的，一般会略多于实际插入的块数。如下例：

scott@ORCL> create table t as select * from dba_objects;

表已创建。

scott@ORCL> create table t1 as select * from t where 1=2;

表已创建。

scott@ORCL> insert into t1 select * from t where rownum<100;

已创建99行。


执行计划
----------------------------------------------------------
Plan hash value: 508354683

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |    99 | 20493 |   324   (1)| 00:00:04 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |       |       |            |          |
|*  2 |   COUNT STOPKEY          |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL     | T    | 88631 |    17M|   324   (1)| 00:00:04 |
---------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<100)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
        451  recursive calls
         70  db block gets
        360  consistent gets
          0  physical reads
      16292  redo size
        921  bytes sent via SQL*Net to client
       1012  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         99  rows processed

      插入99行，当前读为：70（正在找原因为什么这么大）

      在DML命令运行中，为了找到所要操作行的位置，会对块进行扫描，没扫描一个块都会增加一次一致读。通过rowid直接定位则不会产生额外的一致读：

scott@ORCL> update t1 set object_name = lower(object_name) where rowid='AAAVx3AAEAAAKycAAA';

已更新 1 行。

执行计划
----------------------------------------------------------
Plan hash value: 3177604904

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT            |      |     1 |    78 |     1   (0)| 00:00:01 |
|   1 |  UPDATE                     | T1   |       |       |            |          |
|   2 |   TABLE ACCESS BY USER ROWID| T1   |     1 |    78 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          1  db block gets               --只产生一个当前读
          0  consistent gets
          0  physical reads
        292  redo size
        920  bytes sent via SQL*Net to client
       1044  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@ORCL> update t1 set object_name = lower(object_name) where rownum<2;

已更新 1 行。


执行计划
----------------------------------------------------------
Plan hash value: 2733113716

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    66 |     3   (0)| 00:00:01 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T1   |    99 |  6534 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(ROWNUM<2)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          1  db block gets
         12  consistent gets              --12个一致读
          0  physical reads
        292  redo size
        921  bytes sent via SQL*Net to client
       1026  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

      从另一个角度对逻辑读下个定义，逻辑读，就是要求得到cache buffers chains latch的次数。cache buffers chains latch是保护cache buffers chains的一种闩锁。简单点说，如果你想对Hash Bucket上的Buffer进行操作，无论是访问Buffer，还是要将块读进Buffer cache，第一步就是先获得此闩锁。上例中，我们有一次物理读，块要被加进Buffer cache，获得cache buffers chains latch是必须的，每获得一次cache buffers chains latch，就是一次逻辑读，而且此逻辑读不能作为Db Block gets，只能作为一致读。因此，我们就看到如上例中所示，一次物理读，一次一致读，两次当前读。实际上上例中的更新操作并没有产生一致读，1次一致读，代表为了将块链接进Bucket，而对cache buffers chains latch加了一次闩锁。