逻辑读的两种类型

        在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 processedscott@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加了一次闩锁。