同意！
不过精确翻译来说，前两个不是块数而是IO请求次数，不过对于这两者应该是一次读一块的

[quote]原帖由 [i]ern[/i] 于 2006-8-16 16:17 发表
同意！
不过精确翻译来说，前两个不是块数而是IO请求次数，不过对于这两者应该是一次读一块的 [/quote]

在这个地方的意思确实是按照block来处理的..

disk_reads/physical reads都是统计的block个数..

db file scattered read 与 db file parallel read是完全额io次数.

觉得还是不够详细，再补充一下：
[b]db block gets : [/b]
number of data blocks read in CURRENT mode ie) not in a read consistent fashion, but the current version of the data blocks. 产生db_block_gets的主要方式：
1.DML like Update, Delete will need to access the blocks in the current mode for modification.
2.Most data dictionary calls are done in CURRENT mode.
3.Buffers are often retrieved in current mode for INSERT, UPDATE, and DELETE, and SELECT FOR UPDATE statements. Blocks must be requested in current mode in order to be changed.
4.Also certain classes of blocks, like segment header blocks are always requested in current mode.
5. this will happen when you're doing a FULL TABLE SCAN on a table or a FAST FULL SCAN on an index. In those cases, the segment header is read (usually multiple times for some unknown reason) in current mode.

在current mode模式下产生的对block的访问叫db block gets，这些block在SGA中，不需要访问硬盘。
db_block_gets counts logical reads in CURRENT mode in the buffer cache and also includes direct read blocks (sort-on-disk) blocks.
.
[b]consistent gets : [/b]
number of data blocks accessed in READ CONSISTENT mode. When a block is requested in consistent mode, if any changes have been committed or made to that block since the requesting statement (or transaction) began, then they must be rolled back for the purposes of the read, to give a consistent view of the data at that time.(当数据库是在consistent mode模式下被请求的，则假如该数据块的数据修改是在查询语句发出之后被提交的，则此查询语句读到该数据块时，还必须为本次查询将数据回滚，得到查询语句发出时该数据块的值，以便给查询给出一个与该查询有关的所有数据块的一致性视图，这也就是oracle所说的一致性读)  In order to maintain statement level read consistency, Oracle has to read the blocks in a consistent fashion(as of the snapshot SCN) and hence may fetch from rollback segments , which is also added to this statistic. Buffers are usually retrieved in consistent mode for queries. 如普通的select语句、索引访问而引起的将数据读入到buffer中(也可能为physical read)或直接从buffer中读数据。注意，DML语句也能引起consistent gets，如update tab1 set col_b='Hello world' where col_a=1;，因为该语句需要找到需要被修改的所有数据块，在找数据块的过程中就会引起consistent gets。
在Read consistent mode模式下产生的对block的访问叫consistent gets。

[b]Session logical read is:[/b] The sum of "db block gets" plus "consistent gets".

[b]db_block_changes:[/b]
db_block_changes counts modifications made to CURRENT blocks
'db block changes' = changes made to current blocks under LOGING ( UPDATE, INSERT, DELETE) + changes made to SORT blocks ( NOLOGING).

[b]'consistent changes' :[/b]
changes made to block for CONSISTENT READ and changes made to SORT blocks

[b]physical reads :[/b]
Physical( disk and/or filesystem page cache) reads. Basically those that cannot be satisfied by the cache and those that are direct reads.
Total number of data blocks read from disk. This number equals the value of "physical reads direct" (direct from disk, excludes buffer cache) plus all reads into buffer cache.


[b]physical writes ：[/b]
Total number of data blocks written to disk. This number equals the value of "physical writes direct" (Number of writes directly to disk, bypassing the buffer cache as in a direct load operation) plus all writes from buffer cache.

要注意Oracle 的physical read 与 physical writes并不总是等于硬盘真正意义上的物理读与物理写，因为现在都存在操作系统高速缓存与磁盘子系统高速缓存，这样及时I/O没有被实际写入磁盘，操作系统I/O子系统或磁盘系统也会确认为一个成功的I/O，所以ORACLE 的physical read 与 physical writes并不是物理上发生读写的次数。


[b]redo block size：[/b]
redo block size is platform. specific. There is a method to determine the size by dumping the redo header, refer to note 154864.1. Redo blocks written does not include archive writes or multiplexed writes.

[b]redo entries:[/b]
The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database . Redo entries are used for database recovery, if necessary.
Redo entries are copied by Oracle server processes from the user's memory space to the redo log buffer in the SGA. (this copy is what the statistic represents).


