关于cursor open 的时候到底做了些什么

bodyguard · 发表于 2004-3-11 20:12

THE OPEN statement executes the query associated with the cursor,identifies the active set,and positions the
cursor(pointer)before the first row.
这句话值得玩味.
如果游标当中有order by等,open时会不会才生很大的系统开销(IO,内存等)?

ZALBB · 发表于 2004-3-12 11:58

若不读数据，如何执行排序、合并操作？

biti_rainy · 发表于 2004-3-12 12:39

我们可以看出，open的时候依然没有读任何数据，而是在fetch的时候才读表，写temp，读排序后数据

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 ID

16.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]$

bodyguard · 发表于 2004-3-12 13:14

从上面的帖子可以看出:在Fetch第一条记录前,Oracle完成了绝大多数临时段段的操作,这些操作是由于排序引起的;除临时段之外的数据块的读操作.
Fetch时仅仅取了几个数据而已(当然也有部分临时段操作).
在上面这条语句中Fetch之前的系统开销远大于Fetch产生的系统开销.我相信,如果tt表的记录数再增大1000倍,更能说明问题.
我觉得,游标,尤其是涉及到orderby ,sum,distinct,for update 等的游标的Open是一个很复杂的过程,在某些情况下Open甚至会失败;Ftech是一个比较简单的操作.到底哪一步更耗资源,很难一概而论.
另外:由于Open需要考虑的东西太多,不能完全排除Oracle在这个过程中部分才用异步操作(滞后操作)(Open返回了,其实内部还在处理,直到进行下一个操作如Fetch之前才结束,或把把Open的部分操作滞后到Fetch第一条语句时处理).

biti_rainy · 发表于 2004-3-12 13:34

你说的是不对的

open 的时候并没有做什么跟数据相关的大量操作！！！

请看 open  and  fetch 之间还有一个  dbms_lock.sleep !!!!!!!

而读数据，是在 sleep  之后的！
也就是说 fetch 之前进行了读数据，但并不是open的时候读的！

说fetch和读数据没关系吗？  open并没有导致读的产生，是因为要fetch才读，当然得先读再fetch了。数据都没有游标还滚动个啥？

biti_rainy · 发表于 2004-3-12 13:59

declare
v varchar2(30);
cursor c  is select object_name from tt ;
begin
open c;
dbms_lock.sleep(5);
fetch c into v;
dbms_lock.sleep(5);
fetch c into v;

for i in 1..10000 loop
fetch c into v;
end loop;

close c;
end;

=====================
PARSING IN CURSOR #2 len=29 dep=1 uid=41 oct=3 lid=41 tim=1053837005677963 hv=2676723348 ad='534bd22c'
SELECT object_name from tt
END OF STMT
PARSE #2:c=27343,e=25687,p=0,cr=38,cu=0,mis=1,r=0,dep=1,og=4,tim=1053837005677937
BINDS #2:
EXEC #2:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1053837005678273
WAIT #1: nam='PL/SQL lock timer' ela= 5000236 p1=500 p2=0 p3=0 这是open之后的dbms_lock.sleep休眠5秒 :ela= 5000236
WAIT #2: nam='db file scattered read' ela= 441 p1=11 p2=2194 p3=7    --- 这里产生物理读了7个block  : p3=7
FETCH #2:c=0,e=985,p=7,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837010680193 -------  fetch
WAIT #1: nam='PL/SQL lock timer' ela= 4999384 p1=500 p2=0 p3=0    ------------  又休眠5秒
FETCH #2:c=0,e=132,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015680251    ------  在前面7个block中获取数据
FETCH #2:c=0,e=21,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015680430
FETCH #2:c=0,e=19,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015680537
FETCH #2:c=0,e=20,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015680619
FETCH #2:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015680697
************ 这里是一堆fetch
WAIT #2: nam='db file scattered read' ela= 459 p1=11 p2=2201 p3=8 -------  前面7block数据读完后重新进行一次物理读
FETCH #2:c=0,e=821,p=8,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015729273 再开始从第8个block中开始获取数据
FETCH #2:c=0,e=66,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015729699
FETCH #2:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015729809
FETCH #2:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1053837015729889
...............................................

因为要fetch才需要先获取数据，但并不是全部获取，很可能是部分获取，比如这里的FTS就是。open本身并不获取数据本身

bodyguard · 发表于 2004-3-12 14:05

谢谢biti_rainy
1:增加 tt的记录量,
2:关库,开库(可选).
3:请使用:cursor c is select object_name from tt ORDER BY XXX.

biti_rainy · 发表于 2004-3-12 14:36

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= 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 ------ 这里写temp
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 ------- 这里读temp
WAIT #2: nam='direct path read' ela= 35 p1=201 p2=521 p3=7

先 open cursor ,然后休眠，再读数据，再写temp 和读 temp

我不知道为什么说排序的过程是在 open的过程中？

分明休眠的过程插在其中证明了这个问题

你若有猜测，请证明之。
若需要实验，请实验之
共享，谢谢

bodyguard · 发表于 2004-3-12 14:44

老兄,你还没明白我的意思?
前面已经有人说了,这里可能会滞后操作.尊却地说,数据库在把游标移到"BOF"时完成了绝大多数操作.BOF以后仅仅是取几条记录而已.

biti_rainy · 发表于 2004-3-12 14:52

在 open 的时候根本就没有产生集合

实际上，bof 的概念我不懂，但是，在我的理解中，open只是虚拟的认为指向某个集合的首部，这个集合没有产生，只是个null而已，我不知道有什么不妥

从哪里来会发现游标的open会滞后，或者会产生的大量的操作，在这个过程中无非是 parse 再open (确立查询SCN等工作)

1：没有任何地方明显表明 open 有涉及到复杂的数据操作
2：即使如你所谓的猜测的滞后的问题，那我们只打开而不fetch，你觉得能说明问题吗？

关于cursor open 的时候到底做了些什么

看看下面的关闭数据库后重新启动数据库的实验

你看看 dbms_lock.sleep 的位置就知道了

浏览过的版块