如何如何计算I/O

viadeazhu

库忙么？有可能是被flush出buffer cache了。
多执行几遍sql再看看

fan_zhen_hua

库不忙的。多执行几遍也还是没有。跟库忙会有关系吗》？

fan_zhen_hua

ASSM有一个低HWM和一个HWM，ORACLE会全表扫描至HWM,而对于低HWM以下的所有块会直接读取并加以处理，对于介于低HWM和HWM之间的块，必需更加小心，需要参考块所用的ASSM位图信息来查看应该读取哪些块，刚才的情况低HWM，和HWM应该是重合了。

viadeazhu

原帖由 fan_zhen_hua 于 2009-10-29 10:53 发表
ASSM有一个低HWM和一个HWM，ORACLE会全表扫描至HWM,而对于低HWM以下的所有块会直接读取并加以处理，对于介于低HWM和HWM之间的块，必需更加小心，需要参考块所用的ASSM位图信息来查看应该读取哪些块，刚才的情况低HWM，和HWM应该是重合了。

低hwm即formatted blocks的hwm，前面我脚本中显示出来了低hwm的位置：
##################################################
The segment space usage for TABLE "ASSMTEST"
##################################################
UNUSED BLOCKS...........................0
UNUSED Bytes............................0
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------

因为没有unformatted blocks，所以高低hwm重合

fan_zhen_hua

恩。非常感谢~

Yong Huang

I want to make a few comments.

* x$bh.obj is xxx_objects.data_object_id, not object_id. They differ in various cases, such as table move, truncate, index rebuild, partition exchange, etc. (But may or may not change for an empty table.)

* Physically, the order of the first few blocks of a table in ASSM is: first level BMB (bitmap block), second level BMB, segment header, data... That is, the segment header should be after, not before, the bitmap blocks. In addition, each extent (other than extent 0) has its own first level BMB in the first block.

* I don't think you can bring reads of the segment header into the count of consistent gets, because reading segment headers should count as current mode gets. I know since 9iR2 the simplest table read will show 0 db block gets. See Tom Kyte's listing of the four versions at "Followup   April 26, 2005 - 8am Central time zone" at

http://asktom.oracle.com/pls/ask ... ION_ID:865586003021

The question and answer right below are relevant. But I'm not sure Tom really answered the question. We can see from viadeazhu's test in message #7 that the segment header (class 4) was indeed read once. Why does autotrace "db block gets" still report 0? Could SQL trace incorrectly add it to "consistent gets" (which makes message #7 an excellent explanation)? If anybody finds an official explanation, I'd like to see.

Where's the URL for "对于大表，晶晶小妹有实验证明会读segment header3次"?

Yong Huang

viadeazhu

我估计sql trace的确是把读segment header算成consistents read了。

晶晶小妹的实验：
http://www.itpub.net/thread-951696-1-1.html

引用其中一句，看来我们都是用实验推理论，没有official的理论支持：
“全表扫描，段头会被访问3次，这点，我就不详细写的证明过程了，因为不是很严谨，只能说是推测”

fan_zhen_hua

Yong Huang 贴的asktom 上的文章，对此有一些解释。
It is zero because they optimized away the need to do it.


原文如下:
Hi Tom,

I tried the same example (9204 on linux RHEL4/Dell 4600 Box ), and every time it does FTS then how
come "db block gets" can be 0 ? also why "consistent gets" got decreased after first execution ?
why recursive call got decreased from 173 to 0 ?


SQL> set autotrace on
SQL> select * from t1;

D
-
X


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




Statistics
----------------------------------------------------------
        173  recursive calls
          0  db block gets
         22  consistent gets
          2  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

D
-
X


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




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        375  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





Followup   April 26, 2005 - 8am Central time zone:

the recursive sql was from the first "hard parse", brand new query -- never seen before, we ran
some sql in order to figure out how to run your sql.

That accounts for the extra consistent gets too -- that sql that was run for you by us, it counted
in the consistent gets.


The number of db block gets and consistent gets varies by release.


scott.dept on 817:
          4  db block gets
          2  consistent gets

scott.dept on 9ir1
          2  db block gets
          2  consistent gets


scott.dept on 9ir2
          0  db block gets
          4  consistent gets

scott.dept on 10gr1
          0  db block gets
          4  consistent gets



things change over time -- one of the reasons I show how to measure things here.   


   April 26, 2005 - 8am Central time zone Bookmark | Bottom | TopReviewer: Kapil Goyal from INDIA
<quote>
That indicates that we did 4 current mode reads to determine how to FULL SCAN dual.
</quote>
so in 9iR2, 10g what mechanism Oracle follows ? NO "current mode reads" kind of concept ? or some
great enhancement ?? i am sure you must have digged upto that much depth
Can you elaborate a bit more why it is ZERO in 9iR2 and 10g.



Followup   April 26, 2005 - 9am Central time zone:

that is a good enhancement since current mode reads are "more expensive" than the other kinds yes.

This was simply an incremental improvement over time.

It is zero because they optimized away the need to do it.

fan_zhen_hua

那什么时候读段头算consistents read,什么时候算db block gets。

Yong Huang

原帖由 fan_zhen_hua 于 2009-10-29 21:08 发表
那什么时候读段头算consistents read,什么时候算db block gets。

I don't know. That's what we should look for on Metalink or any online source. One thing for sure is that if you run DML, db block gets are still non-zero.

Yong Huang