db block changes包含dml修改的block，也包含为实现rollback功能而修改的block，但是不包含为实现redo功能而修改的block。

the number of blocks visited = consistent gets + db block gets
the number of blocks visited相当与logical read，即从内存中都数据块的次数。


[b]做一个测试：[/b]
用下面语句取得统计信息：
select sid,value,name from v$sesstat x,v$statname y
where x.statistic#=y.statistic#
and ( name like '%db block%gets%' or name like '%consistent%gets%'
or name like '%consistent%change%' or name like '%db block%change%'
or name like '%physical writes%' or name like '%physical reads%'
or name = 'index fetch by key' or name like '%read%' or name like '%scans%'  
)
and sid = 12 and value > 0
order by value;


Lets take an example to illustrate relationship between 'db block gets' and 'db block changes' .

create table toto( i number);

select sid,value,name from v$sesstat x,v$statname y
where x.statistic#=y.statistic#
and ( name like '%db block%gets%' or name like '%consistent%gets%'
or name like '%consistent%change%' or name like '%db block%change%'
or name like '%physical writes%' or name like '%physical reads%'
or name = 'index fetch by key' or name like '%read%' or name like '%scans%'  
)
and sid = 12 and value > 0
order by value;

SID VALUE NAME
---------- ---------- ------------------------------
11 95 db block gets
11 108 db block changes
11 0 consistent changes

select count(*) from toto x, toto y, toto z;
COUNT(*)
----------
0

select sid,value,name from v$sesstat x,v$statname y
where x.statistic#=y.statistic#
and ( name like '%db block%gets%' or name like '%consistent%gets%'
or name like '%consistent%change%' or name like '%db block%change%'
or name like '%physical writes%' or name like '%physical reads%'
or name = 'index fetch by key' or name like '%read%' or name like '%scans%'  
)
and sid = 11 and value > 0
order by value

SID VALUE NAME
---------- ---------- ------------------------------
11 98 db block gets
11 108 db block changes
11 0 consistent changes

Here we can see that a select increment 'db block gets' by 3 and 'db block changes' remains the same : this illustrates that we can get block in current mode ( to have most recent information) without performing any changes." – 这也可以作为current mode的定义。



下面这段写的非常好，如果上面的还是不理解，则需要仔细的看一下！
A 'consistent get' is your server process telling the database "I need this dba (data block address) consistent with the point in time represented by this SCN, x."

So, lots of things can happen here. First, Oracle will look in the buffer cache for a CR (consistent read) buffer of the block that's consistent w/ the requested SCN. It may find it, if it does, that's counted as a 'consistent get' and either a 'consistent gets - no work' or 'consistent gets - cleanouts only', depending on whether the block needed to be cleaned out. (See V$SYSSTAT/V$SESSTAT for the statistics.) If it doesn't, it may take an existing CR buffer and roll it back further, or it may clone the current block and roll it back. If it needs to apply rollback (aka undo) then it will increment 'consistent gets' and either 'consistent gets - rollbacks only' or 'consistent gets - cleanouts and rollbacks'.

So, each 'consistent get' is your server process successfully getting access to the contents of a dba consistent w/ a particular SCN. This number should represent the number of buffer gets required to satisfy a particular query.

