Oracle共享游标有哪些

共计 4450 个字符，预计需要花费 12 分钟才能阅读完成。

本篇内容介绍了“Oracle 共享游标有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

首先，明确一个概念，这里讨论的游标主要是共享游标（shared cursor），跟 SQL 语句中定义的游标（session cursor）不是一个概念。

共享游标是用户提交 SQL 或 PL/SQL 程序块到 Oracle 的 share pool 之后，在 library cache 中生成的一个可执行对象，这个对象我们称之为游标（cursor）。而 SQL 定义游标则是 SELECT 语句产生的多行结果集，需要声明、打开、提取、关闭。
游标定义与分类

游标包括 shared cursor 和 session cursor：
shared cursor 即是共享游标，是 SQL 语句在游标解析阶段生成获得的，是位于 library cache 中的 sql 或匿名的 pl/sql 等。其元数据被在视图 V$sqlarea 与 v$sql 中具体化。如果 library cache 中的父游标与子游标能够被共享，此时则为共享游标。父游标能够共享即为共享的父游标，子游标能够共享即为共享的子游标。

session cursor 即系统为用户分配缓存区，用于存放 SQL 语句的执行结果。用户可以通过这个中间缓冲区逐条取出游标中的记录并对其处理，直到所有的游标记录被逐一处理完毕。session cursor 指的跟这个 session 相对应的 server process 的 PGA 里 (准确的说是 UGA) 的一块内存区域 (或者说内存结构) 即其主要特性表现在记录的逐条定位，逐条处理。session cursor 的元数据通过 v$open_cursor 视图来具体化，每一个打开或解析的 SQL 都将位于该视图。

游标的生命周期

shared cursor 生命周期

1）包含 vpd 的约束条件：SQL 语句如果使用的表使用了行级安全控制，安全策略生成的约束条件添加到 where 子句中。
2）语法、语义、访问权限检查：检查 SQL 语句书写的正确性，对象存在性，用户的访问权限。
3）父游标缓存：将该游标（SQL 语句）的文本进行哈希得到哈希值并在 library cache 寻找相同的哈希值，如不存在则生存父游标且保存在 library cache 中，按顺序完成后续步骤。如果此时存在父游标，则进一步判断是否存在子游标。若存在相同的子游标，则直接调用其子游标的执行计划执行该 SQL 语句，否则转到下一步进行逻辑优化。
4）逻辑优化：使用不同的转换技巧，生成语义上等同的新的 SQL 语句（SQL 语句的改写），一旦该操作完成，则执行计划数量、搜索空间将会相应增长。其主要目的未进行转换的情况下是寻找无法被考虑到的执行计划。
5）物理优化：为逻辑优化阶段的 SQL 语句产生执行计划，读取数据字典中的统计信息以及动态采样的统计信息，计算开销，开销最低的执行计划将被选中。
6）子游标缓存：分配内存，生成子游标（即最佳执行计划），与父游标关联。可以在 v$sqlarea, v$sql 得到具体游标信息，父子游标通过 sql_id 关联。

对于仅仅完成步骤 1 与 2 的 SQL 语句即为软解析，否则即为硬解析。SQL 语句在 Oracle 中的执行机理大概也类似这个，具体可见“Oracle SQL 语句执行流程与顺序原理解析”。

共享游标包括父游标和子游标。

父游标是在进行硬解析时产生的，父游标里主要包含两种信息：SQL 文本以及优化目标(optimizer goal)，首次打开父游标被锁定，直到其他所有的 session 都关闭该游标后才被解锁。当父游标被锁定的时候是不能被 LRU 算法置换出 library cache，只有在解锁以后才能置换出 library cache，此时该父游标对应的所有子游标也同样被置换出 library cache。v$sqlarea 中的每一行代表了一个 parent cursor，address 表示其内存地址。

子游标在发生硬解析时，在产生父游标的同时，则跟随父游标会产生相应的子游标，此时 V$SQL.CHILD_NUMBER 的值为 0。如果存在父游标，由于不同的运行环境，此时同样会产生新的子游标，新子游标的 CHILD_NUMBER 在已有子游标基础上以 1 为单位累计。子游标包括游标所有相关信息，如具体的执行计划、绑定变、OBJECT、权限、优化器设置等。子游标随时可以被 LRU 算法置换出 library cache，当子游标被置换出 library cache 时，oracle 可以利用父游标的信息重新构建出一个子游标来，这个过程叫 reload。v$sql 中的每一行表示了一个 child cursor，根据 hash value 和 address 与 parent cursor 关联。child cursor 有自己的 address，即 v$sql.child_address。
确定一个游标的三个主要字段：address、hash_value 和 child_number。sql_id 可以唯一确定一个父游标，sql_id、child_number 唯一确定一个子游标。

