Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › open 游标+bulk Limite 1000时

69 积分	0 好友	13 主题

发消息

open 游标+bulk Limite 1000时

1^#

发表于 2012-7-6 14:41:58 | 查看: 7503| 回复: 14

请问 open cur_xxx 是一次执行游标里的SQL语句,还是分页执行呢?
cursor cur_xxx is select * from a;
open cur_xxx ;
fetch cur_detail bulk collect into xxx limit 10000;

据说 bulk collect into 能减少SQL引撑切换的次数. 是不是它bulk 把Limite 10000 带进了 SQL语句里比如 select * from a where rownum<=10000; 这样子的呢?

分享0

收藏0 回复显示全部楼层道具举报

zengmuansha

2^#

发表于 2012-7-6 17:11:59

这么说来 OPEN_CURSOR 负责解析SQL语句和生成执行计划.

会不会去执行执行计划?

是不是在第一次提取的时候才会执行执行计划?

回复显示全部楼层道具举报

zengmuansha

3^#

发表于 2012-7-6 17:26:01

还是每提取一次执行一次呢?

回复显示全部楼层道具举报

zengmuansha

4^#

发表于 2012-8-1 15:01:31

test_soruce

create table zengfankun_temp01 as select * from dba_objects;
select count(*) from zengfankun_temp01;--12,6826
analyze table zengfankun_temp01 compute statistics;

create or replace procedure test_open_cursor is
type type_owner is table of zengfankun_temp01.owner%type index by binary_integer;
type type_object_name is table of zengfankun_temp01.object_name%type index by binary_integer;
type type_object_id is table of zengfankun_temp01.object_id%type index by binary_integer;
type type_object_type is table of zengfankun_temp01.object_type%type index by binary_integer;
type type_last_ddl_time is table of zengfankun_temp01.last_ddl_time%type index by binary_integer;

l_ary_owner type_owner;
l_ary_object_name type_object_name;
l_ary_object_id type_object_id;
l_ary_object_type type_object_type;
l_ary_last_ddl_time type_last_ddl_time;

cursor cur_object is
select owner,object_name,object_id,object_type,last_ddl_time
from zengfankun_temp01
order by owner,object_name,object_type,last_ddl_time;
OPEN_START  number;
OPEN_END number;
FETCH_START number;
FETCH_END number;
begin
  DBMS_OUTPUT.ENABLE (buffer_size=>null) ;
  OPEN_START:=dbms_utility.get_time();
  open cur_object;
  OPEN_END  :=dbms_utility.get_time();
dbms_output.put_line('OPEN_TIME:'||TO_CHAR(OPEN_END-OPEN_START));
  loop
FETCH_START:=dbms_utility.get_time();
fetch  cur_object bulk collect into
   l_ary_owner,
   l_ary_object_name,
   l_ary_object_id,
   l_ary_object_type,
   l_ary_last_ddl_time
limit 10000;
FETCH_END:=dbms_utility.get_time();
dbms_output.put_line('FETCH_TIME:'||TO_CHAR(FETCH_END-FETCH_START)||' ROWCOUNT:'||cur_object%rowCount);

exit when cur_object%notfound or cur_object%notfound is null;
end loop;
end test_open_cursor;

回复显示全部楼层道具举报

zengmuansha

5^#

发表于 2012-8-1 15:01:55

test_result

OPEN_TIME:12
FETCH_TIME:21 ROWCOUNT:10000
FETCH_TIME:3 ROWCOUNT:20000
FETCH_TIME:3 ROWCOUNT:30000
FETCH_TIME:3 ROWCOUNT:40000
FETCH_TIME:3 ROWCOUNT:50000
FETCH_TIME:3 ROWCOUNT:60000
FETCH_TIME:3 ROWCOUNT:70000
FETCH_TIME:3 ROWCOUNT:80000
FETCH_TIME:3 ROWCOUNT:90000
FETCH_TIME:3 ROWCOUNT:100000
FETCH_TIME:3 ROWCOUNT:110000
FETCH_TIME:3 ROWCOUNT:120000
FETCH_TIME:1 ROWCOUNT:126826

