1.物理读(physical read)
当数据块第一次读取到,就会缓存到buffer cache 中,而第二次读取和修改该数据块时就在内存buffer cache 了以下是例子:
1.1 第一次读取:
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Feb 28 09:32:04 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and 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 gets
9 physical reads --9个物理读
0 redo size
373 bytes sent via SQL*Net to client
vf$u2\ V6]Q350296 503 bytes received via SQL*Net from clientITPUB个人空间~!?;y{$@
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;
Statistics
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
2.逻辑读(buffer read)
逻辑读指的就是从（或者视图从）Buffer Cache中读取数据块。按照访问数据块的模式不同，可以分为即时读（Current Read）和一致性读（Consistent Read）。注意：逻辑IO只有逻辑读，没有逻辑写。
- 即时读
即时读即读取数据块当前的最新数据。任何时候在Buffer Cache中都只有一份当前数据块。即时读通常发生在对数据进行修改、删除操作时。这时，进程会给数据加上行级锁，并且标识数据为“脏”数据。
SQL> select * from test for update;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
1 0 FOR UPDATE
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
14 consistent gets
0 physical reads
252 redo size
386 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
- 一致性读
Oracle是一个多用户系统。当一个会话开始读取数据还未结束读取之前，可能会有其他会话修改它将要读取的数据。如果会话读取到修改后的数据，就会造成数据的不一致。一致性读就是为了保证数据的一致性。在Buffer Cache中的数据块上都会有最后一次修改数据块时的SCN。如果一个事务需要修改数据块中数据，会先在回滚段中保存一份修改前数据和SCN的数据块，然后再更新Buffer Cache中的数据块的数据及其SCN，并标识其为“脏”数据。当其他进程读取数据块时，会先比较数据块上的SCN和自己的SCN。如果数据块上的SCN小于等于进程本身的SCN，则直接读取数据块上的数据；如果数据块上的SCN大于进程本身的SCN，则会从回滚段中找出修改前的数据块读取数据。通常，普通查询都是一致性读。
下面这个例子帮助大家理解一下一致性读：
会话1中：
SQL> select * from test;
----------
1000
SQL> update test set id=2000;
1 row updated.
会话2中:
SQL> set autotrace on
SQL> select * from test;
----------
1000
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)
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets 没有事物做update时 是 7 consistent gets 说明多了2个 consistent gets 这2个是要从回滚段中获取的
0 physical reads
52 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)ITPUB个人空间.c&otUL"n
0 sorts (disk)
1 rows processed
db block gets、Consistent gets、Physical Reads 三个概念
db block gets：Number of times a CURRENT block was requested. consistent gets：Number of times a consistent read was requested for a block. physical reads：Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
--------------------------------------------------------------------------------
针对以上3个概念进行的说明解释及关系如下：
1、DB Block Gets（当前请求的块数目）当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。
2、Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操 作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产 生了一致性读。
3、Physical Reads（物理读）就是从磁盘上读取数据块的数量，其产生的主要原因是：
1、 在数据库高速缓存中不存在这些块
2、 全表扫描
3、 磁盘排序
它们三者之间的关系大致可概括为：逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'phsical reads'。
· Recursive Calls. Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls.
· DB Block Gets. Number of times a CURRENT block was requested.
Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them. (DB Block Gets:请求的数据块在buffer能满足的个数)
· Consistent Gets. Number of times a consistent read was requested for a block. This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification. (Consistent Gets:数据请求总数在回滚段Buffer中)
· Physical Reads. Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量)
· Sorts (disk). Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.
Oracle accesses blocks in one of two modes, current or consistent.
A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version).
A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".
As to latching, and how it relates, well, consider that the block buffers are in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that point to the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuning SQL to use more efficient access paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable.
db block gets 当前的block是什么数据，那么读到的就是什么数据比如数据是session自己产生的在dml的时候读block中 数据 也必须是当前block的
而在查询中，block中数据如果是别人更改过的，需要去回滚段中读取变化前的数据，这时产生consistent reads 。这个叫 一致读，也就是块处于 query mode 下
但是 consistent gets 是在query mode下的读，即使没有产生 consistent reads ,但是也叫 consistenet gets
consistent gets＝一致性读的block的个数，简单理解就是select访问的block个数 ;db block gets＝简单理解，就是update、insert、delete访问的block个数
buffer gets = db block gets + consistent gets tuning 单个sql 不应该 物理读为标准，而应该以逻辑读(buffer gets)为标准在数据库整体上来讲，是要降低 物理读而对于单条 sql 来讲，是以逻辑读的降低为标准的道理很简单，对于单条sql来说，如果反复运行，物理读决定于 data buffer 的大小 ，第一次运行 和 第二次运行也是不一样的。但比较稳定的是 逻辑读。 如果以物理读为标准，那难道物理读为0的sql就是好sql 了？如果sql的逻辑读都良好，那数据库整体的物理读降下来也是很自然的事情
db block gets 和 physical reads 完全是两个不同的概念！通常情况下 db block gets 可以理解为是 dml 产生的
consistent gets query mode 下 read 次数 比如根据索引查询可能对同一个 block读多次那就是 多个 constent gets
ff consistent gets 是指为保持一致性， 而从undo segment处取得数据． ff 这是读一致性下获取的数据块，从undo 读数据叫 consistent reads , consistent gers 包含了 consistent reads
db block get+consistent gets = logical read
consistent gets是BUFFER中的读的部分 加上 直接在表的BLOKC中读的部分
如果是第一次，那么有部分数据在BUFFER中，有部分数据在可能DISK上，这时就要是BUFFER中的读的部分 加上 直接在表的BLOKC中读的部分 , 同样 同时如果数据返回的行数据没有大于arraysize，这时就不会受arraysize的影响，如果返回数据大于arraysize，就会受影响
如果不是第一次读，所有的数据都在BUFFER中了，如果有block已经被修改但是还没有被写回datafile，生了多版本的table block 。这时consistent gets主要数据就是在UNDO重新构造并且在去读的数据，同时如果数据返回的行数据没有大于arraysize，这时就不会受arraysize的影响，如果返回数据大于arraysize，就会受影响
先不谈arraysize对consistent get的影响 1、consistent gets是BUFFER中的读的部分 加上 直接在表的BLOKC中读的部分 如何理解直接在表的block中读？ 2、如果不是第一次读，所有的数据都在BUFFER中了，如果有block已经被修改但是还没有被写回datafile，生了多版本的table block 。这时consistent gets主要数据就是在UNDO重新构造并且在去读的数据这里感觉有些问题，假如block被修改了，我觉得和是否写到datafile文件没有关系，应该和是否commit有关，没有commit则读undo，commit了则还是要读buffer，buffer中没有从datafile中读到buffer，如果没有还没有写到datafile，则需要从redo中构造！
consistent gets读取就是从buffer中读,至于没有进行BUFFER的数据块,他会进行缓存,我们只能说此时的consistent gets引起了磁盘IO,所有要读取的数据块,还都是要经过缓存的,每个consistent gets都会引起latch,这也许是TOM强调调整时关注逻辑读的原因
数据库正常ONLINE状况下,是不会读取REDO了,REDO只是供恢复使用,所以不存在从REDO中构造什么数据
logical I/O 与 physical I/O 的区别：
1）logical I/O是指对cache到 Buffer Cache 中的数据块(db block)的访问请求。又分为:consistent gets 和 db block gets(current gets) consistent gets代表对特定版本或时间的block的访问select db block gets代表对最新的或当前的block的访问，通常用于Insert,update,delete
2）physical I/O是指通过底层的I/O系统，对没有cache到 Buffer Cache 中的数据块(db block)的访问请求。其实就是oracle在buffer cache中没有发现需要的block,而去访问datafile,并且将block populate到buffer cache中的过程。