【DB笔试面试578】在Oracle中，什么是会话游标？会话游标和共享游标的区别有哪些？会话游标分为哪几类？
- 2019 年 10 月 10 日
- 筆記
♣
题目部分
在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中会话游标的分类
在上表中需要注意的是，动态游标是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状态存在的会话游标的总数。
LHR@orclasm > show parameter open_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 65535 SELECT USERENV('SID') FROM DUAL; SELECT * FROM V$OPEN_CURSOR WHERE SID=16; SELECT * FROM V$SYSSTAT D WHERE D.NAME ='opened cursors current';
（2）参数SESSION_CACHED_CURSORS用于设定单个会话中能够以Soft Closed状态缓存在PGA中的会话游标的总数。在Oracle 10g中默认为20（注意：在官方文档中记录的该值默认为0是有误的），11g中默认为50。
LHR@orclasm > show parameter session_cached_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ session_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语句查询缓存在当前系统中的所有会话游标：
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';
下面给出一个会话游标缓存的示例：
LHR@orclasm > alter system flush shared_pool;--生产库慎用 System altered. --开始第1次执行 LHR@orclasm > 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(*)%' ; no rows selected LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > 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(*)%' ; no rows selected --开始第2次执行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > 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(*)%' ; no rows selected --开始第3次执行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > 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(*)%' ; SQL_ID CURSOR_TYPE ------------- ---------------------------------------------------------------- 9r01dt51f46tf DICTIONARY LOOKUP CURSOR CACHED 从结果可以看到，虽然已经缓存到PGA中了，但是类型为“DICTIONARY LOOKUP CURSOR CACHED”，并不是“SESSION CURSOR CACHED”，所以下面开始第4次执行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > 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(*)%' ; SQL_ID CURSOR_TYPE ------------- ---------------------------------------------------------------- 9r01dt51f46tf SESSION CURSOR CACHED LHR@orclasm > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf'; VERSION_COUNT EXECUTIONS PARSE_CALLS LOADS ------------- ---------- ----------- ---------- 1 4 3 1 --从结果可以看到，在SQL语句“SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;”第4次执行完毕后，Oracle已经将其对应的会话游标缓存在当前会话的PGA中了，而此时缓存的会话游标的类型为“SESSION CURSOR CACHED”。下面开始第5次执行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > 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(*)%' ; SQL_ID CURSOR_TYPE ------------- ---------------------------------------------------------------- 9r01dt51f46tf SESSION CURSOR CACHED LHR@orclasm > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf'; VERSION_COUNT EXECUTIONS PARSE_CALLS LOADS ------------- ---------- ----------- ---------- 1 5 3 1
从结果看出，缓存的会话游标的类型依然为“SESSION CURSOR CACHED”，不再改变。
（五）会话游标的dump文件
会话游标的dump文件可以通过Level值为3的errorstack得到，获取过程如下所示：
SELECT COUNT(*) FROM SCOTT.EMP;--执行5次，让其缓存在PGA中 ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3'; SELECT COUNT(*) FROM SCOTT.EMP; ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK OFF'; SELECT VALUE FROM V$DIAG_INFO;
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
