sqlplus的oci prefetch特性

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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.

right"(出处:清风软件下载学院)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航