回复显示全部楼层道具举报

zengmuansha

6^#

发表于 2012-8-1 15:04:36

结果符合你说的,可第二次,三次

当OPEN CURSOR 操作发生时， PL/SQL引擎转到SQL引擎负责PARSE SQL语句获得执行计划，同时它会记录OPEN CURSOR这一刻的SNAPSHOT SCN 快照SCN，但是Oracle并不会实际FETCH相关的数据，也不会将这些数据复制到某个地方。

直到实际FETCH 数据时才会去访问实际的数据块，这些块一般都是Current Block， The most recent version of block ，这样的块的SCN >> Snapshot scn，需要通过UNDO数据构建出一个SCN 合适的Best Block ，以满足Read Consistentcy；如果此时存在的UNDO SNAPSHOT不足以构造出这样一个很久之前的Best Block的话，那么就可能出现ORA-1555错误。

回复显示全部楼层道具举报

zengmuansha

7^#

发表于 2012-8-1 15:05:01

重复执行

OPEN_TIME:0
FETCH_TIME:18 ROWCOUNT:10000
FETCH_TIME:3 ROWCOUNT:20000
FETCH_TIME:3 ROWCOUNT:30000
FETCH_TIME:3 ROWCOUNT:40000
FETCH_TIME:3 ROWCOUNT:50000
FETCH_TIME:3 ROWCOUNT:60000
FETCH_TIME:3 ROWCOUNT:70000
FETCH_TIME:3 ROWCOUNT:80000
FETCH_TIME:3 ROWCOUNT:90000
FETCH_TIME:3 ROWCOUNT:100000
FETCH_TIME:3 ROWCOUNT:110000
FETCH_TIME:3 ROWCOUNT:120000
FETCH_TIME:2 ROWCOUNT:126826

回复显示全部楼层道具举报

zengmuansha

8^#

发表于 2012-8-1 15:08:47

重复执行结果

SQL已经处于软件解析了, 块大部分缓存到了内存当中.
因此OPEN CURSOR 时间接近0
第一次提取时间也降低了3个点.
而第N次时间并没改变.

怎么说了第N次的时间只有提取到数组所花费的时间.

很显然必有在某个地方存放结果集游标指针指向该结果集. 否则如何知道要提取10000条呢?

回复显示全部楼层道具举报

zengmuansha

9^#

发表于 2012-8-2 09:32:21

原帖由 maclean 于 2012-8-1 15:22 发表
"否则如何知道要提取10000条呢?"

这句话是什么意思？

意思是它游标如何避免提取已经提取的记录, 知道该提取应该提取的记录

回复显示全部楼层道具举报

zengmuansha

10^#

发表于 2012-8-2 09:44:17

适合局部扫描

谢谢! 会不会你的SQL代码较单纯些. 适合局部扫描?

3 单纯的open cursor+ fetch bulk collect不会在”某个地方存放结果集”

对复杂的SQL语句会不会在某个地方存放结果集呢?
按你第3条来说我那个测试每提取1万条的时间应该相似的.

回复显示全部楼层道具举报

zengmuansha

11^#

发表于 2012-8-2 10:39:13

我做了个 trace_pin

