Re: consistent read and LIO question

From: lsllcm <lsllcm_at_gmail.com>
Date: Thu, 14 May 2009 18:13:23 -0700 (PDT)
Message-ID: <193c4637-ee04-46ed-bc24-ac3435ab8881_at_y34g2000prb.googlegroups.com>



On May 12, 11:19 pm, lsllcm <lsl..._at_gmail.com> wrote:
> 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=121301249812­3140
> 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

After read the article
http://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/, there is one statement

-----------------------------------begin-------------------------
*** In other session, run the following (where SID = the sid of the other session) before and after an execution of the select statement in the other session.
select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 123 and n.name like 'consistent %';
-----------------------------------end-------------------------
I can get exact same consistent reads from v$sesstat. Received on Thu May 14 2009 - 20:13:23 CDT

Original text of this message