Now, 'db block gets'. A 'db block get' is a copy of the 'current mode block'. That is, the data in the block, as it exists currently, or at this point in time. Note that while multiple CR copies of a block may exist in the buffer cache, there can only ever be one current mode copy of a block in the buffer cache at any one time. (RAC is a special case, w/ shared current and exclusive current, but I'm not going to get into that here.) So, a 'db block get' is a buffer get in current mode. 'db block gets' are usually associated w/ DML, and in that scenario, will implicitly lock one or more rows in that block. Also, there is a notable case where db block gets can occur with a select statement. That will happen when you're doing a FULL TABLE SCAN on a table or a FAST FULL SCAN on an index. In those cases, the segment header is read (usually multiple times for some unknown reason) in current mode.

Next, 'physical reads': A physical read will occur any time a consistent get or a db block get goes looking for block and can't find it in the buffer cache. So, for each block read from disk, physical reads will be incremented. Gets which result in physical reads are counted both as as get and as a read in the statistics. So, if you do 10 consistent gets and 5 of them require physical reads, you should see consistent gets incremented by 10 and physical reads incremented by 5.

Now, what's up w/ arraysize? Well, arraysize is the client side setting for SQL*Plus specifying the size of the array that will receive result sets. The default, as you learned, is 15. Now, suppose you have a table where there are 30 records per block, and 3,000 rows in the table. But, your arraysize is 15. So, your server process will get the first 15 rows, and return them from the first block buffer. Now, for the next 15, you need to get that same block again, for rows 16-30. So, you need to do two buffer gets per block to get all the rows. For a 3,000 row table, you'll do (approximately) 3,000/15 = 200 buffer gets. If you change your arraysize to 30, you can get away w/ visitng each block only once and do 3,000/30 = 100 buffer gets.

So, consider that even after you've optimized a particular SQL statement, if the arraysize is too small, you're going to force your server process to do excess database calls (FETCH calls), and extra buffer gets as well. This can best be illustrated with a test similar to what you did, but try looking at the raw trace file for FETCH calls. The number of FETCH calls ought to be very close to (number of rows returned / arraysize). The 'r=xxx' in the FETCH call data in the trace file is the number of rows returned, which is probably what your arraysize is set to.

So, db block gets, consistent gets, and physical reads are all measured in buffers (or blocks). If the same block is requested multiple times, it will be counted that many times in these statistics. Oracle will always access data from the buffer cache by the buffer. All 'get' operations are by the buffer, never by row. After the buffer is 'gotten', Oracle parses the block to get the data for the relevant rows.


When a "consistent get" is done, this doesn't necessarily mean that Oracle had to do any rollback/undo of DML in order to get the consistent image. Rather, it just means that Oracle requested a copy of the block as of the point in time that the query was started (a "consistent snapshot"). So, I believe it is quite normal to have many, many consistent gets even if there is NO DML occuring.


自己做的一个更详细的测试：
用到的查询统计信息的语句;
select sid,value,name from v$sesstat x,v$statname y
where x.statistic#=y.statistic#
and ( name like '%db block%gets%' or name like '%consistent%gets%'
or name like '%consistent%change%' or name like '%db block%change%'
or name like '%physical writes%' or name like '%physical reads%'
or name = 'index fetch by key' or name like '%read%' or name like '%scans%'  
)
and sid = 11 and value > 0
order by value

Scott用户登录，但是没有做任何操作，sid为10，在另外一个用户下查询：
       SID      VALUE NAME
---------- ---------- --------------------------------
        11          3 db block gets
        11          0 physical reads
        11          4 db block changes
        11          0 consistent changes
        11          0 physical writes
        11          0 physical writes non checkpoint
        11          0 physical reads direct
        11          0 physical writes direct
        11          0 physical reads direct (lob)
        11          0 physical writes direct (lob)

在scott sid=11下运行select * from emp后，结果没有任何变化，这是不应该的，应该有physical reads与db block gets的值变化

在scott sid = 11下运行update emp set comm = 10; 在另外一个用户下查询：
      SID      VALUE NAME
--------- ---------- ------------------------------
       11         18 db block gets
       11          0 physical reads
       11         33 db block changes
       11          0 consistent changes
       11          0 physical writes
       11          0 physical writes non checkpoint
       11          0 physical reads direct
       11          0 physical writes direct
       11          0 physical reads direct (lob)
       11          0 physical writes direct (lob)

再次在scott sid = 11的同一个session下运行update emp set comm = 10; 在另外一个用户下查询：
    SID      VALUE NAME
------- ---------- -------------------------------
     11         32 db block gets
     11          0 physical reads
     11         61 db block changes
     11          0 consistent changes
     11          0 physical writes
     11          0 physical writes non checkpoint
     11          0 physical reads direct
     11          0 physical writes direct
     11          0 physical reads direct (lob)
     11          0 physical writes direct (lob)
不要commit;

用scott再次登录, sid 为17，在另外一个用户下查询：
    SID      VALUE NAME
------- ---------- --------------------------------
     17          3 db block gets
     17          0 physical reads
     17          4 db block changes
     17          0 consistent changes
     17          0 physical writes
     17          0 physical writes non checkpoint
     17          0 physical reads direct
     17          0 physical writes direct
     17          0 physical reads direct (lob)
     17          0 physical writes direct (lob)

在scott session 17，下运行select *?from emp 后，在另外一个用户下查询：
     SID      VALUE NAME
-------- ---------- --------------------------------
      17          3 db block gets
      17          0 physical reads
      17          5 db block changes
      17         28 consistent changes
      17          0 physical writes
      17          0 physical writes non checkpoint
      17          0 physical reads direct
      17          0 physical writes direct
      17          0 physical reads direct (lob)
      17          0 physical writes direct (lob)

再次在scott session 17，下运行select *?from emp 后，在另外一个用户下查询：
  SID      VALUE NAME
----- ---------- ---------------------------------
   17          3 db block gets
   17          0 physical reads
   17          6 db block changes
   17         56 consistent changes
   17          0 physical writes
   17          0 physical writes non checkpoint
   17          0 physical reads direct
   17          0 physical writes direct
   17          0 physical reads direct (lob)
   17          0 physical writes direct (lob)

在此在scott sid = 11的同一个session下运行rollback; 在另外一个用户下查询：
   SID      VALUE NAME
------ ---------- ------------------------------
    11         89 db block gets
    11          0 physical reads
    11        118 db block changes
    11          0 consistent changes
    11          0 physical writes
    11          0 physical writes non checkpoint
    11          0 physical reads direct
    11          0 physical writes direct
    11          0 physical reads direct (lob)
11          0 physical writes direct (lob)

再次在scott sid = 11的同一个session下运行update emp set comm = 10; 在另外一个用户下查询：
SID      VALUE NAME
---- ---------- --------------------------------
  11        104 db block gets
  11          0 physical reads
  11        147 db block changes
  11          0 consistent changes
  11          0 physical writes
  11          0 physical writes non checkpoint
  11          0 physical reads direct
  11          0 physical writes direct
  11          0 physical reads direct (lob)
  11          0 physical writes direct (lob)

再次在scott sid = 11的同一个session下运行commit; 在另外一个用户下查询：
SID      VALUE NAME
---- ---------- ------------------------------
  11        105 db block gets
  11          0 physical reads
  11        148 db block changes
  11          0 consistent changes
  11          0 physical writes
  11          0 physical writes non checkpoint
  11          0 physical reads direct
  11          0 physical writes direct
  11          0 physical reads direct (lob)
  11          0 physical writes direct (lob)

经过初步试验，发现如果开始运行select * from emp后，然后再运行select * from emp where empno = 7902, 不管运行多少此，都不会引起physical reads与db block gets的增加。因为db block gets是在current mode模式中产生的。
而是只会引起session logical reads与index fetch by key、consistent gets的增加。
如运行
select sid,value,name from v$sesstat x,v$statname y
where x.statistic#=y.statistic#
and ( name like '%db block%change%' or name like '%db block%get%'
or name like '%consistent%change%' or name like '%physical reads%'
or name like '%physical writes%' or name like '%scans%'
or name = 'index fetch by key' or name like '%read%'
)
and sid = 11 and value > 0
order by value
查询后，得到：
SID      VALUE NAME
---- ---------- -------------------------------
  11          3 db block gets
  11          4 db block changes
  11          4 index scans kdiixs1
  11          5 index fetch by key
  11          6 table scans (short tables)
  11          8 no work - consistent read gets
  11         42 session logical reads

将数据库关闭，重新启动后，重新用scott联接，sid = 12立即查询统计信息，得到：
SID      VALUE NAME
---- ---------- -------------------------------
  12          6 db block gets
  12          6 table scans (short tables)
  12          8 db block changes
  12         53 cluster key scans
  12         89 index fetch by key
  12        145 physical reads
  12        163 index scans kdiixs1
  12        337 consistent gets - examination
  12        423 no work - consistent read gets
  12        945 consistent gets
  12        951 session logical reads

在sid =12 session中运行select * from emp where empno = 7902;后，得到统计信息：
SID      VALUE NAME
---- ---------- -------------------------------
  12          6 db block gets
  12          6 table scans (short tables)
  12          8 db block changes
  12         60 cluster key scans
  12         98 index fetch by key
  12        150 physical reads
  12        168 index scans kdiixs1
  12        360 consistent gets - examination
  12        439 no work - consistent read gets
  12        990 consistent gets
  12        996 session logical reads
再次运行select * from emp;则db block gets与physical reads不发生变化

将数据库关闭，重新启动后，重新用scott联接，sid = 12立即查询统计信息，得到：
SID      VALUE NAME
---- ---------- --------------------------------
  12          6 db block gets
  12          6 table scans (short tables)
  12          8 db block changes
  12         53 cluster key scans
  12         89 index fetch by key
  12        145 physical reads
  12        163 index scans kdiixs1
  12        337 consistent gets - examination
  12        423 no work - consistent read gets
  12        945 consistent gets
  12        951 session logical reads

在sid =12 session中运行select * from emp;后，得到统计信息：
SID      VALUE NAME
--- ---------- -------------------------------
12          6 db block gets
12          7 table scans (short tables)
12          8 db block changes
12         60 cluster key scans
12         97 index fetch by key
12        149 physical reads
12        168 index scans kdiixs1
12        358 consistent gets - examination
12        441 no work - consistent read gets
12        992 consistent gets
12        998 session logical reads
再次运行select * from emp;则db block gets与physical reads不发生变化


希望这些信息对大家更清楚的了解db block gets , consistent gets 有帮助！

兄弟们补充的都很好啊，这篇文章的初衷是为了回答调优版块一朋友对这三个概念的困惑
阿的文章权作抛砖引玉，也希望此帖对有同样困惑的朋友有所启发和提高
如没人反对 洒家就来给此帖加个精 ^_^

引用记得写明出处. :)

