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

biti_rainy · 发表于 2004-3-12 15:04

你看看open消耗了多少时间， 0.01  秒！

你的这个推论是建立在错误的理解的基础上推导得出来的

SQL> select count(*) from tt;

  COUNT(*)
----------
163840

QL>
SQL> declare
  2 v varchar2(30);
  3 n1 number;
  4  n2 number;
  5  cursor c  is select *  from tt t1,tt t2,tt t3 where t1.owner = t2.owner and t2.owner = t3.owner order by t2.DATA_OBJECT_ID ;
begin
  6 7  n1 := dbms_utility.get_time;
open c;
  8 9  n2 := dbms_utility.get_time;
dbms_output.put_line('the n2 - n1 is : '|| to_char(n2 - n1));
10 11
close c;
end;  12 13
14
15  /
the n2 - n1 is : 1

PL/SQL procedure successfully completed.

*** 2004-03-12 15:15:50.162
*** SESSION ID

17.93) 2004-03-12 15:15:50.161
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=41 oct=42 lid=41 tim=1053841162267710 hv=1346161232 ad='5351e54c'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #1:c=0,e=203,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053841162267045
WAIT #1: nam='SQL*Net message to client' ela= 7 p1=1650815232 p2=1 p3=0
*** 2004-03-12 15:16:03.977
WAIT #1: nam='SQL*Net message from client' ela= 13490822 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=323 dep=0 uid=41 oct=47 lid=41 tim=1053841175760594 hv=3090075666 ad='53afa650'
declare
v varchar2(30);
n1 number;
n2 number;
cursor c  is select *  from tt t1,tt t2,tt t3 where t1.owner = t2.owner and t2.owner = t3.owner order by t2.DATA_OBJECT_ID ;
begin
n1 := dbms_utility.get_time;
open c;
n2 := dbms_utility.get_time;
dbms_output.put_line('the n2 - n1 is : '|| to_char(n2 - n1));
close c;
end;
END OF STMT
PARSE #1:c=1953,e=1190,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053841175760570
BINDS #1:
=====================
PARSING IN CURSOR #3 len=48 dep=2 uid=0 oct=3 lid=0 tim=1053841175761849 hv=3997906522 ad='535f78f4'
select user# from sys.user$ where name = 'OUTLN'
END OF STMT
PARSE #3:c=0,e=143,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1053841175761821
BINDS #3:
EXEC #3:c=0,e=191,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1053841175762257
FETCH #3:c=0,e=156,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1053841175762474
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=122 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=67 us)'
=====================
PARSING IN CURSOR #2 len=110 dep=1 uid=41 oct=3 lid=41 tim=1053841175763068 hv=1755094983 ad='53ae53d4'
SELECT *  from tt t1,tt t2,tt t3 where t1.owner = t2.owner and t2.owner = t3.owner order by t2.DATA_OBJECT_ID
END OF STMT
PARSE #2:c=1953,e=1608,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1053841175763054
BINDS #2:
EXEC #2:c=0,e=239,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1053841175763426
EXEC #1:c=3906,e=3544,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1053841175764430
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= 795 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #3 len=52 dep=0 uid=41 oct=47 lid=41 tim=1053841175768023 hv=1307714173 ad='53adc9e0'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #3:c=1953,e=2223,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1053841175767990
BINDS #3:
bind 0: dty=1 mxl=2000(255) mal=25 scl=00 pre=00 oacflg=43 oacfl2=10 size=2000 offset=0
bfp=404ed244 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=404efbb8 bln=22 avl=02 flg=05
value=25
WAIT #3: nam='SQL*Net message to client' ela= 10 p1=1650815232 p2=1 p3=0
EXEC #3:c=7812,e=7624,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1053841175775889
*** 2004-03-12 15:16:14.040
WAIT #3: nam='SQL*Net message from client' ela= 9810370 p1=1650815232 p2=1 p3=0
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='MERGE JOIN  (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=3 cnt=0 pid=2 pos=1 obj=0 op='MERGE JOIN  (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT JOIN (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=5 cnt=0 pid=4 pos=1 obj=14568 op='TABLE ACCESS FULL TT (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=6 cnt=0 pid=3 pos=2 obj=0 op='SORT JOIN (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=7 cnt=0 pid=6 pos=1 obj=14568 op='TABLE ACCESS FULL TT (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=8 cnt=0 pid=2 pos=2 obj=0 op='SORT JOIN (cr=0 r=0 w=0 time=0 us)'
STAT #2 id=9 cnt=0 pid=8 pos=1 obj=14568 op='TABLE ACCESS FULL TT (cr=0 r=0 w=0 time=0 us)'
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=41 oct=42 lid=41 tim=1053841185588269 hv=4110456808 ad='5350ca90'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=1953,e=234,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053841185588248
BINDS #1:
EXEC #1:c=0,e=218,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053841185588642
[oracle@jumper udump]$

bodyguard · 发表于 2004-3-12 15:07

我将用事实来说明一些东西.

biti_rainy · 发表于 2004-3-12 15:10

最初由 bodyguard 发布
[B]我将用事实来说明一些东西. [/B]

等的就是这个……

因为我觉得自己做的实验已经说明了 open 不会真正地操作到实际表中数据

bodyguard · 发表于 2004-3-12 18:57

实验条件:
比较简单,一个内存256MB的PC上同时跑了LINUX,WINDOWS.ORACLE数据库在LINUX下.给数据库的内存总共只有几十MB.测试用的test表也是一个简单表,记录数1048576.
SQL> alter  session  set  events  '10046 trace name context  forever,level 12';

Session altered.

SQL> declare
cursor  cur_test is select  *  from  test  order  by  1,2,3;
cur_row  cur_test%rowtype;
t1  number;
t2  number;
t3  number;
begin
dbms_lock.sleep(10);
--t1:=dbms_utility.get_time;
--dbms_output.put_line('t1='||t1);
open cur_test;
dbms_lock.sleep(10);
fetch  cur_test  into  cur_row;
dbms_lock.sleep(10);
loop
   fetch cur_test  into  cur_row;
   exit  when cur_test%notfound;
end loop  ;
close  cur_test;
--t2:=dbms_utility.get_time;
--dbms_output.put_line('t2='||t2);
end;
/  2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

PL/SQL procedure successfully completed.

SQL> alter  session  set  events  '10046 trace name context  off';
生成的trace文件很大(70-80MB)
下面是部分内容
PARSE #1:c=13672,e=557575,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1053790833956291
BINDS #1:
*** 2004-03-12 16:57:04.323
WAIT #1: nam='PL/SQL lock timer' ela= 9999061 p1=1000 p2=0 p3=0    ------------------!!!
=====================
PARSING IN CURSOR #3 len=48 dep=2 uid=0 oct=3 lid=0 tim=1053790844474988 hv=1005331575 ad='507acaf8'
select user# from sys.user$ where name = 'OUTLN'
END OF STMT
PARSE #3:c=1953,e=372694,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1053790844474959
BINDS #3:
EXEC #3:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1053790844475206
FETCH #3:c=1953,e=65486,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1053790844540718
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ '
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 '
=====================
PARSING IN CURSOR #2 len=39 dep=1 uid=22 oct=3 lid=22 tim=1053790844548496 hv=3822992111 ad='507a9238'
SELECT  *  from  test  order  by  1,2,3
END OF STMT
PARSE #2:c=3906,e=482033,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1053790844548489
BINDS #2:
EXEC #2:c=0,e=8997,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1053790844557551
*** 2004-03-12 16:57:15.379
WAIT #1: nam='PL/SQL lock timer' ela= 10305422 p1=1000 p2=0 p3=0       ------------------!!!
*** 2004-03-12 16:58:00.162
WAIT #2: nam='db file sequential read' ela= 353621 p1=3 p2=440 p3=1
WAIT #2: nam='db file scattered read' ela= 496244 p1=3 p2=442 p3=7
WAIT #2: nam='db file scattered read' ela= 253 p1=3 p2=449 p3=8
WAIT #2: nam='db file scattered read' ela= 48647 p1=3 p2=457 p3=8
...

WAIT #2: nam='direct path write' ela= 9 p1=201 p2=633 p3=7
WAIT #2: nam='direct path write' ela= 1 p1=201 p2=640 p3=7
WAIT #2: nam='direct path write' ela= 1 p1=201 p2=647 p3=2
...
WAIT #2: nam='direct path read' ela= 13 p1=201 p2=1615 p3=7
FETCH #2:c=3124999,e=194548919,p=18004,cr=7619,cu=158,mis=0,r=1,dep=1,og=4,tim=1053791049412204
*** 2004-03-12 17:00:48.207
WAIT #1: nam='PL/SQL lock timer' ela= 10038125 p1=1000 p2=0 p3=0          ----------------!!!
FETCH #2:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062730732
FETCH #2:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062730857
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062730886
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062730916
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062730945
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062730974
FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062731002
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062731031
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791062731060
...
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791120763847
FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791120763876
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791120763905
...
FETCH #2:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1053791127682258
FETCH #2:c=3907,e=2600198,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1053791130282483
EXEC #1:c=37083984,e=297216323,p=21525,cr=7621,cu=158,mis=0,r=1,dep=0,og=4,tim=1053791131172830
WAIT #1: nam='SQL*Net message to client' ela= 155339 p1=1650815232 p2=1 p3=0
*** 2004-03-12 17:09:50.202
WAIT #1: nam='SQL*Net message from client' ela= 458945383 p1=1650815232 p2=1 p3=0
STAT #2 id=1 cnt=1048576 pid=0 pos=1 obj=0 op='SORT ORDER BY '
STAT #2 id=2 cnt=1048576 pid=1 pos=1 obj=5947 op='TABLE ACCESS FULL TEST '
=====================
PARSING IN CURSOR #1 len=60 dep=0 uid=22 oct=42 lid=22 tim=1053791594973790 hv=2622962732 ad='50768e78'
alter  session  set  events  '10046 trace name context  off'
END OF STMT
PARSE #1:c=1953,e=460605,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053791594973773
BINDS #1:
EXEC #1:c=1953,e=1063909,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1053791596037806

bodyguard · 发表于 2004-3-12 19:12

可以看出:
1:游标的OPEN是非常快的(1秒)
2:Fetch第一条记录花了3.5分钟
3:Fetch剩下的1048575条记录花了9分钟
4:Fetch第一条记录时系统做了全表扫描,直接读写,消耗了大量的资源,做了排序.
5:Fetch剩下的1048575条记录仅仅是取一下结果而已..
是否可以认为:
1

pen没读记录,Fetch时才读记录
2:在前述条件下Fetch第一条记录时数据库已经完成了该游标对应的SQL语句的执行,并且生成了结果集(这个集合可能仅是"引用",也可能是实体).
3:Fetch剩下的记录仅仅是简单的取数而已.

biti_rainy · 发表于 2004-3-12 23:43

说了半天，我不是就在说这个结论嘛

若有排序，第一次fetch的时候有所有记录的排序自然很正常啊

若是 FTS，则只进行一次 IO

我不是都早测试过了吗？

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

最初由 biti_rainy 发布
[B]说了半天，我不是就在说这个结论嘛

若有排序，第一次fetch的时候有所有记录的排序自然很正常啊

若是 FTS，则只进行一次 IO

我不是都早测试过了吗？ [/B]

"若有排序，第一次fetch的时候有所有记录的排序自然很正常啊"这时就是一个风险点.常说的游标OPEN失败,往往就是这里.第一次Fetch实际上包括两个动作:移到"BOF"和移到第一条记录.后面的FETCH就相对简单了(其实也要看具体的SQL,也可能很复杂)

游标的处理(包括OPEN)是一个非常复杂的过程.
游标的预读,双向滚动,close前的提交,优化器模式等东西都有联系.
搞定游标,不容易.

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

在某些情况下,OPEN游标时就锁定了相关记录,这些锁定的依据或许是ROWID,或许是字段中的条件值.OPEN时存在以下几种情况
1:不需读表中数据,也不需rowid
2:需要读rowid
3:需要读表中部分数据(但并不返回数据),读的这些数据并不是SELECT 子句中的项,而是where 以后的部分.
这个我以后再做实验.
biti_rainy或其他人有空做了贴上了让我们欣赏欣赏.

biti_rainy · 发表于 2004-3-13 16:41

最初由 bodyguard 发布
[B]
"若有排序，第一次fetch的时候有所有记录的排序自然很正常啊"这时就是一个风险点.常说的游标OPEN失败,往往就是这里.第一次Fetch实际上包括两个动作:移到"BOF"和移到第一条记录.后面的FETCH就相对简单了(其实也要看具体的SQL,也可能很复杂)

游标的处理(包括OPEN)是一个非常复杂的过程.
游标的预读,双向滚动,close前的提交,优化器模式等东西都有联系.
搞定游标,不容易. [/B]

第一次 fetch 和 open 是两码事

常说的 open 失败事什么具体情况具体信息,请明示

biti_rainy · 发表于 2004-3-13 16:50

游标的预读,双向滚动,close前的提交,优化器模式等东西都有联系.

------ 关于预读,是 open 的时候还是 fetch 的时候,你能确信码?
要么请给出原文出处,要么用试验来证实,无法证实的想当然对于我来说台困难了

双向滚动是否是在有限记录范围内滚动?是否是先声明了特殊类型的游标?可这跟open有必然联系码?
close 前的提交也好,优化模式也好,跟 open 是否处理表数据有什么关系?

我们争论的焦点是 open 的时候数否处理数据表数据本身,而不是说open的时候怎样的复杂!产生良好的执行计划的过程当然很复杂,这是谁都知道的道理! 没有谁争论这个问题,设计过多的东西进来恐怕只是让你自己更晕?

如果你要说产生执行计划的过程中涉及的东西,包括数据字典表和统计信息等,那就每必要再讨论下去了

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

这么大一个表做3次表连接还排序够意思了吧？

浏览过的版块