ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Consistent Gets,Physical Reads和DB Block Gets的解释(转)

Consistent Gets,Physical Reads和DB Block Gets的解释(转)

原创 Linux操作系统 作者:流浪的野狼 时间:2013-10-29 11:09:37 0 删除 编辑

在Oracle的文档中有这样的解释:

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中的数据一致性读所需要的数据块)
这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块,这些块产生的主要原因是因为由于在你查询的过程中,由于其他会话对数据块进行操作,而对所要查询的块有了修改,但是由于我们的查询是在这些修改之前调用的,所以需要对回滚段中的数据块的前映像进行查询,以保证数据的一致性。这样就产生了一致性读。 不过精确翻译来说,前两个不是块数而是IO请求次数,不过对于这两者应该是一次读一块的。
3 Physical Reads(物理读)
就是从磁盘上读取数据块的数量,其产生的主要原因是:
在数据库高速缓存中不存在这些块
全表扫描
磁盘排序
它们三者之间的关系大致可概括为:

逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取,于是就产生了'phsical reads'。

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.

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.

DB Block Gets. 当前块被请求的次数。

当存在时,当前模式块将被立即检索,而不会以一致读的方式检索。通常,查询检索的块如果在查询开始时存在,它们就被检索。当前模式块如果存在就立即被检索,而不是从一个以前的时间点检索。在一个SELECT期间,你可以看到当前模式检索,因为对于需要进行全面扫描的表来说,需要读数据字典来找到范围信息(因为你需要"立即"信息,而不是一致读)。在修改期间,为了向块中写入内容,你要以当前模式访问块。

Consistent Gets. 对于一个块一致读被请求的次数。

这是你以"一致读"模式处理的块数。为了回滚一个块,这将包括从回滚段读取的块的数目。例如,这是你在SELECT语句中读取块的模式。当你进行一个指定的UPDATE/DELETE操作时,你也以一致读模式读取块,然后以当前模式获得块以便实际进行修改。

引自(biti_rainy):

db block gets

当前的block是什么数据,那么读到的就是什么数据 比如数据是session自己产生的 在dml的时候读block中 数据 也必须是当前block的 而在查询中,block中数据如果是别人更改过的,需要去回滚段中读取变化前的数据,这时产生consistent reads 。这个叫 一致读,也就是块处于 query mode 下 但是 consistent gets 是在query mode下的读,即使没有产生 consistent reads ,但是也叫 consistenet gets

db block gets : current mode , 不管这个块上的数据是否可能存在before image ,也就是说不管是否存在回滚中的数据可以回滚,只看见当前最新块的数据,即使别人正在更新,也看见别人更新状态的数据,比如dml的时候就不需要看见别人更改前的数据,而是看见正在更改的,当然同时,若操作相同数据则被lock住。也就是说一次查询中看见的数据可能不在同一个时间点上。

consistent gets : 看见的数据是查询开始的时间点的,所以若存在block在查询开始后发生了变化的情况,则必须产生 before image 然后读数据,这就是一致读的含义。

下面这段话比较重要:

db block gets 仅仅表达,即使有回滚段内容也不用去产生before image consistent get 仅仅表达,如果有回滚段内容并且block的变化的提交时间点晚于查询开始的时间点就要产生 before image ,但只有满足这个条件的时候才产生(回滚),并不是说一定要回滚 或者 是从回滚段获取来的数据

查询就是表示 consistent gets (query mode),因为查询要保证所获取的数据的时间点的一致性,所以叫一致读,即使是从当前 buffer 获得的数据,也叫 consistent gets ,这仅仅表达一种模式一种期望,并不表示真实的是从 当前buffer 获得还是从回滚段获取数据产生的 bufore image ,还有什么问题吗?

如果是 db block gets , current mode ,比如一个大的dml,当dml 开始更新一个非常大的表后,这个表更新的过程中,有一个进程去把该表末尾的一个记录更新了,然后这个大更新抵达该记录的时候会被阻塞的,若该进程事物提交,则大更新会覆盖该事务的更新,也就是说,这个大更新所看见的数据是当前的,不具有时间点的一致性,所以叫 current mode

把问题简单并粗略化:

db block gets 看作是 DML 的读取 ,其看到的数据可能不具有时间点的一致性。 consistent gets 是 select 这样的读取,无论是否来自回滚段的数据都是 consistents gets ,仅仅表示其看到的数据具有时间点上的一致性。

db block gets : 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. consistent gets : 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。

Session logical read is: The sum of "db block gets" plus "consistent gets".

db_block_changes: 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).

'consistent changes' : changes made to block for CONSISTENT READ and changes made to SORT blocks

physical reads : 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.

physical writes : 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并不是物理上发生读写的次数。

redo block size: 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.

redo entries: 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,即从内存中都数据块的次数。

做一个测试: 用下面语句取得统计信息:

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 = 11 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.

My Conclusion:

 1. db block gets :

 dml 一定会在current mode 以后去request新的块, select 有时也会因为要做order by 如果需要的PGA很高会去使用DISK 排序,从而会需要使用多余的DB BLOCK GETS.

 2. consistent gets:

 我对这个指标的理解是, 如何需要使用到内存的读取都是一致性读的范畴, 包括fetch, fetch 一次算是一次 consistent get. 一般而言consistent gets包含了对内存, 数据字典, 以及数据在缓存中的分批读取的次数.

 还有一种比较特殊的情况,

 就是X$表,或者又X$构建出的V$试图,这个表很有意思, 是一个虚表, 同一条SQL比如select * from x$ksupr, 或者select* from x$dual, 执行了一次,
第二次便不再需要consistent gets, 这我估计是由于X$表的这种虚表的特殊性质造成的。使用实表多多少少需要因为数据字典而产生consistent gets.

  而至的注意的是, 在做select * from x$ksupr connect by level <= 2;的时候db block gets不再为0(这个也很好理解).

  使用管道FUNCTION来模拟了一个table查询, 似乎也是一样的。同一句SQL第一次有很多consistent gets第2次为0.
  关于x$的这个consistent gets的特殊情况放着也许突然某个概念可以解决这个问题。

3. logical reads:
   logical reads = db block gets + consistent gets

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28612416/viewspace-775257/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-04-10

  • 博文量
    151
  • 访问量
    1507437