oracle 逻辑读 物理读

13年前
1.物理读(physical read)
当数据块第一次读取到,就会缓存到buffer cache 中,而第二次读取和修改该数据块时就在内存buffer cache
1.1  第一次读取
SQL> set autotrace traceonly;
SQL> create table test_read as select * from dba_objects;
Table created.
 
SQL> select * from test_read;
 
50349 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4187308185
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 53569 |  9259K|   157   (3)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TEST_READ | 53569 |  9259K|   157   (3)| 00:00:02 |
-------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
        288  recursive calls
          0  db block gets
       4120  consistent gets
        691  physical reads
          0  redo size
    5404806  bytes sent via SQL*Net to client
      37301  bytes received via SQL*Net from client
       3358  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50349  rows processed

1.2  第二次读取

SQL> select * from test_read;
 
50349 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4187308185
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 53569 |  9259K|   157   (3)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TEST_READ | 53569 |  9259K|   157   (3)| 00:00:02 |
-------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4019  consistent gets
          0  physical reads
          0  redo size
    5404806  bytes sent via SQL*Net to client
      37301  bytes received via SQL*Net from client
       3358  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50349  rows processed
 
SQL>

1.3  数据块被重新读入buffer cache

如果有新的数据需要被读入Buffer Cache中,而Buffer Cache又没有足够的空闲空间,Oracle就根据LRU算法将LRU链表中LRU端的数据置换出去。
当这些数据被再次访问到时,需要重新从磁盘读入。 

SQL> alter session set events 'immediate trace name flush_cache';
 
Session altered.
 
SQL> select * from test_read;
 
50349 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4187308185
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 53569 |  9259K|   157   (3)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TEST_READ | 53569 |  9259K|   157   (3)| 00:00:02 |
-------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4019  consistent gets
        692  physical reads
          0  redo size
    5404806  bytes sent via SQL*Net to client
      37301  bytes received via SQL*Net from client
       3358  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50349  rows processed
 
SQL> 

2.逻辑读(buffer read)

逻辑读指的就是从(或者视图从)Buffer Cache中读取数据块。按照访问数据块的模式不同,可以分为即时读(Current Read)和一致性读(Consistent Read)。
注意:逻辑IO只有逻辑读,没有逻辑写。

(1)、即时读(db block gets)
即时读即读取数据块当前的最新数据。任何时候在Buffer Cache中都只有一份当前数据块。即时读通常发生在对数据进行修改、删除操作时。
这时,进程会给数据加上行级锁,并且标识数据为“脏”数据。

SQL> select * from test_read for update;                       
 
50349 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 428907831
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           | 53569 |  9259K|   157   (3)| 00:00:02 |
|   1 |  FOR UPDATE        |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST_READ | 53569 |  9259K|   157   (3)| 00:00:02 |
--------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
        215  recursive calls
      51449  db block gets
       4894  consistent gets
          6  physical reads
   10505524  redo size
    4507795  bytes sent via SQL*Net to client
      37301  bytes received via SQL*Net from client
       3358  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50349  rows processed
 
SQL> 

(2)、一致性读(consistent gets)

Oracle是一个多用户系统。当一个会话开始读取数据还未结束读取之前,可能会有其他会话修改它将要读取的数据。
如果会话读取到修改后的数据,就会造成数据的不一致。一致性读就是为了保证数据的一致性。在Buffer Cache中的数据块上都会有最后一次修改数据块时的SCN。
如果一个事务需要修改数据块中数据,会先在回滚段中保存一份修改前数据和SCN的数据块,然后再更新Buffer Cache中的数据块的数据及其SCN,并标识其为“脏”数据。
当其他进程读取数据块时,会先比较数据块上的SCN和自己的SCN。如果数据块上的SCN小于等于进程本身的SCN,则直接读取数据块上的数据;
如果数据块上的SCN大于进程本身的SCN,则会从回滚段中找出修改前的数据块读取数据。通常,普通查询都是一致性读。

下面这个例子帮助大家理解一下一致性读:

a、第一个会话

SQL> update test_read set status='Y' where object_id=20;

b、第二个会话

SQL> set autotrace traceonly;
SQL> select * from test_read;
 
50349 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4187308185
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 53569 |  9259K|   157   (3)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TEST_READ | 53569 |  9259K|   157   (3)| 00:00:02 |
-------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4020  consistent gets
          0  physical reads
          0  redo size
    5404802  bytes sent via SQL*Net to client
      37301  bytes received via SQL*Net from client
       3358  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50349  rows processed
 
