再议OPEN CURSOR与BULK COLLECT

有同学在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
--------------------------------------------------------------------------------
了解 Maclean Liu 刘相兵 @诗檀软件
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不会在”某个地方存放结果集”

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号