watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

         题目         部分

在Oracle中,什么是会话游标?会话游标和共享游标的区别有哪些?会话游标分为哪几类?


     
         答案部分          



(一)会话游标的含义

会话游标(Session Cursor)是当前会话(Session)解析和执行SQL的载体,即会话游标用于在当前会话中解析和执行SQL,会话游标是以哈希表的方式缓存在PGA中(共享游标是缓存在SGA的库缓存里)。在目标SQL的执行过程中,会话游标起承上启下的作用。因为Oracle依靠会话游标来将目标SQL所涉及的数据从Buffer Cache的对应数据块读到PGA里,然后在PGA里做后续的排序、表连接等处理,最后将最终的处理结果返回给用户,所以,会话游标是当前会话解析和执行SQL的载体。

共享游标和会话游标的对比如下表所示:


共享游标(Shared Cursor)

会话游标(Session Cursor)

缓存位置

缓存在SGA中的共享池里的库缓存(Library Cache)中。

缓存在每个会话的PGA中。

共享

共享游标在所有会话之间共享。

会话游标(Session Cursor)与会话(Session)是一一对应的,不同会话的会话游标之间不能共享,这是与共享游标(Shared Cursor)的本质区别。

生命周期

共享游标无生命周期,会进行缓存。

会话游标是有生命周期的,每个会话游标在使用的过程中都至少会经历一次Open、Parse、Bind、Execute、Fetch和Close中的一个或多个阶段。Oracle会根据参数SESSION_CACHED_CURSORS的值来决定是否将已经用过的会话游标缓存在对应会话的PGA中。

联系

1.会话游标是以哈希表的方式缓存在PGA中,意味着Oracle会通过相关的哈希运算来存储和访问在当前会话的PGA中的对应会话游标。这种访问机制和共享游标是一样的,可以简单地认为Oracle是根据目标SQL的SQL文本的哈希值去PGA中的相应Hash Bucket中找匹配的会话游标。由于在缓存会话游标的哈希表的对应Hash Bucket中,Oracle会存储目标SQL对应的父游标的库缓存对象句柄地址,所以,Oracle可以通过会话游标找到对应的父游标,进而就可以找到对应子游标中目标SQL的解析树和执行计划,然后Oracle就可以重用目标SQL的解析树和执行计划来执行SQL语句了。

2.一个会话游标只能对应一个共享游标,而一个共享游标却可以同时对应多个会话游标。

(二)会话游标的分类

会话游标的详细分类参考下表:

表 3-20 Oracle中会话游标的分类

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


在上表中需要注意的是,动态游标是Oracle数据库中最灵活的一种会话游标,它的灵活性表现在:①动态游标的定义方式非常灵活,它可以有多种定义方式。②动态游标可以作为存储过程的输入参数和函数的输出参数。上表中的各种游标希望读者可以通过做大量的练习题来掌握,毕竟游标是存储过程开发过程中必不可少的内容。

(三)会话游标的属性

会话游标有4个属性,见下表:

表 3-21 游标的属性

属性

类型

简介

适用对象

适用SQL

SQL%FOUND

布尔型

最近的FETCH是否提取到数据,表示一条SQL语句被执行成功后受其影响而改变的记录数是否大于或等于1,若是则赋值为TRUE,否则为FALSE。在一条DML语句被执行前,SQL%FOUND的值是NULL。

隐式游标、显式游标

INSERT、DELETE、UPDATE、SELECT ... INTO ...

SQL%NOTFOUND

布尔型

最近的FETCH是否没有提取到数据,表示一条SQL语句被执行成功后受其影响而改变的记录数是否为0,若是则赋值为TRUE,否则为FALSE。在一条DML语句被执行前,SQL%NOTFOUND的值是NULL。

隐式游标、显式游标

SQL%ROWCOUNT

数值型

表示最近的一条SQL语句成功执行后受其影响而改变的记录的数量,后续执行的SQL会覆盖SQL%ROWCOUNT的值。

隐式游标、显式游标

SQL%ISOPEN

布尔型

游标是否打开,当游标打开时返回TRUE。对于隐式游标而言,SQL%ISOPEN的值永远是FALSE。

显式游标

当执行一条DML语句后,DML语句的结果保存在这四个游标属性中,这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时,PL/SQL打开一个内建游标并处理结果。在这些属性中,SQL%FOUND和SQL%NOTFOUND是布尔值,SQL%ROWCOUNT是整数值。需要注意的是,若游标属于隐式游标,则在PL/SQL中可以直接使用上表中的属性,若游标属于显式游标,则上表中的属性里“SQL%”需要替换为自定义显式游标的名称。上表中的这4个属性对于动态游标依然适用。

(四)会话游标的相关参数

和会话游标相关的有两个重要参数,分别为OPEN_CURSORS和SESSION_CACHED_CURSORS,下面详细介绍这两个参数。