=====================
PARSING IN CURSOR #4 len=132 dep=1 uid=61 oct=3 lid=61 tim=2088173298 hv=273040489 ad='27e62380'
SELECT OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,LAST_DDL_TIME FROM T_ALL_OBJECTS ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
END OF STMT
PARSE #4:c=15625,e=79120,p=10,cr=132,cu=0,mis=1,r=0,dep=1,og=1,tim=2088173296
BINDS #4:
EXEC #4:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=2088173359
*** 2012-08-02 09:53:01.359
WAIT #3: nam='PL/SQL lock timer' ela= 10000299 duration=1000 p2=0 p3=0 obj#=10209 tim=2098173754
WAIT #4: nam='db file sequential read' ela= 8048 file#=4 block#=411 blocks=1 obj#=52544 tim=2098182123
pin ktewh25: kteinicnt dba 100019b:4 time 2098182185
pin ktewh26: kteinpscan dba 100019b:4 time 2098182223
pin ktewh27: kteinmap dba 100019b:4 time 2098182241
WAIT #4: nam='db file scattered read' ela= 280 file#=4 block#=412 blocks=5 obj#=52544 tim=2098182583
pin kdswh01: kdstgr dba 100019c:1 time 2098182615
pin kdswh01: kdstgr dba 100019d:1 time 2098182702
pin kdswh01: kdstgr dba 100019e:1 time 2098182764
pin kdswh01: kdstgr dba 100019f:1 time 2098182798
pin kdswh01: kdstgr dba 10001a0:1 time 2098182843
WAIT #4: nam='db file scattered read' ela= 425 file#=4 block#=417 blocks=8 obj#=52544 tim=2098183321
pin kdswh01: kdstgr dba 10001a1:1 time 2098183343
pin kdswh01: kdstgr dba 10001a2:1 time 2098183384
pin kdswh01: kdstgr dba 10001a3:1 time 2098183417
pin kdswh01: kdstgr dba 10001a4:1 time 2098183451
pin kdswh01: kdstgr dba 10001a5:1 time 2098183485
pin kdswh01: kdstgr dba 10001a6:1 time 2098183533
pin kdswh01: kdstgr dba 10001a7:1 time 2098183570
pin kdswh01: kdstgr dba 10001a8:1 time 2098183620
WAIT #4: nam='db file scattered read' ela= 1370 file#=4 block#=1195 blocks=32 obj#=52544 tim=2098265897
pin kdswh01: kdstgr dba 10004ab:1 time 2098265944
pin kdswh01: kdstgr dba 10004ac:1 time 2098265995
pin kdswh01: kdstgr dba 10004ad:1 time 2098266024
pin kdswh01: kdstgr dba 10004ae:1 time 2098266051
pin kdswh01: kdstgr dba 10004af:1 time 2098266079
pin kdswh01: kdstgr dba 10004b0:1 time 2098266111
pin kdswh01: kdstgr dba 10004b1:1 time 2098266140
pin kdswh01: kdstgr dba 10004b2:1 time 2098266168
pin kdswh01: kdstgr dba 10004b3:1 time 2098266196
pin kdswh01: kdstgr dba 10004b4:1 time 2098266225
pin kdswh01: kdstgr dba 10004b5:1 time 2098266254
pin kdswh01: kdstgr dba 10004b6:1 time 2098266283
pin kdswh01: kdstgr dba 10004b7:1 time 2098266321
pin kdswh01: kdstgr dba 10004b8:1 time 2098266350
pin kdswh01: kdstgr dba 10004b9:1 time 2098266378
pin kdswh01: kdstgr dba 10004ba:1 time 2098266406
pin kdswh01: kdstgr dba 10004bb:1 time 2098266433
pin kdswh01: kdstgr dba 10004bc:1 time 2098266461
pin kdswh01: kdstgr dba 10004bd:1 time 2098266491
pin kdswh01: kdstgr dba 10004be:1 time 2098266529
pin kdswh01: kdstgr dba 10004bf:1 time 2098266558
pin kdswh01: kdstgr dba 10004c0:1 time 2098266585
pin kdswh01: kdstgr dba 10004c1:1 time 2098266612
pin kdswh01: kdstgr dba 10004c2:1 time 2098266648
pin kdswh01: kdstgr dba 10004c3:1 time 2098266677
pin kdswh01: kdstgr dba 10004c4:1 time 2098266707
pin kdswh01: kdstgr dba 10004c5:1 time 2098266736
pin kdswh01: kdstgr dba 10004c6:1 time 2098266765
pin kdswh01: kdstgr dba 10004c7:1 time 2098266793
pin kdswh01: kdstgr dba 10004c8:1 time 2098266820
pin kdswh01: kdstgr dba 10004c9:1 time 2098266845
pin kdswh01: kdstgr dba 10004ca:1 time 2098266873
WAIT #4: nam='db file scattered read' ela= 244 file#=4 block#=1227 blocks=5 obj#=52544 tim=2098267164
pin kdswh01: kdstgr dba 10004cb:1 time 2098267195
pin kdswh01: kdstgr dba 10004cc:1 time 2098267246
pin kdswh01: kdstgr dba 10004cd:1 time 2098267296
pin kdswh01: kdstgr dba 10004ce:1 time 2098267348
pin kdswh01: kdstgr dba 10004cf:1 time 2098267395
FETCH #4:c=62500,e=127231,p=692,cr=696,cu=0,mis=0,r=10000,dep=1,og=1,tim=2098301132
*** 2012-08-02 09:53:11.484
WAIT #3: nam='PL/SQL lock timer' ela= 9999874 duration=1000 p2=0 p3=0 obj#=52544 tim=2108301094
FETCH #4:c=15625,e=12506,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2108314116
*** 2012-08-02 09:53:21.500
WAIT #3: nam='PL/SQL lock timer' ela= 9999983 duration=1000 p2=0 p3=0 obj#=52544 tim=2118314180
FETCH #4:c=15625,e=12784,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2118327436
*** 2012-08-02 09:53:31.515
WAIT #3: nam='PL/SQL lock timer' ela= 9999738 duration=1000 p2=0 p3=0 obj#=52544 tim=2128327259
FETCH #4:c=15625,e=12449,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2128340197
*** 2012-08-02 09:53:41.531
WAIT #3: nam='PL/SQL lock timer' ela= 10000148 duration=1000 p2=0 p3=0 obj#=52544 tim=2138340423
FETCH #4:c=15625,e=17133,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2138358015
*** 2012-08-02 09:53:51.546
WAIT #3: nam='PL/SQL lock timer' ela= 10000195 duration=1000 p2=0 p3=0 obj#=52544 tim=2148358309
FETCH #4:c=0,e=900,p=0,cr=0,cu=0,mis=0,r=315,dep=1,og=1,tim=2148359809
*** 2012-08-02 09:54:01.546
WAIT #3: nam='PL/SQL lock timer' ela= 9999756 duration=1000 p2=0 p3=0 obj#=52544 tim=2158359672
EXEC #3:c=140625,e=70266159,p=702,cr=828,cu=0,mis=0,r=1,dep=0,og=1,tim=2158360286
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=52544 tim=2158360654
WAIT #3: nam='SQL*Net message from client' ela= 4510 driver id=1111838976 #bytes=1 p3=0 obj#=52544 tim=2158365188
STAT #4 id=1 cnt=50315 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=696 pr=692 pw=0 time=108924 us)'
STAT #4 id=2 cnt=50315 pid=1 pos=1 obj=52544 op='TABLE ACCESS FULL T_ALL_OBJECTS (cr=696 pr=692 pw=0 time=209974 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=52544 tim=2158365322
*** 2012-08-02 09:54:25.375
WAIT #0: nam='SQL*Net message from client' ela= 23820886 driver id=1111838976 #bytes=1 p3=0 obj#=52544 tim=2182186228
=====================

