open cursor 是否去获取数据?

原创 2004年07月03日 00:35:00

 

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进行数据的获取

WAIT #2: nam='db file scattered read' ela= 221 p1=11 p2=770 p3=2

 

 

oracle  document 有描述

SG:Introduction to oracle:sql and pl/sql 21-6上上关于CURSOR的一段话:THE OPEN statement executes the query associated with the cursor,identifies the active set,and positions the
cursor(pointer)bifore the first row.The FETCH statement retrieves the current row and advances the cursor to the next row.

还有21-9中的关于OPEN STATEMENT的原文:Open the cursor to execute
the query and identify the active set,which consists of all rows that
meet the query search criteria.The cursor now points to the first row
in the active set.

 

游标在打开没有执行的时候,本就是没有获取到任何数据

查询SCN确实是在 open cursor 的时候确定的,但IO或者真正查询是在 fetch 的时候才产生的

关于 IO 上面已经证明了
关于查询SCN的确定
下面演示

首先执行一段pl/sql

SQL> set serverout on
SQL> declare
2 v varchar2(30);
cursor c is select d from test1 where a = 1;
3 4 begin
open c;
dbms_lock.sleep(60); 在休眠这60秒内去新session中更新将被查到的行
fetch c into v;
5 6 7 8 dbms_output.put_line('the value : '|| v);
close c;
end; 9 10
11 /
the value : 2

PL/SQL procedure successfully completed.

新session中

SQL> select * from test1 where a = 1;

A B C D
-------------------- -------------------- -------------------- ----------
1 BBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCC 2

SQL> update test1 set d = 0 where a = 1;

1 row updated.

SQL> commit;

Commit complete.


cursor 输出依然是 更新前数据
close c;
end; 9 10
11 /
the value : 2

PL/SQL procedure successfully completed.



因为查询结果集的确立,只需要获取系统SCN 就可以了,这是在open 的时候就确定了的
cursor 虚拟的指向了集合的第一行,但是并没有真正地获取数据,因为集合本身并没有产生。


下面是trace file

=====================
PARSING IN CURSOR #1 len=185 dep=0 uid=41 oct=47 lid=41 tim=1052309975752642 hv=2103278157 ad='54d236cc'
declare
v varchar2(30);
cursor c is select d from test1 where a = 1;
begin
open c;
dbms_lock.sleep(60);
fetch c into v;
dbms_output.put_line('the value : '|| v);
close c;
end;
END OF STMT
PARSE #1:c=0,e=557,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1052309975752624
BINDS #1:
=====================
PARSING IN CURSOR #2 len=31 dep=1 uid=41 oct=3 lid=41 tim=1052309975753431 hv=2424123046 ad='54d0d49c'
SELECT d from test1 where a = 1
END OF STMT
PARSE #2:c=0,e=175,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1052309975753416
BINDS #2:
EXEC #2:c=0,e=129,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1052309975753684
*** 2004-02-23 11:44:36.612
WAIT #1: nam='PL/SQL lock timer' ela= 60000938 p1=6000 p2=0 p3=0 open cursor 之后开始sleep
FETCH #2:c=0,e=291,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1052310035755394 fetch 的时候真正地产生了 cr=3 ,r=1 表示逻辑读3,rows 1
EXEC #1:c=1953,e=60003409,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1052310035756319
WAIT #1: nam='SQL*Net message to client' ela= 8 p1=1650815232 p2=1 p3=0


综合以上论述,与SG 描述并不矛盾。我们要把这里的 the active set 看做一个虚拟的并没有真实存在的集合。

 

即使在游标的查询中有大量的排序甚至join,在open的时候也不会去读数据

SQL> create table tt as select * from t;

Table created.

SQL> desc tt
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> select count(*) from tt;

COUNT(*)
----------
81920
SQL> set serverout on
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL>
SQL> declare
2 v varchar2(30);
3 cursor c is select object_name from tt order by DATA_OBJECT_ID ;
begin
open c;
4 5 6 dbms_lock.sleep(5);
7 fetch c into v;
dbms_output.put_line('the value 1 : '|| v);
8 9 fetch c into v;
dbms_output.put_line('the value 2 : '|| v);
10 11
close c;
12 13 end;
14
15 /
the value 1 : CLU$
the value 2 : COL$

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> exit
Disconnected from 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@jumper udump]$ cat *
/opt/oracle/admin/hsjf/udump/hsjf_ora_3613.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: 15
Unix process pid: 3613, image:
oracle@jumper.hurray.com.cn (TNS V1-V3)

