Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!x6g2000prc.googlegroups.com!not-for-mail From: lsllcm Newsgroups: comp.databases.oracle.server Subject: Re: SELECT on ASSM tablespace Date: Wed, 9 Sep 2009 07:45:00 -0700 (PDT) Organization: http://groups.google.com Lines: 195 Message-ID: References: <3e167bbe-0d3a-4c1f-a537-c58cb1c3975b@v37g2000prg.googlegroups.com> <4d1ae136-fc3a-4212-aa96-0e8ca5ecac63@o9g2000yqj.googlegroups.com> NNTP-Posting-Host: 58.60.96.138 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Trace: posting.google.com 1252507500 27489 127.0.0.1 (9 Sep 2009 14:45:00 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 9 Sep 2009 14:45:00 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: x6g2000prc.googlegroups.com; posting-host=58.60.96.138; posting-account=RPKoowoAAABSPFVZDrpQ4kMUfITNOVaz User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30618),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl Hi David, Thanks for your feedback. I did one test 1. set up data drop table tt purge; create table tt ( tt_id number, tt_name varchar2(2000) ) tablespace jackytbs; insert into tt values (1, 'TT1'); COMMIT; 2. enable 10200/10202/10046 events, and execute "select * from tt" ALTER SESSION SET EVENTS '10200 trace name context forever, level 1'; ALTER SESSION SET EVENTS '10202 trace name context forever, level 1'; ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' ; Below is the trace file. PARSING IN CURSOR #6 len=18 dep=0 uid=83 oct=3 lid=83 tim=1351072469 hv=3446379526 ad='2203e528' sqlid='bt748vg6qr506' select * from tt END OF STMT PARSE #6:c=15600,e=49306,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=1351072462 BINDS #6: EXEC #6:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1351072797 WAIT #6: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=62996 tim=1351072884 WAIT #6: nam='SQL*Net message from client' ela= 610 driver id=1413697536 #bytes=1 p3=0 obj#=62996 tim=1351073620 ktrget2(): started for block <0x0005 : 0x0140006c> objd: 0x00011a50 env: (scn: 0x0000.024d9104 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 33sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.024d90c3) ktrexf(): returning 9 on: 059864D0 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0 Consistent read complete... Block header dump: 0x0140006c Object id on Block? Y seg/obj: 0x11a50 csc: 0x00.24d90b3 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1400069 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/ Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ktrgcm(): completed for block <0x0005 : 0x0140006c> objd: 0x00011a50 ktrget2(): completed for block <0x0005 : 0x0140006c> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x0140006d> objd: 0x00011a50 env: (scn: 0x0000.024d9104 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 32sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.024d90c3) ktrexf(): returning 9 on: 059864D0 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0 Consistent read complete... Block header dump: 0x0140006d Object id on Block? Y seg/obj: 0x11a50 csc: 0x00.24d90b3 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1400069 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/ Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ktrgcm(): completed for block <0x0005 : 0x0140006d> objd: 0x00011a50 ktrget2(): completed for block <0x0005 : 0x0140006d> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x0140006e> objd: 0x00011a50 env: (scn: 0x0000.024d9104 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 32sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.024d90c3) ktrexf(): returning 9 on: 059864D0 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0 Consistent read complete... Block header dump: 0x0140006e Object id on Block? Y seg/obj: 0x11a50 csc: 0x00.24d90b3 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1400069 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/ Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ktrgcm(): completed for block <0x0005 : 0x0140006e> objd: 0x00011a50 ktrget2(): completed for block <0x0005 : 0x0140006e> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x0140006f> objd: 0x00011a50 env: (scn: 0x0000.024d9104 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 32sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.024d90c3) ktrexf(): returning 9 on: 059864D0 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0 Consistent read complete... Block header dump: 0x0140006f Object id on Block? Y seg/obj: 0x11a50 csc: 0x00.24d90b3 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1400069 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/ Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ktrgcm(): completed for block <0x0005 : 0x0140006f> objd: 0x00011a50 ktrget2(): completed for block <0x0005 : 0x0140006f> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x01400070> objd: 0x00011a50 env: (scn: 0x0000.024d9104 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 32sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.024d90c3) ktrexf(): returning 9 on: 059864D0 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0 Consistent read complete... Block header dump: 0x01400070 Object id on Block? Y seg/obj: 0x11a50 csc: 0x00.24d90b3 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1400069 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/ Fsc 0x01 0x0009.009.00004467 0x00c00014.057f.05 --U- 1 fsc 0x0000.024d90b4 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ktrgcm(): completed for block <0x0005 : 0x01400070> objd: 0x00011a50 ktrget2(): completed for block <0x0005 : 0x01400070> objd: 0x00011a50 WAIT #6: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=62996 tim=1351077041 FETCH #6:c=15600,e=3397,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=1351077117 STAT #6 id=1 cnt=1 pid=0 pos=1 obj=72272 op='TABLE ACCESS FULL TT (cr=7 pr=0 pw=0 time=0 us cost=3 size=1015 card=1)' WAIT #6: nam='SQL*Net message from client' ela= 62079 driver id=1413697536 #bytes=1 p3=0 obj#=62996 tim=1351139377 ===================== 3. from the trace file. FETCH #6:c=15600,e=3397,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=1351077117 --total consistent read is 7 ktrget2(): started for block <0x0005 : 0x0140006c> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x0140006d> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x0140006e> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x0140006f> objd: 0x00011a50 ktrget2(): started for block <0x0005 : 0x01400070> objd: 0x00011a50 --there are 5 reads for data. 0x0140006c - 0x140006f have no data. But db still has consistent reads on them. 4. dump FIRST LEVEL BITMAP BLOCK Freeness Status: nf1 0 nf2 0 nf3 0 nf4 5 -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x01400069 Length: 8 Offset: 0 0:Metadata 1:Metadata 2:Metadata 3:75-100% free 4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free 5. question Where is other two consistent reads? Does consistent read include PAGETABLE SEGMENT HEADER/Second LEVEL BITMAP BLOCK/FIRST LEVEL BITMAP BLOCK ? Thanks Jacky