up,好久没有技术:)精华贴了.

解释的非常透彻

写得太好了。顶

[quote]原帖由 [i]SunnyXu[/i] 于 2006-8-18 09:28 发表
.....
db_block_gets的主要方式：
2.Most data dictionary calls are done in CURRENT mode.
4.Also certain classes of blocks, like segment header blocks are always requested in current mode.
5. this will happen when you're doing a FULL TABLE SCAN on a table or a FAST FULL SCAN on an index. In those cases, the segment header is read (usually multiple times for some unknown reason) in current mode.
.....
[/quote]
SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1310  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)
         14  rows processed

为什么我这里全表扫描db block gets却是0呢？

当然是0了，我觉得应该就是0。对于select 来说，一般db block gets为0

那你这句怎么解释啊：
this will happen when you're doing a FULL TABLE SCAN on a table ？

SunnyXu能解释下为什么全表扫描的db block gets是0吗？实在郁闷，不解啊………………

为什么我发出：
select * from t for update;
的时候一定会看到db block gets，而发出：
select * from t;
的时候就基本上看不到呢？？？狂郁闷中…………………………………………

2)

physical reads 物理读
db block gets ---current mode read
consistent gets ---一致性杜
db block gets+consistent gets=逻辑读

DB Bocks gets，Consistent gets And Physical reads


