Oracle IO问题解析（2）

作者： fuyuncat

来源： www.HelloDBA.com

1.2     读 1.2.1      物理读 产生物理读主要有以下几种情况：   第一次读取 当数据块第一次被读取到，Oracle会先将其从磁盘上读入Buffer Cache中，并将他们放在LRU（Last Recently Used）链表的MRU（Most Recently Used）端。再次访问数据块时就可以直接从Buffer Cache中读取、修改了。看以下例子： SQL> select owner, index_name from t_test3;   2856 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 2878488296   ----------------------------------------------------------------------------- | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         |  2856 | 68544 |    22   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| T_TEST3 |  2856 | 68544 |    22   (0)| 00:00:01 | -----------------------------------------------------------------------------     Statistics ----------------------------------------------------------         407  recursive calls          32  db block gets         344  consistent gets          89  physical reads           0  redo size      103888  bytes sent via SQL*Net to client        2475  bytes received via SQL*Net from client         192  SQL*Net roundtrips to/from client           9  sorts (memory)           0  sorts (disk)        2856  rows processed   SQL> select owner, index_name from t_test3;   2856 rows selected.   Elapsed: 00:00:00.03   Execution Plan ---------------------------------------------------------- Plan hash value: 2878488296   ----------------------------------------------------------------------------- | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         |  2856 | 68544 |    22   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| T_TEST3 |  2856 | 68544 |    22   (0)| 00:00:01 | -----------------------------------------------------------------------------     Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets         276  consistent gets           0  physical reads           0  redo size      103888  bytes sent via SQL*Net to client        2475  bytes received via SQL*Net from client         192  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        2856  rows processed   数据块被重新读入Buffer Cache 如果有新的数据需要被读入Buffer Cache中，而Buffer Cache又没有足够的空闲空间，Oracle就根据LRU算法将LRU链表中LRU端的数据置换出去。当这些数据被再次访问到时，需要重新从磁盘读入。   SQL> select owner, table_name from t_test2   2  where owner = 'SYS';   718 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 1900296288 -------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU) | Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |    99 |  2178 |    10   (0) | 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST2      |    99 |  2178 |    10   (0) | 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_TEST2_IDX1 |    99 |       |     1   (0) | 00:00:01 | --------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OWNER"='SYS')     Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets         145  consistent gets           0  physical reads           0  redo size       21690  bytes sent via SQL*Net to client         902  bytes received via SQL*Net from client          49  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         718  rows processed   SQL> select * from t_test1; --占用Buffer Cache   47582 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 1883417357   ----------------------------------------------------------------------------- | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         | 47582 |  3996K|   151   (2)| 00:00:02 | |   1 |  TABLE ACCESS FULL| T_TEST1 | 47582 |  3996K|   151   (2)| 00:00:02 | -----------------------------------------------------------------------------   Statistics ----------------------------------------------------------         195  recursive calls           0  db block gets        3835  consistent gets           5  physical reads           0  redo size     5102247  bytes sent via SQL*Net to client       35277  bytes received via SQL*Net from client        3174  SQL*Net roundtrips to/from client           5  sorts (memory)           0  sorts (disk)       47582  rows processed   SQL> select owner, table_name from t_test2   2  where owner = 'SYS';   718 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 1900296288   -------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU) | Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |    99 |  2178 |    10   (0) | 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST2      |    99 |  2178 |    10   (0) | 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_TEST2_IDX1 |    99 |       |     1   (0) | 00:00:01 | --------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OWNER"='SYS')     Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets         145  consistent gets          54  physical reads           0  redo size       21690  bytes sent via SQL*Net to client         902  bytes received via SQL*Net from client         49  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         718  rows processed   全表扫描 当发生全表扫描（Full Table Scan）时，用户进程读取表的数据块，并将他们放在LRU链表的LRU端（和上面不同，不是放在MRU端）。这样做的目的是为了使全表扫描的数据尽快被移出。因为全表扫描一般发生的频率较低，并且全表扫描的数据块大部分在以后都不会被经常使用到。 而如果你希望全表扫描的数据能被cache住，使之在扫描时放在MRU端，可以通过在创建或修改表（或簇）时，指定CACHE参数。 1.2.2      逻辑读 逻辑读指的就是从（或者视图从）Buffer Cache中读取数据块。按照访问数据块的模式不同，可以分为即时读（Current Read）和一致性读（Consistent Read）。注意：逻辑IO只有逻辑读，没有逻辑写。   即时读 即时读即读取数据块当前的最新数据。任何时候在Buffer Cache中都只有一份当前数据块。即时读通常发生在对数据进行修改、删除操作时。这时，进程会给数据加上行级锁，并且标识数据为“脏”数据。   SQL> select * from t_test1 where owner='SYS' for update;   22858 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 3323170753   ------------------------------------------------------------------------------ | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |         | 22858 |  1919K|   151   (2)| 00:00:02 | |   1 |  FOR UPDATE        |         |       |       |            |          | |*  2 |   TABLE ACCESS FULL| T_TEST1 | 22858 |  1919K|   151   (2)| 00:00:02 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("OWNER"='SYS')     Statistics ----------------------------------------------------------          44  recursive calls       23386  db block gets        2833  consistent gets           0  physical reads     5044956  redo size     2029221  bytes sent via SQL*Net to client       17138  bytes received via SQL*Net from client        1525  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)       22858  rows processed   一致性读 Oracle是一个多用户系统。当一个会话开始读取数据还未结束读取之前，可能会有其他会话修改它将要读取的数据。如果会话读取到修改后的数据，就会造成数据的不一致。一致性读就是为了保证数据的一致性。在Buffer Cache中的数据块上都会有最后一次修改数据块时的SCN。如果一个事务需要修改数据块中数据，会先在回滚段中保存一份修改前数据和SCN的数据块，然后再更新Buffer Cache中的数据块的数据及其SCN，并标识其为“脏”数据。当其他进程读取数据块时，会先比较数据块上的SCN和自己的SCN。如果数据块上的SCN小于等于进程本身的SCN，则直接读取数据块上的数据；如果数据块上的SCN大于进程本身的SCN，则会从回滚段中找出修改前的数据块读取数据。通常，普通查询都是一致性读。   下面这个例子帮助大家理解一下一致性读： 会话1中：   SQL> select object_name from t_test1 where object_id = 66;   OBJECT_NAME ------------------------------ I_SUPEROBJ1   SQL> update t_test1 set object_name = 'TEST' where object_id = 66;   1 row updated.   会话2中：   SQL> select object_name from t_test1 where object_id = 66;   OBJECT_NAME ------------------------------ I_SUPEROBJ1     Execution Plan ---------------------------------------------------------- Plan hash value: 1883417357   ----------------------------------------------------------------------------- | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         |     1 |    27 |   151   (2)| 00:00:02 | |*  1 |  TABLE ACCESS FULL| T_TEST1 |     1 |    27 |   151   (2)| 00:00:02 | -----------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("OBJECT_ID"=66)