*** 2004-03-12 12:47:53.987
*** SESSION ID16.353) 2004-03-12 12:47:53.986
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=41 oct=42 lid=41 tim=1053832494128016 hv=1346161232 ad='53dd510c'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #1:c=0,e=201,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053832494127359
WAIT #1: nam='SQL*Net message to client' ela= 8 p1=1650815232 p2=1 p3=0
*** 2004-03-12 12:48:04.817
WAIT #1: nam='SQL*Net message from client' ela= 10576349 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=261 dep=0 uid=41 oct=47 lid=41 tim=1053832504706787 hv=1653057355 ad='53d68e9c'
declare
v varchar2(30);
cursor c is select object_name from tt order by DATA_OBJECT_ID ;
begin
open c;
dbms_lock.sleep(5);
fetch c into v;
dbms_output.put_line('the value 1 : '|| v);
fetch c into v;
dbms_output.put_line('the value 2 : '|| v);
close c;
end;
END OF STMT
PARSE #1:c=1953,e=1535,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053832504706759
BINDS #1:
=====================
PARSING IN CURSOR #3 len=48 dep=2 uid=0 oct=3 lid=0 tim=1053832504707818 hv=3997906522 ad='53672788'
select user# from sys.user$ where name = 'OUTLN'
END OF STMT
PARSE #3:c=0,e=158,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1053832504707790
BINDS #3:
EXEC #3:c=0,e=188,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1053832504708208
FETCH #3:c=0,e=148,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1053832504708421
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=118 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=62 us)'
=====================
PARSING IN CURSOR #2 len=51 dep=1 uid=41 oct=3 lid=41 tim=1053832504709113 hv=1161579795 ad='53d5bd50'
SELECT object_name from tt order by DATA_OBJECT_ID
END OF STMT
PARSE #2:c=1953,e=1671,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1053832504709096
BINDS #2:
EXEC #2:c=0,e=171,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1053832504709413
WAIT #1: nam='PL/SQL lock timer' ela= 5000254 p1=500 p2=0 p3=0
WAIT #2: nam='db file scattered read' ela= 412 p1=11 p2=2194 p3=7
WAIT #2: nam='db file scattered read' ela= 349 p1=11 p2=2201 p3=8
WAIT #2: nam='db file scattered read' ela= 371 p1=11 p2=2209 p3=8
WAIT #2: nam='db file scattered read' ela= 385 p1=11 p2=2217 p3=8
WAIT #2: nam='db file scattered read' ela= 425 p1=11 p2=2225 p3=8
WAIT #2: nam='db file scattered read' ela= 390 p1=11 p2=2233 p3=8
WAIT #2: nam='db file scattered read' ela= 384 p1=11 p2=2241 p3=8
WAIT #2: nam='db file scattered read' ela= 374 p1=11 p2=2249 p3=8
WAIT #2: nam='db file scattered read' ela= 380 p1=11 p2=2257 p3=8
WAIT #2: nam='db file scattered read' ela= 407 p1=11 p2=2265 p3=8
WAIT #2: nam='db file scattered read' ela= 418 p1=11 p2=2273 p3=8
WAIT #2: nam='db file scattered read' ela= 360 p1=11 p2=2281 p3=8
WAIT #2: nam='db file scattered read' ela= 375 p1=11 p2=2289 p3=8
WAIT #2: nam='db file scattered read' ela= 361 p1=11 p2=2297 p3=8
WAIT #2: nam='db file scattered read' ela= 383 p1=11 p2=2305 p3=8
WAIT #2: nam='db file scattered read' ela= 370 p1=11 p2=3081 p3=8
WAIT #2: nam='db file scattered read' ela= 3892 p1=11 p2=3209 p3=32
WAIT #2: nam='db file scattered read' ela= 3675 p1=11 p2=3241 p3=32
WAIT #2: nam='db file scattered read' ela= 3595 p1=11 p2=3273 p3=32
WAIT #2: nam='db file scattered read' ela= 3506 p1=11 p2=3305 p3=32
WAIT #2: nam='db file scattered read' ela= 3525 p1=11 p2=3337 p3=32
WAIT #2: nam='db file scattered read' ela= 3603 p1=11 p2=3369 p3=32
WAIT #2: nam='db file scattered read' ela= 3618 p1=11 p2=3401 p3=32
WAIT #2: nam='db file scattered read' ela= 3578 p1=11 p2=3433 p3=32
WAIT #2: nam='db file scattered read' ela= 3845 p1=11 p2=3465 p3=32
WAIT #2: nam='db file scattered read' ela= 3397 p1=11 p2=3497 p3=32
WAIT #2: nam='db file scattered read' ela= 3499 p1=11 p2=3529 p3=32
WAIT #2: nam='db file scattered read' ela= 3523 p1=11 p2=3561 p3=32
WAIT #2: nam='db file scattered read' ela= 3339 p1=11 p2=3593 p3=32
WAIT #2: nam='db file scattered read' ela= 4179 p1=11 p2=3625 p3=32
WAIT #2: nam='db file scattered read' ela= 3512 p1=11 p2=3657 p3=32
WAIT #2: nam='db file scattered read' ela= 3580 p1=11 p2=3689 p3=32
WAIT #2: nam='db file scattered read' ela= 3342 p1=11 p2=3721 p3=32
WAIT #2: nam='db file scattered read' ela= 3436 p1=11 p2=3753 p3=32
WAIT #2: nam='db file scattered read' ela= 3573 p1=11 p2=3785 p3=32
WAIT #2: nam='db file scattered read' ela= 3443 p1=11 p2=3817 p3=32
WAIT #2: nam='db file scattered read' ela= 3512 p1=11 p2=3849 p3=32
WAIT #2: nam='db file scattered read' ela= 4157 p1=11 p2=3881 p3=32
WAIT #2: nam='db file scattered read' ela= 3400 p1=11 p2=3913 p3=32
WAIT #2: nam='db file scattered read' ela= 3515 p1=11 p2=3945 p3=32
WAIT #2: nam='db file scattered read' ela= 3485 p1=11 p2=3977 p3=32
WAIT #2: nam='db file scattered read' ela= 3519 p1=11 p2=4009 p3=32
WAIT #2: nam='db file scattered read' ela= 3577 p1=11 p2=4041 p3=32
WAIT #2: nam='db file scattered read' ela= 3342 p1=11 p2=4073 p3=31
WAIT #2: nam='direct path write' ela= 11 p1=201 p2=764 p3=7
WAIT #2: nam='direct path write' ela= 22 p1=201 p2=771 p3=7
WAIT #2: nam='direct path write' ela= 1 p1=201 p2=778 p3=7
WAIT #2: nam='direct path write' ela= 2 p1=201 p2=785 p3=5
WAIT #2: nam='direct path read' ela= 79 p1=201 p2=769 p3=7
WAIT #2: nam='direct path read' ela= 35 p1=201 p2=521 p3=7
WAIT #2: nam='direct path read' ela= 35 p1=201 p2=645 p3=4
WAIT #2: nam='direct path read' ela= 33 p1=201 p2=726 p3=7
WAIT #2: nam='direct path read' ela= 34 p1=201 p2=678 p3=7
FETCH #2:c=523437,e=592460,p=1076,cr=1027,cu=6,mis=0,r=1,dep=1,og=4,tim=1053832510302853
FETCH #2:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053832510304332
UNMAP #2:c=1953,e=478,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1053832510305241
WAIT #2: nam='direct path read' ela= 19 p1=201 p2=776 p3=1
WAIT #2: nam='direct path read' ela= 4 p1=201 p2=528 p3=7
WAIT #2: nam='direct path read' ela= 2 p1=201 p2=733 p3=7
WAIT #2: nam='direct path read' ela= 25 p1=201 p2=685 p3=7
EXEC #1:c=529296,e=5599226,p=1076,cr=1029,cu=6,mis=0,r=1,dep=0,og=4,tim=1053832510306313
WAIT #1: nam='SQL*Net message to client' ela= 7 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 829 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #3 len=52 dep=0 uid=41 oct=47 lid=41 tim=1053832510308690 hv=1307714173 ad='53dd2cb0'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #3:c=1953,e=677,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053832510308675
BINDS #3:
bind 0: dty=1 mxl=2000(255) mal=25 scl=00 pre=00 oacflg=43 oacfl2=10 size=2000 offset=0
bfp=404edeb8 bln=255 avl=00 flg=05
bind 1: dty=2 mxl=22(02) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=24 offset=0
bfp=404efb80 bln=22 avl=02 flg=05
value=25
WAIT #3: nam='SQL*Net message to client' ela= 8 p1=1650815232 p2=1 p3=0
EXEC #3:c=0,e=1006,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1053832510309876
WAIT #3: nam='SQL*Net message from client' ela= 8459860 p1=1650815232 p2=1 p3=0
STAT #2 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1027 r=1076 w=268 time=592412 us)'
STAT #2 id=2 cnt=81920 pid=1 pos=1 obj=14568 op='TABLE ACCESS FULL TT (cr=1027 r=1022 w=0 time=257994 us)'
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=41 oct=42 lid=41 tim=1053832518771069 hv=4110456808 ad='53d84de8'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=183,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053832518771046
BINDS #1:
EXEC #1:c=0,e=216,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053832518771440
[oracle@jumper udump]$

 