DB block gets，Consistent gets，Physical reads与Logical reads各自具体表示的是什么以及之间的的关系到底是怎样。

官方在线文档基本的定义为如下：
DB block gets：the number of accesses to the current image of a block
Consistent gets：the number of accesses to a read-consistent image of a block
Physical reads：the number of blocks read from disk


这其中主要涉及到了Oracle读取数据的consistent mode和current mode这两个模式，对于db block gets是在current mode下读取的block数目（单位应该是“块次”，同一个block读取了两个算做2），而consistent gets是在consistent mode下读取的block数目（单位同上）。
current mode下读取数据是为了保证读取到的数据是当前时间点上最新的数据，这样做的目的一般都是为了DML语句的需求，比如需要更新，自然需要知道最新的数据才行；consistent mode呢主要是为了保证Oracle数据一致读的特性，一般都是在select情况下发生，读到的数据可能是一个实际存在的block，也有可能需要根据scn信息以及transaction相关信息以及回滚段中数据来构造。

而physical reads是与logical reads相对的一个概念，两者的区别是读取的数据是从buffer中读取到还是从disk上的db file中取到。通过v$sysstat也可以看到，里面还有db block gets from cache以及consistent gets from cache两项，且这两项的数值与db block gets和consistent gets并不相同且小于后两者。所以不管是db block gets还是consistent gets，都可能出现了physical reads和logical reads两种情况（由buffer中的是否已经存在需要的数据），也即是说，db block gets与consistent gets两者已经构成了一次数据库操作中读取的所有block的总次数了。因此，logical reads自然也就可以通过如下公式算的：logical reads = (db block gets + consistent gets) - physical reads。

由此，自然也就得出了cache命中率的公式：
Hit Ratio = (db block gets + consistent gets - physical reads) / (db block gets + consistent gets)
OR
Hit Ratio = 1 – (physical reads/(db block gets + consistent gets))

PS:
由于在Oracle中，取数据最后都是从Buffer中取，所以每出现一个physical reads必然会出现一次 logical reads，但是这里有一个需要注意的地方，就是当出现一个physical reads后接着会有一个logical reads这里，实际上这里只算了1 block（physical reads）！