Re: consistent read and LIO question
Date: Tue, 12 May 2009 08:19:49 -0700 (PDT)
Message-ID: <b3846439-dd46-47a5-a42f-809b8b94a814_at_j9g2000prh.googlegroups.com>
Hi All,
Thanks for your information:
I have one test about consistent read on index. The consistent gets are not different from different tools.
In v$sysstat and v$statname, the consistent get is 3
In execution plan, consistent reads is: 5 consistent gets
In 10200 and 10202 events, the consistent is also 5.
one index brach block - 1 consistent read
one index leaf block - 2 consistent read (The leaf block is read two
times)
one tran data block - 2 consistent read (The tran data block is read
two times)
The question is that index leaf block already contains rows in one data block. Why does oracle read the same data block two times? Why are the results from different tools different for consistent reads?
Below are test case:
- create table t1(c1 int, c2 int, c3 char(100));
truncate table t1;
drop index t1_n1 ;
drop index t1_n2 ;
2. insert values
insert into t1 values (1, '1111', 'xxx'); insert into t1 values (1, '2222', 'xxx'); insert into t1 values (1, '333', 'xxx'); insert into t1 values (1, '4444', 'xxx');
3. insert other values
insert into t1
select level, dbms_random.random, 'x'
from dual connect by level <= 10000;
4. create index
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);
5. execute one query
select * from t1 where c1 = 1;
6. from execution plan, consistent reads is: 5 consistent gets
7. from 10200 and 10202 events, the consistent is also 5.
one index brach block - 1 consistent read
one index leaf block - 2 consistent read (The leaf block is read two
times)
one tran data block - 2 consistent read (The tran data block is read
two times)
8 from v$sysstat and v$statname, the consistent get is 3 (before.)STAT..consistent
gets 94651317 (after...)STAT..consistent gets 94651320
Below are trace file of 10200/10202 and 10046 events:
PARSING IN CURSOR #2 len=29 dep=0 uid=5 oct=3 lid=5 tim=1213012498123148 hv=3634861691 ad='688ae80c' select * from t1 where c1 = 1
END OF STMT
PARSE
#2:c=159975,e=155643,p=28,cr=251,cu=0,mis=1,r=0,dep=0,og=2,tim=1213012498123140
BINDS #2:
EXEC
#2:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=1213012498123340
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=1213012498123398
Consistent read started for block 0 : 0040fdda env: (scn: 0x0000.0044dbd5 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 8sch: scn: 0x0000.00000000) WAIT #2: nam='db file sequential read' ela= 17 file#=1 block#=64986 blocks=1 obj#=54818 tim=1213012498123599 CR exa ret 2 on: 0x20009cdc scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0
Consistent read finished for block 0 : 40fdda Consistent read started for block 0 : 0040fddb env: (scn: 0x0000.0044dbd5 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 8sch: scn: 0x0000.00000000) WAIT #2: nam='db file sequential read' ela= 16 file#=1 block#=64987 blocks=1 obj#=54818 tim=1213012498123812 Consistent read complete...
Block header dump: 0x0040fddb
Object id on Block? Y
seg/obj: 0xd622 csc: 0x00.44db36 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0044db36
Consistent read finished for block 0 : 40fddb
Consistent read finished for block 0 : 40fddb
Consistent read started for block 0 : 0040fa5a
env: (scn: 0x0000.0044dbd5 xid: 0x0000.000.00000000 uba:
0x00000000.0000.00 statement num=0 parent xid: xid:
0x0000.000.00000000 scn: 0x0000.00000000 8sch: scn: 0x0000.00000000)
WAIT #2: nam='db file sequential read' ela= 15 file#=1 block#=64090
blocks=1 obj#=54806 tim=1213012498124189
Consistent read complete...
Block header dump: 0x0040fa5a
Object id on Block? Y
seg/obj: 0xd621 csc: 0x00.44daca itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.02e.00000af4 0x008000e0.02a6.0e --U- 62 fsc 0x0000.0044db31
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Consistent read finished for block 0 : 40fa5a
Consistent read finished for block 0 : 40fa5a
FETCH
#2:c=1000,e=1010,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=2,tim=1213012498124467
WAIT #2: nam='SQL*Net message from client' ela= 869 driver
id=1413697536 #bytes=1 p3=0 obj#=54806 tim=1213012498125413
Consistent read started for block 0 : 0040fddb
env: (scn: 0x0000.0044dbd5 xid: 0x0000.000.00000000 uba:
0x00000000.0000.00 statement num=0 parent xid: xid:
0x0000.000.00000000 scn: 0x0000.00000000 8sch: scn: 0x0000.00000000)
CR exa ret 9 on: 0x20009cdc scn: 0xffff.ffffffff xid:
0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff
sfl: 0
Consistent read complete...
Block header dump: 0x0040fddb
Object id on Block? Y
seg/obj: 0xd622 csc: 0x00.44db36 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0044db36
Consistent read finished for block 0 : 40fddb
Consistent read finished for block 0 : 40fddb
Consistent read started for block 0 : 0040fa5a
env: (scn: 0x0000.0044dbd5 xid: 0x0000.000.00000000 uba:
0x00000000.0000.00 statement num=0 parent xid: xid:
0x0000.000.00000000 scn: 0x0000.00000000 8sch: scn: 0x0000.00000000)
CR exa ret 9 on: 0x20009cdc scn: 0xffff.ffffffff xid:
0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff
sfl: 0
Consistent read complete...
Block header dump: 0x0040fa5a
Object id on Block? Y
seg/obj: 0xd621 csc: 0x00.44daca itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.02e.00000af4 0x008000e0.02a6.0e --U- 62 fsc 0x0000.0044db31
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Consistent read finished for block 0 : 40fa5a
Consistent read finished for block 0 : 40fa5a
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=54806 tim=1213012498126224
FETCH
#2:c=1000,e=808,p=0,cr=2,cu=0,mis=0,r=4,dep=0,og=2,tim=1213012498126271
Thanks
Jacky
Received on Tue May 12 2009 - 10:19:49 CDT