consistent read and LIO question

From: lsllcm <lsllcm_at_gmail.com>
Date: Sat, 18 Apr 2009 09:24:05 -0700 (PDT)
Message-ID: <7df01c0c-1424-4163-9e00-4696516ad7be_at_u39g2000pru.googlegroups.com>

Hi Friends,

I have one quesiton about consistent read and LIO. Are they same? But from my test, they are different.

Below are my test case

SELECT * FROM test_case1 WHERE id = 1000 ;

There is primary key on ID column.

Cost = 1 root + 1 branch + 1 leaf + 1 table = 4 LIOs

But from 10046 event, the consistent read is 5, and the physical read is 4. Where is 5th read from?

---

PARSING IN CURSOR #4 len=41 dep=0 uid=81 oct=3 lid=81 tim=3213230332 hv=1968510033 ad='43c9dbac' sqlid='7t38hd5upa52j' SELECT * FROM test_case1 WHERE id = 1000 END OF STMT
PARSE
#4:c=31200,e=110490,p=4,cr=76,cu=0,mis=1,r=0,dep=0,og=1,tim=3213230328
BINDS #4:
EXEC #4:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3213230494 WAIT #4: nam='SQL*Net message to client' ela= 4 driver id=1111838976
#bytes=1 p3=0 obj#=12446 tim=3213230543

WAIT #4: nam='db file sequential read' ela= 27820 file#=5 block#=45196 blocks=1 obj#=69573 tim=3213317184
WAIT #4: nam='db file sequential read' ela= 9701 file#=6 block#=32964 blocks=1 obj#=69573 tim=3213382978
WAIT #4: nam='db file sequential read' ela= 381 file#=5 block#=45205 blocks=1 obj#=69573 tim=3213383480
WAIT #4: nam='db file sequential read' ela= 7706 file#=5 block#=95 blocks=1 obj#=69572 tim=3213391283
FETCH
#4:c=0,e=160746,p=4,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=3213391341
WAIT #4: nam='SQL*Net message from client' ela= 499 driver id=1111838976 #bytes=1 p3=0 obj#=69572 tim=3213391923 FETCH #4:c=0,e=35,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=3213392026 STAT #4 id=1 cnt=1 pid=0 pos=1 obj=69572 op='TABLE ACCESS BY INDEX ROWID TEST_CASE1 (cr=5 pr=4 pw=4 time=0 us cost=2 size=533 card=1)' STAT #4 id=2 cnt=1 pid=1 pos=1 obj=69573 op='INDEX RANGE SCAN TEST_CASE1_IDX (cr=4 pr=3 pw=3 time=0 us cost=2 size=0 card=1)' WAIT #4: nam='SQL*Net message to client' ela= 38 driver id=1111838976
#bytes=1 p3=0 obj#=69572 tim=3213392219

Thanks
Jacky Received on Sat Apr 18 2009 - 11:24:05 CDT