Oracle中的游标、硬解析、软解析、软软解析、解析失败

0    249    1

Tags:

👉 本文共约23836个字,系统预计阅读时间或需90分钟。

一、 游标的分类及共享游标

游标(Cursor)是Oracle数据库中SQL解析和执行的载体,它可以分为共享游标(Shared Cursor)和会话游标(Session Cursor)。共享游标可以细分为父游标(Parent Cursor)和子游标(Child Cursor),可以通过视图V$SQLAREA和V$SQL来查看当前缓存在库缓存(Library Cache)中的父游标和子游标,其中V$SQLAREA用于查看父游标,V$SQL用于查看子游标。父游标和子游标的结构是一样的,它们都是以库缓存对象句柄的方式缓存在库缓存中,Namespace属性的值均为CRSR。由于子游标对应的库缓存对象句柄的Name属性值为空,所以,只能通过父游标才能找到相应的子游标。综上所述,任意一个经过解析的目标SQL一定会同时对应两个共享游标(Shared Cursor),一个是父游标(Parent Cursor),另一个则是子游标(Child Cursor),父游标会存储该SQL的SQL文本,而该SQL真正的可以被重用的解析树和执行计划则存储在子游标中。

Oracle中游标的分类如下所示:

Oracle中的游标、硬解析、软解析、软软解析、解析失败

图 3-13 Oracle中的游标分类

父游标和子游标的对比如下表所示:

Oracle中的游标、硬解析、软解析、软软解析、解析失败

二、 会话游标

(一)会话游标的含义

会话游标(Session Cursor)是当前会话(Session)解析和执行SQL的载体,即会话游标用于在当前会话(Session)中解析和执行SQL,会话游标缓存在PGA中(Shared Cursor是缓存在SGA的库缓存里)。会话游标是以哈希表的方式缓存在PGA中,在缓存会话游标的哈希表的对应Hash Bucket中,Oracle会存储目标SQL对应的父游标的库缓存对象句柄地址,所以,Oracle可以通过会话游标找到对应的父游标,进而就可以找到对应子游标中目标SQL的解析树和执行计划,然后Oracle就可以按照这个解析树和执行计划来执行目标SQL了。

会话游标在目标SQL的执行过程中实际上起到了一个承上启下的作用,Oracle依靠会话游标来将目标SQL所涉及的数据从Buffer Cache的对应数据块读到PGA里,然后在PGA里做后续的处理(如排序、表连接等),最后将最终的处理结果返回给用户,所以,会话游标是当前会话解析和执行SQL的载体的原因。

关于会话游标,需要注意以下几点:

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

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

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

Oracle中的游标、硬解析、软解析、软软解析、解析失败

(二)会话游标的分类

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

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

Oracle中的游标、硬解析、软解析、软软解析、解析失败

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

(三)会话游标的属性

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

表 3-21 游标的属性

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

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

和会话游标相关的有2个重要参数,分别为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,其实是20),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。

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

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

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中

SELECT COUNT(*) FROM SCOTT.EMP;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle中的游标、硬解析、软解析、软软解析、解析失败后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部