回复显示全部楼层道具举报

zengmuansha

12^#

发表于 2012-8-2 10:40:06

核心代码每个10秒

open cur_object;
  dbms_lock.sleep(10);
  loop
fetch  cur_object bulk collect into
   l_ary_owner,
   l_ary_object_name,
   l_ary_object_id,
   l_ary_object_type,
   l_ary_last_ddl_time
limit 10000;
dbms_lock.sleep(10);
exit when cur_object%notfound or cur_object%notfound is null;
end loop;

回复显示全部楼层道具举报

zengmuansha

13^#

发表于 2012-8-2 10:44:09

dbms_lock.sleep(10) 起到了作用

*** 2012-08-02 09:53:01.359
FETCH #4:c=62500,e=127231,p=692,cr=696,cu=0,mis=0,r=10000,dep=1,og=1,tim=2098301132
*** 2012-08-02 09:53:11.484
WAIT #3: nam='PL/SQL lock timer' ela= 9999874 duration=1000 p2=0 p3=0 obj#=52544 tim=2108301094
FETCH #4:c=15625,e=12506,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2108314116
*** 2012-08-02 09:53:21.500
WAIT #3: nam='PL/SQL lock timer' ela= 9999983 duration=1000 p2=0 p3=0 obj#=52544 tim=2118314180
FETCH #4:c=15625,e=12784,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2118327436
*** 2012-08-02 09:53:31.515
WAIT #3: nam='PL/SQL lock timer' ela= 9999738 duration=1000 p2=0 p3=0 obj#=52544 tim=2128327259
FETCH #4:c=15625,e=12449,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2128340197
*** 2012-08-02 09:53:41.531
WAIT #3: nam='PL/SQL lock timer' ela= 10000148 duration=1000 p2=0 p3=0 obj#=52544 tim=2138340423
FETCH #4:c=15625,e=17133,p=0,cr=0,cu=0,mis=0,r=10000,dep=1,og=1,tim=2138358015
*** 2012-08-02 09:53:51.546
WAIT #3: nam='PL/SQL lock timer' ela= 10000195 duration=1000 p2=0 p3=0 obj#=52544 tim=2148358309
FETCH #4:c=0,e=900,p=0,cr=0,cu=0,mis=0,r=315,dep=1,og=1,tim=2148359809
*** 2012-08-02 09:54:01.546

