ITPUB??ì3
新一届的微软MVP评选已经开始,欢迎各位推荐!
ITPUB论坛 » Oracle专题深入讨论 » 关于consistent get的问题

标题: 关于consistent get的问题
离线 solearn
中级会员


精华贴数 0
个人空间 0
技术积分 915 (2047)
社区积分 16 (8517)
注册日期 2006-4-28
论坛徽章:1
2008北京奥运纪念徽章:自行车     
      

发表于 2008-7-2 14:13 
Thank Yong Huang and eagle_fan.

I did some experiments on my 9i box with full scan table in which case undo blocks should not be read. I followed eagle_fan's procedures by set events 10046 and 10200, and got a trace file. I extract the part which corresponds to 'select * from t8'.

=====================
PARSING IN CURSOR #1 len=16 dep=0 uid=39 oct=3 lid=39 tim=101212509339 hv=1430366237 ad='12ec1ebc'
select * from t8
END OF STMT
PARSE #1:c=0,e=717,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=101212509330
EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=101212528139
Consistent read started for block 6 : 01800014
  env: (scn: 0x0000.0068eba3  xid: 0x0010.01f.0000000d  uba: 0x02000016.0019.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0068eb46 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  01FEE950  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 1800014
Consistent read finished for block 6 : 1800014
FETCH #1:c=15625,e=18005,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=101212549286
Consistent read started for block 6 : 01800014
  env: (scn: 0x0000.0068eba3  xid: 0x0010.01f.0000000d  uba: 0x02000016.0019.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0068eb46 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  01FEE950  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 1800014
Consistent read finished for block 6 : 1800014
Consistent read started for block 6 : 01800015
  env: (scn: 0x0000.0068eba3  xid: 0x0010.01f.0000000d  uba: 0x02000016.0019.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0068eb46 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  01FEE950  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 1800015
Consistent read finished for block 6 : 1800015
Consistent read started for block 6 : 01800016
  env: (scn: 0x0000.0068eba3  xid: 0x0010.01f.0000000d  uba: 0x02000016.0019.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0068eb46 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  01FEE950  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 1800016
Consistent read finished for block 6 : 1800016
Consistent read started for block 6 : 01800017
  env: (scn: 0x0000.0068eba3  xid: 0x0010.01f.0000000d  uba: 0x02000016.0019.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0068eb46 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  01FEE950  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 1800017
Consistent read finished for block 6 : 1800017
Consistent read started for block 6 : 01800018
  env: (scn: 0x0000.0068eba3  xid: 0x0010.01f.0000000d  uba: 0x02000016.0019.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0068eb46 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  01FEE950  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 6 : 1800018
Consistent read finished for block 6 : 1800018
FETCH #1:c=93750,e=95620,p=0,cr=5,cu=0,mis=0,r=7,dep=0,og=4,tim=101212648544
*** SESSION ID9.33) 2008-07-02 12:07:25.000
STAT #1 id=1 cnt=8 pid=0 pos=1 obj=27749 op='TABLE ACCESS FULL T8 '


As it shows, the overall consistent get is 8 comprising 2 fetchs. The first fetch has 3 consistent gets and the second fetch has 5 consistent get.  I find that the first row always fetch 1 row but has several consistent gets(in this case, cr=3. But while I tested on a biger table, the first fetch has cr=4. I wonder what this means). After the first fetch, there are consequent fetches which scan all blocks under highwater mark even though there are no rows in them at all. I don't think the formula 'consistent gets=blocks+ceil(rownum/arraysize)' is true for all cases.

eagle_fan:
'1004 = 1000 undo apply reads + 3 segment header reads + 1 data block read (no work - consistent read gets)
'
What is '3 segment header reads' for? Does a 'select' need bitmap info? Or a table scan includes (pagetable segment header+SECOND LEVEL BITMAP BLOCK+FIRST LEVEL BITMAP BLOCK)? Is the number of this kind of cr always 3?
What if cr=4?


__________________
某年,空中旅行。观舷窗外,景色灿烂。乃感而留言。   

    扶摇上青天,凌云近日边。
    千光投下界,万色绣山川。
    居高无阴雨,御风任左前。
    层天绝尘境,云何不羡仙。

=====================
===抬起头,看看彩色的世界===
=====================
只看该作者    顶部
相关内容


CopyRight 1999-2006 itpub.net All Right Reserved.
北京皓辰广域网络信息技术有限公司. 版权所有
E-mail:Webmaster@itpub.net
京ICP证:010037号 联系我们 法律顾问