 |
|
| Computers Forum Index » Computer - Databases - Oracle (Server) » SELECT on ASSM tablespace... |
|
Page 1 of 1 |
|
| Author |
Message |
| lsllcm... |
 Posted: Tue Sep 08, 2009 1:38 am |
|
|
|
Guest
|
Hi All,
I do one test on consistent read about SELECT on ASSM tablespace
--SELECT FROM TABLE in ASSM tablespace
from 10046 trace file, there is no read operation to read PAGETABLE
SEGMENT HEADER/Second LEVEL BITMAP BLOCK/FIRST LEVEL BITMAP BLOCK
Is that right?
Thanks
Jacky |
|
|
| Back to top |
|
|
|
| ddf... |
| Posted: Tue Sep 08, 2009 2:40 pm |
|
|
|
Guest
|
On Sep 7, 8:38 pm, lsllcm <lsl... at (no spam) gmail.com> wrote:
Quote: Hi All,
I do one test on consistent read about SELECT on ASSM tablespace
--SELECT FROM TABLE in ASSM tablespace
from 10046 trace file, there is no read operation to read PAGETABLE
SEGMENT HEADER/Second LEVEL BITMAP BLOCK/FIRST LEVEL BITMAP BLOCK
Is that right?
Thanks
Jacky
Yes, which does not mean those events don't occur. Event 10046 does
not, to my knowledge, report them.
David Fitzjarrell |
|
|
| Back to top |
|
|
|
| lsllcm... |
| Posted: Wed Sep 09, 2009 2:45 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Fri Mar 19, 2010 1:52 pm
|
|