逻辑读，当前读，一致读，和物理读

BTxigua

对内，共匪什么都要，就是不要脸；对外，共匪什么都不要，就是要脸。

Yong Huang

原帖由 bottawa 于 2010-8-3 19:52 发表
In  Chapter 2, When Jonathan explains v$segstat, the result of the statistics shows more physical reads than logical reads. His explanation is 1) the logical reads statistic is sampled, especially since his use of the object had been a little extreme.  or 2) the sample clause used by the dbms_stats when estimating statistics on a table can result in a variant of the tablescan run-time mechanism that literally does report more physical I/Os than logical I/Os.

Thanks. But that only applies to the numbers in the view v$segstat or v$segment_statistics, not anywhere else.

According to v$segstat_name and further "Bug" 3771746, logical reads in those two views are sampled. I just checked 11gR2. They still are.

Yong Huang

红叶DBA

关于一致性读（consistent gets）的问题，我前些天还上网看了：

consistent gets：一致性读，为了保持读一致性而获取的块，其中可能包括undo block，也有包括非undo block，而非undo block有有可能包括 buffer cache block ，也有可能包括 disk block。
consistent gets可以简单的理解为，最终的结果集，所获取的块数。

db block gets：其实这个名字可能有点歧义，这个表示 no consistent gets，即非一致性读，
也就是说，一条查询语句发出后，所读的块中，除了consistent gets，就是db block gets，
所以才会有：consistent gets + db block gets = Logical IO 的说法。

physical reads：这个就更好理解了，物理读，就是从磁盘中读取出的 block 数目，这个参数大家的理解都没什么问题。
所以： physical reads =physical IO。

Yong Huang

> physical reads：这个就更好理解了，物理读，就是从磁盘中读取出的 block 数目，这个参数大家的理解都没什么问题。
> 所以： physical reads =physical IO。

One complication with physical reads is that you can't say it's "读取出的 block 数目" in all cases. In most cases, it's the number of blocks that are read, not number of times of physical read; they differ when there's multiblock read. See
http://yong321.freeshell.org/computer/diskreadunit.txt However, the plain English of "physical reads" should really mean number of times of physical read, which is the meaning v$filestat and v$tempstat or non-Oracle tools such as iostat correctly use.

Yong Huang

[ 本帖最后由 Yong Huang 于 2010-8-29 17:42 编辑 ]

aktt12

原帖由 Yong Huang 于 2010-8-27 02:29 发表
> physical reads：这个就更好理解了，物理读，就是从磁盘中读取出的 block 数目，这个参数大家的理解都没什么问题。
> 所以： physical reads =physical IO。

One complication with physical reads is that you can't say it's "读取出的 block 数目" in all cases. In most cases, it's actually the number of times of read, not blocks that have been read, unless you guarantee there's no multiblock read. See
http://yong321.freeshell.org/computer/diskreadunit.txt

Yong Huang

我非常想了解下您引经据典的办法。有很多知识在文档上见过，回头会找不到在哪见过，事后想翻阅的事后常常没有方向。

想请教下您的经验，是怎么做到的？是靠勤做笔记吗？

Yong Huang

原帖由 aktt12 于 2010-8-28 06:15 发表

我非常想了解下您引经据典的办法。有很多知识在文档上见过，回头会找不到在哪见过，事后想翻阅的事后常常没有方向。

想请教下您的经验，是怎么做到的？是靠勤做笔记吗？

Yes, I make notes a lot, all electronically. I save important messages in a text or html file on my local hard drive, occasionally save a PDF file. If I have some thoughts, I either write and save it to a file locally, or if it's mature enough I post it to my own web site. Both are searchable. Locally, I use Google Desktop Search. In the past I tried Copernic and Microsoft Desktop Search. All have their own problems. Without desktop search, my study can't be that efficient.

However, nothing beats a human brain. Some of the things are too difficult to search because there's no clear keyword to use. You just have to remember them.

Yong Huang

熊猫儿

学习了，Yong版主有耐心回答的也很好啊，3Q。。。

红叶DBA

原帖由 Yong Huang 于 2010-8-27 02:29 发表
> physical reads：这个就更好理解了，物理读，就是从磁盘中读取出的 block 数目，这个参数大家的理解都没什么问题。
> 所以： physical reads =physical IO。

One complication with physical reads is that you can't say it's "读取出的 block 数目" in all cases. In most cases, it's actually the number of times of read, not blocks that have been read, unless you guarantee there's no multiblock read. See
http://yong321.freeshell.org/computer/diskreadunit.txt

Yong Huang

I don't think so. Here is the statement from the goverment document:

physical reads:
Total number of data blocks read from disk. This value can be greater than the value of "physical reads direct" plus "physical reads cache" as reads into process private buffers also included in this statistic.

So  I think "physical reads" is the number of blocks, not the read time of the blocks.

Yong Huang

原帖由 红叶DBA 于 2010-8-29 09:15 发表

I don't think so. Here is the statement from the goverment document:

physical reads:
Total number of data blocks read from disk. This value can be greater than the value of "physical reads direct" plus "physical reads cache" as reads into process private buffers also included in this statistic.

So  I think "physical reads" is the number of blocks, not the read time of the blocks.

I made a mistake in my last message (#14). My note
http://yong321.freeshell.org/computer/diskreadunit.txt
really proves that in the case of v$sql(area) and tkprof, "physical reads" refers to number of blocks, not number of times of read. I simply remembered backward. I made the change in my msg #14. Please read it.

The statistic "physical reads" is clearly defined in Reference manual as number of blocks, so people won't blame Oracle, except for its misleading use of the English phrase.

Yong Huang

[ 本帖最后由 Yong Huang 于 2010-8-29 17:45 编辑 ]

sundog315

原帖由 Yong Huang 于 2010-8-29 03:43 发表


Yes, I make notes a lot, all electronically. I save important messages in a text or html file on my local hard drive, occasionally save a PDF file. If I have some thoughts, I either write and save it to a file locally, or if it's mature enough I post it to my own web site. Both are searchable. Locally, I use Google Desktop Search. In the past I tried Copernic and Microsoft Desktop Search. All have their own problems. Without desktop search, my study can't be that efficient.

However, nothing beats a human brain. Some of the things are too difficult to search because there's no clear keyword to use. You just have to remember them.

Yong Huang

有没有好的文档管理软件推荐？