验证open cursor 与 fetch的内部操作

原文转自：http://www.askmaclean.com/archives/%e5%86%8d%e8%ae%aeopen-cursor%e4%b8%8ebulk-collect.html

当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:

找到对应的trace file 打开：

SQL> oradebug setmypid      (设置进程，否则下一条语句报：ORA-00074: no process has been specified 错误)

已处理的语句

SQL>oradebug tracefile_name

d:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_10596.trc

下面为跟踪文件内容：

 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不会在”某个地方存放结果集”

时间： 2024-10-24 02:47:26

验证open cursor 与 fetch的内部操作

验证open cursor 与 fetch的内部操作的相关文章

字符集问题的初步探讨(七)----关于字符集更改的内部操作

一种flash中密码验证的新方法：检测鼠标操作

oracle下巧用bulk collect实现cursor批量fetch的sql语句_oracle

C#线程内部操作datagridview 使数据重新加载实现向上跑动！！！

“极验验证“让原本枯燥的验证码操作变得有趣多

python实践3：cursor() — 数据库连接操作

ASP.NET 2.0 中的窗体身份验证

phalapi-入门篇5(数据库操作和Model层)

javascript设计模式--策略模式之输入验证_javascript技巧