回复显示全部楼层道具举报

zengmuansha

14^#

发表于 2012-8-2 10:50:28

全局扫描读全部块

那后的提取操作,从哪里再次获得数据块呢? 是临时表空间,还是HODL住DATABUFFER的块不让它释放? 可每个10秒的DBMS_LOCK.SLEEP(10) 会导致第一次提取的块被释放!

回复显示全部楼层道具举报

zengmuansha

15^#

发表于 2012-8-2 14:34:33

为什么在最后提取完了才给出执行计划.?

*** 2012-08-02 09:54:01.546
WAIT #3: nam='PL/SQL lock timer' ela= 9999756 duration=1000 p2=0 p3=0 obj#=52544 tim=2158359672
EXEC #3:c=140625,e=70266159,p=702,cr=828,cu=0,mis=0,r=1,dep=0,og=1,tim=2158360286
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=52544 tim=2158360654
WAIT #3: nam='SQL*Net message from client' ela= 4510 driver id=1111838976 #bytes=1 p3=0 obj#=52544 tim=2158365188
STAT #4 id=1 cnt=50315 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=696 pr=692 pw=0 time=108924 us)'
STAT #4 id=2 cnt=50315 pid=1 pos=1 obj=52544 op='TABLE ACCESS FULL T_ALL_OBJECTS (cr=696 pr=692 pw=0 time=209974 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=52544 tim=2158365322

为什么跟踪文件最后一部分才给出执行计划来呢?

回复显示全部楼层道具举报

返回列表

		自动登录	找回密码
密码			注册

open 游标+bulk Limite 1000时

test_soruce

test_result

结果符合你说的,可第二次,三次

重复执行

重复执行结果

适合局部扫描

我做了个 trace_pin

核心代码 每个10秒

dbms_lock.sleep(10) 起到了作用

全局扫描读全部块

为什么在最后 提取完了才给出执行计划.?

核心代码每个10秒

为什么在最后提取完了才给出执行计划.?