原文参考:http://www.itpub.net/showthread.php?threadid=197416

 

 

 

 

 

 

oracle参数open_cursors和session_cached_cursor详解!

SQL> show parameter open_cursors --每个session(会话)最多能同时打开多少个cursor(游标) NAME ...
  • zq9017197
  • zq9017197
  • 2012年03月12日 16:06
  • 20347

open cursor for

oracle中open cursor for 和for cursor in的区别如下:open cursor for 是直接打开游标查询结果,适用于单表查询,结果集一般不会太大。for cursor ...
  • LeehomeYU
  • LeehomeYU
  • 2017年07月11日 12:01
  • 387

Oracle中cursor(游标)总结

1.       游标: 容器,存储SQL语句影响行数。 2.       游标类型: 隐式游标,显示游标,REF游标。其中,隐式游标和显示游标属于静态游标(运行前将游标与SQL语句关联),REF游标...
  • djfkd
  • djfkd
  • 2008年11月14日 19:26
  • 9549

查看open_cursors

SELECT v.name, v.value value FROM V$PARAMETER v WHERE name = 'open_cursors'; select * from v$open_cu...
  • zhangliao613
  • zhangliao613
  • 2011年01月07日 13:51
  • 470

OPEN CURSOR 例子

BC - ABAP Programming中三个例子,很好,摘抄如下,以供学习   1. DATA: C1 TYPE CURSOR, C2 TYPE CURSOR. DATA: WA1 TYPE SP...
  • u012413770
  • u012413770
  • 2013年10月22日 19:43
  • 167

