Re: consistent read and LIO question

From: lsllcm <lsllcm_at_gmail.com>
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:

  1. 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

Original text of this message