Re: SELECT on ASSM tablespace

From: lsllcm <lsllcm_at_gmail.com>
Date: Wed, 9 Sep 2009 07:45:00 -0700 (PDT)
Message-ID: <d81fc150-7c83-40b7-84aa-524478be722f_at_x6g2000prc.googlegroups.com>

Hi David,

Thanks for your feedback.

I did one test

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;
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/

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:

 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/

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:

 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/

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:

 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 Received on Wed Sep 09 2009 - 09:45:00 CDT

This message: [ Message body ]
Next message: joel garry: "Re: Ora-19906 when restoring user managed backup - stumped!"
Previous message: codefragment_at_googlemail.com: "Re: Temporary table use and the redo log"
Maybe in reply to: lsllcm: "SELECT on ASSM tablespace"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message