session cursor 生命周期：
session cursor 需要从 UGA 中分配内存，因此有其生命周期。其生命周期主要包括：
  打开游标(根据游标声明的名称在 UGA 中分配内存区域)；
  解析游标(将 SQL 语句与游标关联，并将其执行计划加载到 Library Cache)；
  定义输出变量(仅当游标返回数据时)；
  绑定输入变量(如果与游标关联的 SQL 语句使用了绑定变量)；
  执行游标(即执行 SQL 语句)；
  获取游标(即获取 SQL 语句记录结果, 根据需要对记录作相应操作。游标将逐条取出查询的记录，直到取完所有记录)；
  关闭游标(释放 UGA 中该游标占有的相关资源，但 Library Cache 中的游标的执行计划按 LRU 原则清除，为其游标共享提供可能性)；

对于 session cursor 而言，可以将游标理解为任意的 DML，DQL 语句（个人理解，有待核实）。即一条 SQL 语句实际上就是一个游标，只不过 session cursor 分为显示游标和隐式游标，以及游标指针。由上面游标的生命周期可知，任何的游标 (SQL 语句) 都必须经历内存分配，解析，执行与关闭的过程。故对隐式游标而言，生命周期的所有过程由系统来自动完成。对所有的 DML 和单行查询 (select … into …) 而言，系统自动使用隐式游标。多行结果集的 DQL 则通常使用显示游标。

一个 session cursor 只能对应一个 shared cursor，而一个 shared cursor 却可能同时对应多个 session cursor。

共享游标举例
假设有用户 SCOTT 和 KING，两者均有表 EMP。先以 SCOTT 为例，执行如下语句：
select * from emp where empno = 7788;
SELECT * from emp where empno = 7788;
SELECT * FROM emp WHERE empno = 7788;
select * from emp where empno = 7788;

以上 4 条语句，第 1 条和第 4 条完全相同，第 1 条、第 2 条、第 3 条在大小写上有不同，查询 v$sqlarea：
select sql_id, sql_text, executions
  from v$sqlarea
 where sql_text like %empno = 7788%
  and sql_text not like %from v$sqlarea%
 
执行结果见下图，有 3 条记录，说明产生了 3 个父游标，其中一个父游标执行了 2 次。这说明，SQL 语句必须完全一致（大小写、空格回车等）才能共享，进而避免硬解析。

这 3 个父游标对应的子游标可以在 v$sql 中获得：

select sql_id,
  hash_value,
  child_number,
  plan_hash_value,
  sql_text,
  executions
  from v$sql
 where sql_text like %empno = 7788%
  and sql_text not like %from v$sql%

执行结果见下图，可见生成父游标时同时也生成一个以 0 为 child_number 的子游标，其 sql_id 和 hash_value 都和父游标相同。

然后使用 KING 用户，执行相同的语句。然后查询 v$sqlarea 父游标，结果仍然是 3 条记录，不过执行次数发生了变化：

再查询子游标，结果是 6 条记录，说明由于语句执行的环境不同而造成生成不同的子游标：

产生子游标的原因很多，比如上边的用户（SCHEMA）改变的例子，当然还有很多其他原因也可以导致子游标的产生，比如优化器模式的改变，或者绑定变量的窥视等，如果你想确定是由那种原因造成的，需要查看 v$sql_shared_cursor。

共享游标其他知识点

  查看语句共享可以借助两个数据字典：V$SQLAREA 和 V$SQL。V$SQLAREA 保留 SQL 语句的父游标信息，可以通过 SQL_ID 标识，其中的 VERSION_COUNT 列表示子游标的数量。V$SQL 保留 SQL 语句的子游标信息，可以通过 SQL_ID 和 CHILD_NUMBER 标识。V$SQL_SHARED_CURSOR 可以查看语句产生子游标的原因。
关于 v$sql 和 v$sqlarea 视图字段及其详解见“Oracle 高资源消耗 SQL 语句定位”。
  父游标的关键信息是 sql 文本，子游标的关键信息是执行计划和执行环境。
  硬解析通常是由于不可共享的父游标造成的，如经常变动的 SQL 语句，或动态 SQL 或未使用绑定变量等。
  解决硬解析的办法则通常是使用绑定变量来解决。
  与父游标 SQL 文本完全一致的情形下，多个相同的 SQL 语句可以共享一个父游标。
  SQL 文本、执行环境完全一致的情形下，子游标能够被共享，否则如果执行环境不一致则生成新的子游标。
  游标是可以被所有进程共享的，也就是说如果 100 个进程都执行相同的 SQL 语句，那么这 100 个进程都可以同时使用该 SQL 语句所产生的游标，从而节省了内存。每个游标都是由 library cache 中的两个或多个对象所体现的，至少两个对象：一个对象叫做父游标（parent cursor），包含游标的名称以及其他独立于提交用户的信息，从 v$sqlarea 视图里看到的都是有关父游标的信息；另外一个或多个对象叫做子游标（child cursors），如果 SQL 文本相同，但是可能提交 SQL 语句的用户不同，或者用户提交的 SQL 语句所涉及到的对象为同名词等，都有可能生成不同的子游标。因为这些 SQL 语句的文本虽然完全一样，但是上下文环境却不一样，因此这样的 SQL 语句不是一个可执行的对象，必须细化为多个子游标后才能够执行。子游标含有执行计划或者 PL/SQL 对象的程序代码块等。

“Oracle 共享游标有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！