open 游标+bulk Limite 1000时

zengmuansha

请问 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; 这样子的呢?

Maclean Liu(刘相兵

在PL/SQL 中 open cursor 做了什么？

ODM TEST:

SQL> oradebug setmypid;
Statement processed.


SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> oradebug tracefile_name;

/s01/admin/G10R21/udump/g10r21_ora_14946.trc


declare
cursor vCUR is select 121 from mac;
v_cur  number;
begin
open vCUR;
dbms_lock.sleep(60);
fetch vCUR into  v_cur;
end ;
/





PARSING IN CURSOR #2 len=19 dep=1 uid=0 oct=3 lid=0 tim=1301019645435103 hv=2424324144 ad='9973f268'
SELECT 121 FROM MAC
END OF STMT
PARSE #2:c=0,e=334,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1301019645435096
BINDS #2:
EXEC #2:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1301019645435205



PARSE
....................................................

WAIT 60 SECONDS



FETCH #2:c=0,e=70,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1301019782012990
EXEC #3:c=6999,e=58609703,p=0,cr=77,cu=0,mis=0,r=1,dep=0,og=1,tim=1301019782013055
WAIT #3: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1301019782013144
WAIT #3: nam='SQL*Net message from client' ela= 979 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1301019782014152
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=56331 op='TABLE ACCESS FULL MAC (cr=3 pr=0 pw=0 time=57 us)'
WAIT #0: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1301019782014262


当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错误。

http://t.askmaclean.com/redirect ... amp;goto=nextnewset

zengmuansha

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

会不会去执行 执行计划?

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

zengmuansha

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

zengmuansha

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

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

当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

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

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

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

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

Maclean Liu(刘相兵

"否则如何知道要提取10000条呢?"

这句话是什么意思？

Maclean Liu(刘相兵

再议OPEN CURSOR与BULK COLLECT
http://www.askmaclean.com/archiv ... 8Ebulk-collect.html


再议OPEN CURSOR与BULK COLLECT
AUGUST 1, 2012 BY MACLEAN LIU LEAVE A COMMENT
有同学在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


以上演示验证了2个观点：
1.当OPEN CURSOR 操作发生时， PL/SQL引擎转到SQL引擎负责PARSE SQL语句获得执行计划， 同时它会记录OPEN CURSOR这一刻的SNAPSHOT SCN 快照SCN， 但是Oracle并不会实际FETCH相关的数据，也不会将这些数据复制到某个地方。
2.直到实际FETCH 数据时才会去访问实际的数据块
3. 单纯的open cursor+ fetch bulk collect不会在”某个地方存放结果集”

zengmuansha

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

这句话是什么意思？

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

zengmuansha

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


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

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

zengmuansha

=====================
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

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

*** 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

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

Maclean Liu(刘相兵

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

你这个是order by ， 肯定要全表扫描完了 先放到PGA或者临时表空间，否则第一次fetch就无法成功

zengmuansha

*** 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

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

Maclean Liu(刘相兵

你的问题只是10046 trace打印执行计划的顺序的问题， 你可以做个10053 trace 可以看到在parse阶段就生成了执行计划

tom 关于这个open cursor的问题的描述还是很准确的， 虽然要有怀疑精神，但是不要轻易怀疑权威，