oracle open_cursors的含义及ORA-01000: 超出打开游标的最大数模拟

Property Description Parameter type Integer Default value 50 Modifiable ALTER SYSTEM ...
  • guogang83
  • guogang83
  • 2016年06月22日 17:26
  • 1518

open_cursors 与 ORA-01000

open_cursors 是可以打开的游标数,如果只打开不关闭,很容易就到上线了 下面我们来做下实验: 先把值改小一点 SQL> alter system set open_cursors=5; S...
  • jgmydsai
  • jgmydsai
  • 2015年03月11日 20:53
  • 1043

open cursor 是否去获取数据?

  cursor open 的时候到底有没有去获取数据是不是fetch的时候才获取数据请看下面实验SQL> conn test/testConnected.SQL> alter session set...
  • biti_rainy
  • biti_rainy
  • 2004年07月03日 00:35
  • 2649

Qt应用程序Cursor样式

Qt应用程序Cursor样式        在X11上,Qt支持Xcursor库,可以随意更改全彩色的Cursor主题。但是在嵌入式Qt开发中,没有Xcursor的支持,效果就没这么乐观。      ...
  • yiyaaixuexi
  • yiyaaixuexi
  • 2011年12月30日 18:35
  • 19293

Android 中查询数据库时Cursor类的使用

在做一个
  • jing110fei
  • jing110fei
  • 2014年08月11日 13:27
  • 6368
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:open cursor 是否去获取数据?
举报原因:
原因补充:

(最多只允许输入30个字)