关于cursoropen的时候到底做了些什么
cursor open 的时候到底有没有去获取数据是不是fetch的时候才获取数据
请看下面实验
SQL> conn test/test
Connected.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> declare
2 cursor c is select * from test1;
3 begin
4 open c;
5 close c;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
[oracle@jumper udump]$ cat *.trc
/opt/oracle/admin/hsjf/udump/hsjf_ora_26966.trc
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0
System name: Linux
Node name: jumper.hurray.com.cn
Release: 2.4.18-14
Version: #1 Wed Sep 4 13:35:50 EDT 2002
Machine: i686
Instance name: hsjf
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 26966, image: oracle@jumper.hurray.com.cn (TNS V1-V3)
*** 2004-02-22 23:51:41.363
*** SESSION ID20.3141) 2004-02-22 23:51:41.363
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=41 oct=42 lid=41 tim=1052268263050484 hv=1346161232 ad='54d7e004'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #1:c=0,e=211,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1052268263049839
WAIT #1: nam='SQL*Net message to client' ela= 8 p1=1650815232 p2=1 p3=0
*** 2004-02-22 23:52:06.023
WAIT #1: nam='SQL*Net message from client' ela= 24081533 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=71 dep=0 uid=41 oct=47 lid=41 tim=1052268287142522 hv=190018789 ad='54d87df0'
declare
cursor c is select * from test1;
begin
open c;
close c;
end;
END OF STMT
PARSE #1:c=9765,e=9616,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1052268287142473
BINDS #1:
=====================
PARSING IN CURSOR #3 len=48 dep=2 uid=0 oct=3 lid=0 tim=1052268287143761 hv=3997906522 ad='53696c28'
select user# from sys.user$ where name = 'OUTLN'
END OF STMT
PARSE #3:c=0,e=232,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1052268287143735
BINDS #3:
EXEC #3:c=0,e=190,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1052268287144166
FETCH #3:c=0,e=180,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1052268287144402
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID OBJ#(22) (cr=2 r=0 w=0 time=145 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=44 op='INDEX UNIQUE SCAN OBJ#(44) (cr=1 r=0 w=0 time=81 us)'
=====================
PARSING IN CURSOR #2 len=19 dep=1 uid=41 oct=3 lid=41 tim=1052268287145054 hv=1259978721 ad='54d7b05c'
SELECT * from test1
END OF STMT
PARSE #2:c=1953,e=1802,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1052268287145039
BINDS #2:
EXEC #2:c=0,e=134,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1052268287145318
EXEC #1:c=3907,e=2694,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1052268287145505
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 4961677 p1=1650815232 p2=1 p3=0
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=14498 op='TABLE ACCESS FULL TEST1 (cr=0 r=0 w=0 time=0 us)'
=====================
PARSING IN CURSOR #1 len=56 dep=0 uid=41 oct=42 lid=41 tim=1052268292109059 hv=527042363 ad='54d8b9c4'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=811,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1052268292109029
BINDS #1:
EXEC #1:c=0,e=197,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1052268292109432
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> declare
2 v varchar2(30);
3 cursor c is select a from test1;
4 begin
5 open c;
6 fetch c into v;
7 close c;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=41 oct=42 lid=41 tim=1052268348282083 hv=1346161232 ad='54d7e004'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #1:c=0,e=190,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1052268348282051
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1650815232 p2=1 p3=0
*** 2004-02-22 23:54:15.491
WAIT #1: nam='SQL*Net message from client' ela= 65283612 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=100 dep=0 uid=41 oct=47 lid=41 tim=1052268413585464 hv=4163332771 ad='54d76b58'
declare
v varchar2(30);
cursor c is select a from test1;
begin
open c;
fetch c into v;
close c;
end;
END OF STMT
PARSE #1:c=9765,e=18819,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1052268413585420
BINDS #1:
=====================
PARSING IN CURSOR #2 len=19 dep=1 uid=41 oct=3 lid=41 tim=1052268413587757 hv=3226909281 ad='54d7af2c'
SELECT a from test1
END OF STMT
PARSE #2:c=1954,e=1601,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1052268413587721
BINDS #2:
EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1052268413588187
WAIT #2: nam='db file scattered read' ela= 221 p1=11 p2=770 p3=2
FETCH #2:c=1953,e=1194,p=2,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=1052268413589563
EXEC #1:c=3907,e=4119,p=2,cr=6,cu=1,mis=0,r=1,dep=0,og=4,tim=1052268413589885
WAIT #1: nam='SQL*Net message to client' ela= 8 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 6374702 p1=1650815232 p2=1 p3=0
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=14498 op='TABLE ACCESS FULL TEST1 (cr=6 r=2 w=0 time=1158 us)'
=====================
PARSING IN CURSOR #1 len=56 dep=0 uid=41 oct=42 lid=41 tim=1052268419965801 hv=527042363 ad='54d8b9c4'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=182,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1052268419965781
BINDS #1:
EXEC #1:c=0,e=203,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1052268419966144
[oracle@jumper udump]$
我们可以看出在 open cursor 的过程中并未曾去获取过 数据
也就是对于数据文件 11 块编号为 770的 block进行数据的获取
上一个：oracle的调优
下一个：如何有效的利用数据字典