SQL> 

结果:明显看到目前是:4020  consistent gets
而以前是 :4019  consistent gets
说明这多出的一个是要从回滚段中获取的
3、简单例子分析一下逻辑读中的一致性读和即时读
  1. Case1:    
  2. HELLODBA.COM>set time on    
  3. 10:22:09 HELLODBA.COM>update t_test1 set SECONDARY='A' where object_id = -1;    
  4. 1 row updated.    
  5. 10:22:22 HELLODBA.COM>commit;    
  6. Commit complete.    
  7. Session 1:    
  8. 10:22:25 HELLODBA.COM>update t_test1 set SECONDARY='B' where  object_id = -1 and SECONDARY='B' and (select count(*) from t_test2 t1, t_test2 t2) > 0;    
  9. rows updated.    
  10. 10:23:15 HELLODBA.COM>    
  11. Session 2:    
  12. 10:22:37 HELLODBA.COM>update t_test1 set SECONDARY='B' where object_id = -1;    
  13. 1 row updated.    
  14. 10:23:02 HELLODBA.COM>commit;    
  15. Commit complete.    
  16. 10:23:04 HELLODBA.COM>    
  17. Case2:    
  18. 10:25:38 HELLODBA.COM>update t_test1 set SECONDARY='A' where object_id = -1;    
  19. 1 row updated.    
  20. 10:25:48 HELLODBA.COM>commit;    
  21. Commit complete.    
  22. Session 1:    
  23. 10:26:05 HELLODBA.COM>update t_test1 set SECONDARY='B' where  object_id = -1 and SECONDARY='A' and (select count(*) from t_test2 t1, t_test2 t2) > 0;    
  24. rows updated.    
  25. 10:27:21 HELLODBA.COM>    
  26. Session 2:    
  27. 10:26:16 HELLODBA.COM>update t_test1 set SECONDARY='B' where object_id = -1;    
  28. 1 row updated.    
  29. 10:26:41 HELLODBA.COM>commit;    
  30. Commit complete.    
  31. 10:26:42 HELLODBA.COM>   

如果你观察得足够仔细,你可以从上面2个例子看到一个有趣的现象:无论session 1是否命中到数据,it最终都没有修改数据。其根本原因就是当前模式读与一致性读的区别。

我们知道,为了减少并发冲突,Oracle引入了MVCC(多版本并发控制,也叫MCC)方法。在这种机制中,并发事务不会因为一致性的原因而相互阻塞,除非他们要修改同一条记录。他们会将日志中所有SCN大于本身事务SCN的日志做回滚,以保证本事务读取到的数据块与事务SCN的一致。在Oracle中,这样的读取行为就称为一致性读。

然而,一致性读所读取到数据块仅仅是某个时间点的一个快照,也就是说这样的数据是只读的。如果要修改数据,那么oracle需要读取到当前的数据块,也就是当前模式读。

在一个UPDATE过程中,oracle会先一致性读取与事务SCN一致的数据快照,并用where条件进行过滤。让后根据读取到数据块的ID,再从当前数据中读取到相应的数据块进行修改。但是,如在事务启动后到数据块被读取之间的这段时间内,相应的数据块发生了改变,那么可能就会有我们意想不到的事情发生。

往回看我们的第一个例子。我们在session 1中,在10:22:25启动了update事务。但是,由于该事务中存在一个大的子查询,它会在几十秒后才会读取到需要被修改的数据。在Session 2中,我们在10:22:37开始update这些数据并在10:23:02提交了事务。而这个时间是早于数据在session 1中被读取到的时间的。当session 2中的数据改变被提交后,session 1中的事务读取到了该数据块。因为session 2中的事务SCN大于session 1中的事务SCN,因此会读取UNDO中的数据进行回滚,也就是说它读取到数据SECONDARY是'A',再通过条件(SECONDARY='B')过滤后,没有数据被命中,因此也没有数据被修改。

在第二个例子中,session 1的事务在一致性读取到数据块之前也发生了类似的事情。当它回滚了数据后,它一致性读取到了满足过滤条件(SECONDARY='A')的数据块。此时,它需要通过该数据块ID再到当前数据中读取该数据块。但是因为当前数据块的内容已经被session 2中的事务所修改,它还是没有能修改到数据。

我想,通过这两个例子,读者应该更容易理解到当前模式读与一致性读之间的区别。