mysql磁盘读逻辑读_oracle 物理读 逻辑读的理解

Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> set autotrace traceonly

SQL> select * from test;

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

----------------------------------------------------------

175  recursive calls

0   db block gets

24  consistent gets9   physical reads             --9个物理读

0   redo size

373  bytes sent via SQL*Net to client

503  bytes received via SQL*Net from client

2   SQL*Net roundtrips to/from client

2   sorts (memory)

0   sorts (disk)

1   rows processed

1.2  第二次读取

SQL> select * from test;

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads     --没有发生物理读了,直接从buffer cache 中读取了

0  redo size

373  bytes sent via SQL*Net to client

503  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

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;

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

6  physical reads   --又重新发生了物理读

0  redo size

373  bytes sent via SQL*Net to client

503  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed