分享
 
 
 

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"(出处:清风软件下载学院)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有