本文探讨了 MySQL 中 OPEN CURSOR 和 BULK COLLECT 操作的原理与性能。通过示例，解释了 OPEN CURSOR 仅解析 SQL 获取执行计划，而不会立即执行。首次 FETCH 时才执行数据访问，并分析了多次 FETCH 的时间变化。实验表明，数据读取发生在实际 FETCH 时，且每次 FETCH 才会访问数据块。
有同学在T.askmaclean.com上发帖关于bulk collect与open cursor的问题， 帖子的地址在这里。 他的疑问在于:
这么说来 OPEN_CURSOR 负责解析SQL语句 和生成执行计划.
会不会去执行 执行计划?
是不是在第一次提取的时候才会执行 执行计划?
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;
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
重复执行
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
SQL已经处于软件解析了, 块大部分缓存到了内存当中.
因此OPEN CURSOR 时间接近0
第一次提取时间也降低了3个点.
而第N次 时间并没改变.
怎么说了 第N次 的时间只有提取到数组 所花费的时间.
很显然必有在某个地方存放结果集 游标指针指向该结果集. 否则如何知道要提取10000条呢?
实际我没有完全理解这位同学想表达的完全clear的观点， 我想着重和重复说明的是：
当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错误。
为了证明我的观点， 我会创建一个环境测试，这个环境会利用一张小表但是有这char(2000)这样的列， 这导致一条记录将占用一个数据块，我会使用bulk collect fetch一次fetch 10 条记录，如果实验理想那么OPEN CURSOR时将只完成PARSE解析SQL和开始执行的操作， 之后当每需要完成一次fetch bulk collect一次都需要去逻辑读取10个数据块，通过"_trace_pin_time"可以捕获Server Process去pin CR block的行为，换句话说可以看到一次Fetch Bulk Collect limit 10触发10个buffer被pin。
[oracle@nas ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 1 11:36:52 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
http://www.askmaclean.com
SQL> create table maclean (t1 char(2000)) tablespace users pctfree 99;
Table created.
SQL> begin
2 for i in 1..200 loop
3 insert into maclean values('MACLEAN');
4 commit ;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','MACLEAN');
PL/SQL procedure successfully completed.
SQL> select count(*) from maclean;
COUNT(*)
----------
200
SQL> select blocks,num_rows from dba_tables where table_name='MACLEAN';
BLOCKS NUM_ROWS
---------- ----------
244 200
SQL> alter system set "_trace_pin_time"=1 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2232472 bytes
Variable Size 1795166056 bytes
Database Buffers 1325400064 bytes
Redo Buffers 17227776 bytes
Database mounted.
Database opened.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL>
SQL>
SQL> declare
2 cursor v_cursor is
3 select * from sys.maclean;
4 type v_type is table of sys.maclean%rowtype index by binary_integer;
5 rec_tab v_type;
6 begin
7 open v_cursor;
8 dbms_lock.sleep(30);
9 loop
10 fetch v_cursor bulk collect
11 into rec_tab limit 10;
12 dbms_lock.sleep(10);
13 exit when v_cursor%notfound;
14 end loop;
15 end;
16 /
看一下它的10046 trace+ pin trace:
PARSING IN CURSOR #47499559136872 len=337 dep=0 uid=0 oct=47 lid=0 tim=1343836146412056 hv=496860239 ad='11a11dbb0' sqlid='4zh7954ftuz2g'
declare
cursor v_cursor is
select * from sys.maclean;
type v_type is table of sys.maclean%rowtype index by binary_integer;
rec_tab v_type;
begin
open v_cursor;
dbms_lock.sleep(30);
loop
fetch v_cursor bulk collect
into rec_tab limit 10;
dbms_lock.sleep(10);
exit when v_cursor%notfound;
end loop;
end;
END OF STMT
PARSE #47499559136872:c=0,e=346,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1343836146412051
=====================
PARSING IN CURSOR #47499559126280 len=25 dep=1 uid=0 oct=3 lid=0 tim=1343836146414939 hv=3296884535 ad='11a11d250' sqlid='2mb1493284xtr'
SELECT * FROM SYS.MACLEAN
END OF STMT
PARSE #47499559126280:c=1999,e=2427,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=2568761675,tim=1343836146414937
EXEC #47499559126280:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2568761675,tim=1343836146415104
上面完成了 对 SELECT * FROM SYS.MACLEAN的 PARSE 并开始执行 ， 但是没有FETCH任何记录也没有pin 逻辑读任何数据块， 这说明了OPEN CURSOR操作的本质
*** 2012-08-01 11:49:36.424
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 30009361 duration=0 p2=0 p3=0 obj#=-1 tim=1343836176424782
等待了30s
pin ktewh26: kteinpscan dba 0x10a6202:4 time 1039048805
pin ktewh27: kteinmap dba 0x10a6202:4 time 1039048847
pin kdswh11: kdst_fetch dba 0x10a6203:1 time 1039048898
pin kdswh11: kdst_fetch dba 0x10a6204:1 time 1039048961
pin kdswh11: kdst_fetch dba 0x10a6205:1 time 1039049004
pin kdswh11: kdst_fetch dba 0x10a6206:1 time 1039049042
pin kdswh11: kdst_fetch dba 0x10a6207:1 time 1039049089
pin kdswh11: kdst_fetch dba 0x10a6208:1 time 1039049123
pin kdswh11: kdst_fetch dba 0x10a6209:1 time 1039049159
pin kdswh11: kdst_fetch dba 0x10a620a:1 time 1039049191
pin kdswh11: kdst_fetch dba 0x10a620b:1 time 1039049225
pin kdswh11: kdst_fetch dba 0x10a620c:1 time 1039049260
kdst_fetch是实际fetch块中记录的函数 ， 这里fetch了10个块
完成一次实际的FETCH
FETCH #47499559126280:c=0,e=536,p=0,cr=12,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836176425542
*** 2012-08-01 11:49:46.428
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002694 duration=0 p2=0 p3=0 obj#=-1 tim=134383618642829
再次休眠10s
pin kdswh11: kdst_fetch dba 0x10a620d:1 time 1049052211
pin kdswh11: kdst_fetch dba 0x10a620e:1 time 1049052264
pin kdswh11: kdst_fetch dba 0x10a620f:1 time 1049052299
pin kdswh11: kdst_fetch dba 0x10a6211:1 time 1049052332
pin kdswh11: kdst_fetch dba 0x10a6212:1 time 1049052364
pin kdswh11: kdst_fetch dba 0x10a6213:1 time 1049052398
pin kdswh11: kdst_fetch dba 0x10a6214:1 time 1049052430
pin kdswh11: kdst_fetch dba 0x10a6215:1 time 1049052462
pin kdswh11: kdst_fetch dba 0x10a6216:1 time 1049052494
pin kdswh11: kdst_fetch dba 0x10a6217:1 time 1049052525
FETCH #47499559126280:c=0,e=371,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836186428807
接着pin 10个数据块， 并实际fetch 一次
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002864 duration=0 p2=0 p3=0 obj#=-1 tim=1343836196431754
pin kdswh11: kdst_fetch dba 0x10a6218:1 time 1059055662
pin kdswh11: kdst_fetch dba 0x10a6219:1 time 1059055714
pin kdswh11: kdst_fetch dba 0x10a621a:1 time 1059055748
pin kdswh11: kdst_fetch dba 0x10a621b:1 time 1059055781
pin kdswh11: kdst_fetch dba 0x10a621c:1 time 1059055815
pin kdswh11: kdst_fetch dba 0x10a621d:1 time 1059055848
pin kdswh11: kdst_fetch dba 0x10a621e:1 time 1059055883
pin kdswh11: kdst_fetch dba 0x10a621f:1 time 1059055915
pin kdswh11: kdst_fetch dba 0x10a6221:1 time 1059055953
pin kdswh11: kdst_fetch dba 0x10a6222:1 time 1059055992
FETCH #47499559126280:c=0,e=385,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836196432274
以下类似
可以看到上面的 DBA都是连续的
............................
末尾部分
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002933 duration=0 p2=0 p3=0 obj#=-1 tim=1343836366495589
pin kdswh11: kdst_fetch dba 0x10a62f6:1 time 1229119497
pin kdswh11: kdst_fetch dba 0x10a62f7:1 time 1229119545
pin kdswh11: kdst_fetch dba 0x10a62f8:1 time 1229119576
pin kdswh11: kdst_fetch dba 0x10a62f9:1 time 1229119610
pin kdswh11: kdst_fetch dba 0x10a62fa:1 time 1229119644
pin kdswh11: kdst_fetch dba 0x10a62fb:1 time 1229119671
pin kdswh11: kdst_fetch dba 0x10a62fc:1 time 1229119703
pin kdswh11: kdst_fetch dba 0x10a62fd:1 time 1229119730
pin kdswh11: kdst_fetch dba 0x10a62fe:1 time 1229119760
pin kdswh11: kdst_fetch dba 0x10a62ff:1 time 1229119787
FETCH #47499559126280:c=0,e=340,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836366496067
可以看到起始DBA是 0x10a6203 ， 末尾DBA 是 0x10a62ff
以下验证了起始DBA正是MACLEAN表的第一个数据块，而末尾DBA也正是Maclean表高水位块
getbfno函数用于将dba转换为数据文件号和块号
CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
RETURN VARCHAR2
IS
l_str VARCHAR2 (255) DEFAULT NULL;
l_fno VARCHAR2 (15);
l_bno VARCHAR2 (15);
BEGIN
l_fno :=
DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),
'xxxxxxxx'
)
);
l_bno :=
DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),
'xxxxxxxx'
)
);
l_str :=
'datafile# is:'
|| l_fno
|| CHR (10)
|| 'datablock is:'
|| l_bno
|| CHR (10)
|| 'dump command:alter system dump datafile '
|| l_fno
|| ' block '
|| l_bno
|| ';';
RETURN l_str;
END;
/
Function created.
SQL> select getbfno('0x10a6203') from dual;
GETBFNO('0X10A6203')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:680451
dump command:alter system dump datafile 4 block 680451;
SQL> select getbfno('0x10a62ff') from dual;
GETBFNO('0X10A62FF')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:680703
dump command:alter system dump datafile 4 block 680703;
SQL> select dbms_rowid.rowid_block_number(min(rowid)),dbms_rowid.rowid_relative_fno(min(rowid)) from maclean;
DBMS_ROWID.ROWID_BLOCK_NUMBER(MIN(ROWID))
-----------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(MIN(ROWID))
-----------------------------------------
680451
4
SQL> select dbms_rowid.rowid_block_number(max(rowid)),dbms_rowid.rowid_relative_fno(max(rowid)) from maclean;
DBMS_ROWID.ROWID_BLOCK_NUMBER(MAX(ROWID))
-----------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(MAX(ROWID))
-----------------------------------------
680703
4
以上演示验证了3个观点：
1.当OPEN CURSOR 操作发生时， PL/SQL引擎转到SQL引擎负责PARSE SQL语句获得执行计划， 同时它会记录OPEN CURSOR这一刻的SNAPSHOT SCN 快照SCN， 但是Oracle并不会实际FETCH相关的数据，也不会将这些数据复制到某个地方。
2.直到实际FETCH 数据时才会去访问实际的数据块
3. 单纯的open cursor+ fetch bulk collect不会在"某个地方存放结果集"
6390
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
