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

Yong Huang

原帖由 必有我师 于 2009-3-4 01:20 发表
如果对pro*c或其他程序中的游标(简单的SQL)进行了缓存(比如设置了session_cached_cursor，或按照Oracle Document的建议parse once many execution)，那么是不是每次查询到的结果都是一样的？如果是一样的话岂不是看不到对数据的修改，那么做游标缓存岂不是导致错误的结果集了。

It's the cursor, specifically the address of the child cursor, that is cached. The result of executing the cursor is not cached.

Yong Huang

必有我师

原帖由 biti_rainy 于 2004-2-23 11:57 发表
游标在打开没有执行的时候，本就是没有获取到任何数据

查询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  看做一个虚拟的并没有真实存在的集合。

Yong Huang版主，我是因为上面的测试结果而有疑问，即在游标打开时确定了查询SCN(是否可以理解为结果集的SCN)，因此执行查询的时候会去读rollback里的数据，而不是最新状态。

[ 本帖最后由 必有我师 于 2009-3-5 09:49 编辑 ]

Yong Huang

原帖由 必有我师 于 2009-3-4 19:46 发表

Yong Huang版主，我是因为上面的测试结果而有疑问，即在游标打开时确定了查询SCN(是否可以理解为结果集的SCN)，因此执行查询的时候会去读rollback里的数据，而不是最新状态。

Now I see why you asked that question. Your question is "是不是每次查询到的结果都是一样的？" The answer is No, because when you say every time, you mean every time you start a new SQL statement, which opens a new cursor, which has its own starting SCN. biti_rainy's test is using one single cursor so there's only 1 starting SCN.

The reason you bring session_cached_cursor into this discussion means that you're confused between private and shared cursor. That parameter is about shared cursor stored in library cache. It has nothing to do with the private cursor in a session. Too bad, Oracle uses the same term to mean two different things.

Yong Huang

必有我师

原帖由 Yong Huang 于 2009-3-6 02:35 发表


Now I see why you asked that question. Your question is "是不是每次查询到的结果都是一样的？" The answer is No, because when you say every time, you mean every time you start a new SQL statement, which opens a new cursor, which has its own starting SCN. biti_rainy's test is using one single cursor so there's only 1 starting SCN.
我这里指的不是新的SQL statement，而是多次执行由session_cached_cursor缓存起来的游标

The reason you bring session_cached_cursor into this discussion means that you're confused between private and shared cursor. That parameter is about shared cursor stored in library cache. It has nothing to do with the private cursor in a session. Too bad, Oracle uses the same term to mean two different things.
的确有点困惑，能否详解下shared cursor和private cursor。难道session_cached_cursor不是将游标缓存在PGA中？

Yong Huang

你的意思是说由session_cached_cursor缓存的cursor实际上指向shared cursor stored in library cache，对于该cursor的后续执行，每次对会有自己的starting SCN?
多谢Yong Huang版主

Yong Huang

原帖由 必有我师 于 2009-3-6 01:18 发表

你的意思是说由session_cached_cursor缓存的cursor实际上指向shared cursor stored in library cache，对于该cursor的后续执行，每次对会有自己的starting SCN?
多谢Yong Huang版主

Session cursor cache stores the address of a shared child cursor in a session's UGA (which is in PGA normally). It does not store database data.

Yong Huang

必有我师

谢谢，Yong Huang

wangxufeng1981

学习了，很好的验证方法。