|
针对以上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. Consistent gets Revision as of 16:16 2007年4月30日 by Liubin (Talk | 贡献) (差异) ←Older revision | Current revision (差异) | Newer revision→ (差异) 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中的过程。 http://www.itpub.net/468657.html http://www.itpub.net/628600.html http://www.itpub.net/119872.html http://www.itpub.net/225812.html http://www.dba-oracle.com/m_consistent_gets.htm http://www.itpub.net/305849.html http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865586003021 http://www.itpub.net/225812.html http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:848805436235 http://www.itpub.net/269435.html http://www.itpub.net/242144.html http://www.itpub.net/683047.html 取自"http://oracle.nanshapo.com/wiki/Consistent_gets" (责任编辑：IT) |