专栏首页小麦苗的DB宝专栏【DB笔试面试578】在Oracle中,什么是会话游标?会话游标和共享游标的区别有哪些?会话游标分为哪几类?

【DB笔试面试578】在Oracle中,什么是会话游标?会话游标和共享游标的区别有哪些?会话游标分为哪几类?

题目部分

在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程序员面试笔试宝典》,作者:李华荣。

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试396】在Oracle中,声明游标时指定的SELECT语句必须带有下边选项中的哪一个子句()

    在Oracle中,当需要使用显式游标更新或删除游标中的行时,声明游标时指定的SELECT语句必须带有下边选项中的哪一个子句()

    小麦苗DBA宝典
  • 【DB笔试面试577】在Oracle中,游标有哪几类?

    游标(Cursor)是Oracle数据库中SQL解析和执行的载体,它可以分为共享游标(Shared Cursor)和会话游标(Session Cursor)。共...

    小麦苗DBA宝典
  • 【DB笔试面试580】在Oracle中,什么是High Version Count?

    一个父游标下对应的子游标个数被称为Version Count,每个子游标对应一个执行计划。对于一个特定的游标有多少个版本(Version Count)就属于高版...

    小麦苗DBA宝典
  • 父游标、子游标及共享游标

            游标是数据库领域较为复杂的一个概念,因为游标包含了shared cursor和session cursor。两者有其不同的概念,也有不同的表现形...

    Leshami
  • mongo中游标

        mongo中我们常用的查询方式db.collection.find()方法其实返回的就是游标,只不过我们并未给返回的游标分配变量,我们所看到的的查询数据...

    莫问今朝
  • Oracle的静态游标与动态游标

    我们在写Oracle的存储过程里面,经常会用到游标,Oracle里面的游标分为静态游标和动态游标。今天我们在说一下分别有什么不同。

    Vaccae
  • ​【数据库】MySQL进阶三、游标简易教程

    【数据库】MySQL进阶三、游标简易教程 mysql游标简易教程 从mysql V5.5开始,进行了一次大的改变,就是将InnoDB作为默认的存...

    Java帮帮
  • 玩转Mysql系列 - 第19篇:游标详解

    此时我们需要使用游标,通过游标的方式来遍历select查询的结果集,然后对每行数据进行处理。

    路人甲Java
  • MySQL从删库到跑路_高级(九)——存储过程

    游标的设计是一种数据缓冲区的思想,用来存放SQL语句执行的结果。游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 尽管游标能遍历结果中的所有行,...

    良月柒
  • 有坑勿踩(二): 关于游标

    聊一聊一个最基本的问题,游标的使用。可能你从来没有注意过它,但其实它在MongoDB的使用中是普遍存在的,也存在一些常见的坑需要引起我们的注意。

    MongoDB中文社区

扫码关注云+社区

领取腾讯云代金券