sqlplus的oci prefetch特性

核心提示： =Thanks in advance.Stevenlooks like a sqlplus "ism" （http://en.wikipedia.org/wiki/-ism ）drop table t;create table t as select * from al

=====================

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.