Oracle IO问题解析(2)

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-04-07 14:57:09

分享到 

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<