sqlplus的oci prefetch特性

Hi TOM,
  I traced some sqls with " full table scan ",and it always returns 1 row (r=1)
during first fetch .
  Is this because of "cursor prefetch" or else? Hope your advice.

For example,full table scan dual
=====================
PARSING IN CURSOR #1 len=18 dep=0 uid=62 oct=3 lid=62 tim=106633135131
hv=4035109885 ad='65fc41d8'
select * from dual
END OF STMT
PARSE #1:c=0,e=5796,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=106633135123
EXEC #1:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=106633140740
WAIT #1: nam='SQL*Net message to client' ela= 6 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=108,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=106633143298
WAIT #1: nam='SQL*Net message from client' ela= 8755 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=106633154841
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 8055707 p1=1111838976 p2=1 p3=0
=====================

For example,full table scan table T
=====================
PARSING IN CURSOR #1 len=31 dep=0 uid=62 oct=3 lid=62 tim=106641556744
hv=1251189969 ad='65fb71a8'
select * from t where rownum<25
END OF STMT
PARSE #1:c=78125,e=304217,p=13,cr=76,cu=0,mis=1,r=0,dep=0,og=4,tim=106641556737
EXEC #1:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=106641556863
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 20586 p1=9 p2=15459 p3=1
WAIT #1: nam='db file scattered read' ela= 675 p1=9 p2=15460 p3=5
FETCH #1:c=0,e=21702,p=6,cr=7,cu=0,mis=0,r=1,dep=0,og=4,tim=106641578624
WAIT #1: nam='SQL*Net message from client' ela= 463 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=71,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=106641579287
WAIT #1: nam='SQL*Net message from client' ela= 358220 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=102,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=4,tim=106641937758
WAIT #1: nam='SQL*Net message from client' ela= 4991127 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=24 pid=0 pos=1 obj=0 op='COUNT STOPKEY '
=====================

Thanks in advance.
Steven

looks like a sqlplus "ism" （http://en.wikipedia.org/wiki/-ism ）

drop table t;
create table t as select * from all_users;
alter session set events '10046 trace name context forever, level 12';
select username from t where rownum <25;

declare
    type array is table of varchar2(30) index by binary_integer;
    l_array array;
begin
    select username bulk collect into l_array from t where rownum<25;
end;
/

it is the client doing it -- sqlplus purposely did this for some reason.

=====================
PARSING IN CURSOR #5 len=39 dep=0 uid=93 oct=3 lid=93 tim=1089161938125904
hv=1020576043 ad='89965438'
select username from t where rownum <25
END OF STMT
PARSE #5:c=0,e=5127,p=5,cr=10,cu=0,mis=1,r=0,dep=0,og=1,tim=1089161938125896
BINDS #5:
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1089161938126125
WAIT #5: nam='SQL*Net message to client' ela= 3 p1=1650815232 p2=1 p3=0

FETCH #5:c=0,e=75,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1089161938126290

WAIT #5: nam='SQL*Net message from client' ela= 220 p1=1650815232 p2=1 p3=0
WAIT #5: nam='SQL*Net message to client' ela= 3 p1=1650815232 p2=1 p3=0
FETCH #5:c=0,e=76,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=1089161938126727
WAIT #5: nam='SQL*Net message from client' ela= 56435 p1=1650815232 p2=1 p3=0
WAIT #5: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
FETCH #5:c=0,e=83,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=1,tim=1089161938183401
WAIT #5: nam='SQL*Net message from client' ela= 73860 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=38 dep=1 uid=93 oct=3 lid=93 tim=1089161938268277
hv=1337640224 ad='8992bb44'
SELECT USERNAME FROM T WHERE ROWNUM<25
END OF STMT
PARSE #1:c=0,e=2415,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,tim=1089161938268270
BINDS #1:
EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1089161938268502

FETCH #1:c=0,e=113,p=0,cr=3,cu=0,mis=0,r=24,dep=1,og=1,tim=1089161938268650

EXEC #9:c=0,e=3015,p=0,cr=11,cu=0,mis=0,r=1,dep=0,og=1,tim=1089161938268746
WAIT #9: nam='SQL*Net message to client' ela= 3 p1=1650815232 p2=1 p3=0
WAIT #9: nam='SQL*Net message from client' ela= 163 p1=1650815232 p2=1 p3=0
=====================

SQLPLUS is using OCI8  May 06, 2005
Reviewer:  Anjo Kolk  from Garderen, The Netherlands

SQLPlus is using OCI8 (Oracle Call Interface) into the kernel since Oracle8.
OCI8 has this prefetch feature, so if you would rewrite this example in OCI8, it
should show the same behaviour.

Followup:
Anjo,

thanks!

http://asktom.oracle.com/pls/ask/f?p=4950:8:3341804672157758663::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:880343948514,
Array size by default is 15 in plus
10 in JDBC
2 in pro*c
1 in OCI
??? in odbc (no idea, never use it)
...
(sqlplus is JUST an oci application)
...
If you full scan a table T that has N blocks and R rows and you use an array
fetch size of A, we will typically perform the following number of consistent
gets:

N + R/A
...
A FACTOR in consistent gets is arraysize.

ARRAYSIZE does not determine consistent gets.

原文转自：http://www.ltesting.net