(1)参数OPEN_CURSORS用于设定单个会话中同时能够以OPEN状态并存的会话游标的总数,默认值为50。若该值为300,则表示单个会话中同时能够以OPEN状态并存的会话游标的总数不能超过300,否则Oracle会报错“ORA-1000:maximum open cursors exceeded”。视图V$OPEN_CURSOR可以用来查询数据库中状态为OPEN或者己经被缓存在PGA中的会话游标的数量和具体信息(例如,SQL_ID和SQL文本等)。当然,也可以从视图V$SYSSTAT中查到当前所有以OPEN状态存在的会话游标的总数。

[email protected] > show parameter open_cursors
2
3NAME                                 TYPE        VALUE
4------------------------------------ ----------- ------------------------------
5open_cursors                         integer     65535
6
7SELECT USERENV('SID') FROM DUAL;
8SELECT * FROM V$OPEN_CURSOR WHERE SID=16;
9SELECT * FROM V$SYSSTAT D WHERE D.NAME ='opened cursors current';
     


(2)参数SESSION_CACHED_CURSORS用于设定单个会话中能够以Soft Closed状态缓存在PGA中的会话游标的总数。在Oracle 10g中默认为20(注意:在官方文档中记录的该值默认为0是有误的),11g中默认为50。

[email protected] > show parameter session_cached_cursors
2
3NAME                                 TYPE        VALUE
4------------------------------------ ----------- ------------------------------
5session_cached_cursors               integer     50
     


从上述显示结果可以看出,SESSION_CACHED_CURSORS的值为50,意味着在这个库里,单个会话中同时能够以Soft Closed状态缓存在PGA中的会话游标的总数不能超过50。

关于参数SESSION_CACHED_CURSORS需要注意以下几点:

① Oracle会用LRU算法来管理这些已缓存的会话游标(从会话游标的dump文件中可以证实这一点),所以即便某个Session以Soft Closed状态缓存在PGA中的会话游标的总数己经达到了SESSION_CACHED_CURSORS所设置的上限也没有关系,LRU算法依然能够保证那些频繁反复执行的SQL所对应的会话游标的缓存命中率要高于那些不频繁反复执行的SQL。

① 在Oracle 11gR2中,一个会话游标能够被缓存在PGA中的必要条件是该会话游标所对应的SQL解析和执行的次数要超过3次。Oracle这么做的目的是为了避免那些执行次数很少的SQL所对应的会话游标也被缓存在PGA里,这些SQL很可能只执行一次而且不会重复执行,所以把这些执行次数很少的SQL所对应的会话游标缓存在PGA中是没有太大意义的。可以使用如下的SQL语句查询缓存在当前系统中的所有会话游标:

1 SELECT D.INST_ID, D.SQL_ID,D.SQL_TEXT,D.SID,D.USER_NAME,D.HASH_VALUE FROM GV$OPEN_CURSOR D WHERE D.CURSOR_TYPE='SESSION CURSOR CACHED';
     

下面给出一个会话游标缓存的示例:

 1 [email protected] > alter system flush shared_pool;--生产库慎用
 2
 3System altered.
 4
 5--开始第1次执行
 [email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
 7
 8no rows selected
 9
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
11
12  COUNT(*)
13----------
14        14
15
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
17
18no rows selected
19
20--开始第2次执行:
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
22
23  COUNT(*)
24----------
25        14
26
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
28
29no rows selected
30
31--开始第3次执行:
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
33
34  COUNT(*)
35----------
36        14
37
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
39
40SQL_ID        CURSOR_TYPE
41------------- ----------------------------------------------------------------
429r01dt51f46tf DICTIONARY LOOKUP CURSOR CACHED
43从结果可以看到,虽然已经缓存到PGA中了,但是类型为“DICTIONARY LOOKUP CURSOR CACHED”,并不是“SESSION CURSOR CACHED”,所以下面开始第4次执行:
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
45
46  COUNT(*)
47----------
48        14
49
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
51
52SQL_ID        CURSOR_TYPE
53------------- ----------------------------------------------------------------
549r01dt51f46tf SESSION CURSOR CACHED
55
[email protected] > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf';
57
58VERSION_COUNT EXECUTIONS PARSE_CALLS      LOADS
59------------- ---------- ----------- ----------
60            1          4           3          1
61
62--从结果可以看到,在SQL语句“SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;”第4次执行完毕后,Oracle已经将其对应的会话游标缓存在当前会话的PGA中了,而此时缓存的会话游标的类型为“SESSION CURSOR CACHED”。下面开始第5次执行:
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
64
65  COUNT(*)
66----------
67        14
68
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
70
71SQL_ID        CURSOR_TYPE
72------------- ----------------------------------------------------------------
739r01dt51f46tf SESSION CURSOR CACHED
74
[email protected] > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf';
76
77VERSION_COUNT EXECUTIONS PARSE_CALLS      LOADS
78------------- ---------- ----------- ----------
79            1          5           3          1
     

从结果看出,缓存的会话游标的类型依然为“SESSION CURSOR CACHED”,不再改变。

(五)会话游标的dump文件

会话游标的dump文件可以通过Level值为3的errorstack得到,获取过程如下所示:

1SELECT COUNT(*) FROM SCOTT.EMP;--执行5次,让其缓存在PGA中
2ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3';
3SELECT COUNT(*) FROM SCOTT.EMP;
4ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK OFF';
5SELECT VALUE FROM V$DIAG_INFO;