用共享游标提升 MSSQL 性能

　　Boost SQL Performance with cursor_sharing
　　
　　 要害词：cursor_sharing
　　
　　概述
　　本文 阐述在Oracle8i Release 2和Oracle9i中 加强的游标共享设施 。这些 加强 性能被一个新的参数cursor_sharing操纵 。
　　cursor_sharing的 目标便是 普及没有 使用绑定变量(bind vvariable)的 利用程序服务器的性能 。
　　
　　需求 cursor_sharing
　　本段解释为何 利用程序不 使用绑定变量(bind variables)会带来性能问题 。
　　
　　 利用程序 反复执行 类似的SQL语句
　　 使用Oracle数据库治理他(她)们的数据的 利用程序必须 使用SQL语句 拜访/ 批改数据库 。这些SQL语句 可以是由一个 利用程序 使用OCI, OCCI, JDBC, PL/SQL等直接产生的，也是 可以是 使用 其余工具和库(例如：dbms_sql) 直接产生的 。
　　依据不用的 利用类型，通常一个 利用程序都为最终消费者提供了一个固定的 性能 集中，例如，一个人力资源 利用程序可能会提供一些像添加一个新雇员， 批改一个雇员的个人信息等 性能 。最终这些 性能 使用SQL 拜访和/或 批改数据 。由于 利用程序 反复地执行这些 性能，一个 利用和Oracle数据库的交互是由 类似的SQL语句的 反复执行组成的 。
　　
　　SQL调用的步骤
　　为执行一个SQL语句，客户端 可以 使用 使用不同的接口 。例如，通过OCI接口，客户端 缔造一个语句句柄(statement handle)， 而后perpare这个语句，绑定，定义和执行这个语句句柄，或者，SQL语句也 可以通过一个PL/SQL过程被执行 。
　　依照客户端接口，Oracle数据库向来都 使用固定的步骤(默许)：
　　1. 打开一个游标 － 消费者游标是一个和SQL语句 有关的所有消费者状态的句柄，像执行内存，共享游标 引用，消费者游标的目前状态等等 。
　　2. 解析一个SQL语句到 打开的消费者游标中 －使SQL语句和消费者游标关联；它也 构建了一个共享游标，对应于SQL语句的解析 格局 。在一些状况下，共享游标也 可以作为解析的一 部分被校对和优化 。解析，校对和优化SQL语句的过程通常是十分 消费CPU 工夫，内存和衔接资源的 。
　　3. 如有需求，绑定变量 － 给Oracle提供SQL语句中绑定变量的数据类型，大小和值等必要的信息 。
　　4. 校对优化共享游标，如果还没有做这项工作的话 。
　　5. 执行消费者游标 － 这一步真正 实现语句执行的工作，依据语句的复杂程度 消费CPU和会话内存(session memory) 。
　　 留神，解析，校对和优化(在本文中统称为编译)组成了执行一个SQL语句的 消费，而且 可以 制约数据库的容量和可测量性 。
　　
　　共享游标
　　一个典型的 反复执行 类似语句的 利用，在Oracle数据库许多针对SQL 解决 目标的优化 反复执行 。最主要的优化是共享游标，试图通过在 雷同的语句的不同执行中间共享编译 后果来 肃清编译的 消费(不是并发便是在不同的 工夫 产生) 。如下图：
　　User Session 1
　　
　　Private
　　execution
　　state
　　User Session 2
　　
　　Private
　　execution
　　state
　　Shared Cursor

　　
　　为了 可以 使用共享游标，Oracle分割语句执行状态到共享游标中，而且在实例中预 解决 。共享游标是编译的 后果并包括了执行 方案；它在缓存在共享池中 。每个执行该语句的会话有一个预执行状态的私有拷贝，如消费者游标，运行时变量值等 。
　　在解析阶段(上面提到的第2步)，Oracle首先查找一个已经存在的 可以被消费者会话共享的共享游标 。Oracle把查找分为两步：基于SQL文本的检索，找到 雷同SQL文本 缔造的游标，依据 其余 标准 取舍适当的游标，如优化模式， 拜访的 根本对象等 。如果一个 可以共享的游标被找到，并不需求编译，这个 解决成为软解析(soft parse) 。不然，编译SQL语句 缔造新的共享游标，这个 解决成为硬解析(hard parse) 。
　　当被 利用程序 使用的大多数语句 可以共享同样的游标 集中时，大多数解析变成为软解析，进而 普及数据库服务器的 威力/吞吐量(缩减了内存和CPU的 使用)，响应 工夫(削减了解析阶段所 使用的 工夫)和可测量性(削减了闭锁衔接(latch connection) ) 。
　　
　　为何游标不是共享的？
　　 假如 其余的因素是 雷同的，如可配置的实例/会话/事务等级参数， 实际上，如果在同样的行/对象上执行同样的操作， 使用同样的 方案，语句S1和S2的游标 可以被共享 。然而要计算和找出这些游标是十分 困苦的，这样做也可能 对消共享游标带来的 好处 。 因此，Oracle游标共享 标准规定在所有的状况下默许都不共享游标，除非它们被设计得很高效 。从8i Release 2开始，如果S1和S2都是文本的而且不少的 其余条件都 雷同(对象名被转换成为同样的 根本对象，会话中语句的优化模式 雷同，等等)，它们 可以共享同一个游标 。
　　当 利用程序在语句中 使用文字标量 代替绑定变量时就会招致一个游标共享的问题 。如 利用程序最终产生的语句只不过在文字标量上有一些不同，甚至语句都是 雷同的 。如，一个 利用程序没有 使用绑定变量， 可以 假如在不同的时偶尔不同的会话中有下面两个语句：
　　INSERT INTO T VALUES(1, ‘foo’, 4)
　　INSERT INTO T VALUES(2, ‘bar’, 7)
　　由于这两个语句文本上并不 雷同，它们最终产生不同的游标 。
　　有几种状况下 利用程序可能不会 使用绑定变量：
　　l 用文字标量很容易就写出一个SQL语句，特殊是 使用了一些工具
　　l 老的Oracle关系数据库不 支撑绑定变量(至少是没有共享游标的 好处，从Oracle7才开始 使用它们)，已有的 利用程序要求作一些代码 晋级的工作 。
　　l 其余所有的数据库供给商都不 支撑绑定变量， 即便有语法也不 雷同； 因此， 使用特定的Oracle语法/ 特点会使 利用程序失去与 其余数据的兼容性 。
　　l 如果一个语句 使用绑定变量，那么它就向来 使用 雷同的执行 方案 。如果不同的绑定变量会有不同的优化 方案就可能产生问题，如，考量下面的语句：
　　SELECT * FROM T1, T2 WHERE (T1.N <= 100) AND (T1.N1=T2.N2)
　　SELECT * FROM T1, T2 WHERE (T1.N <= 500) AND (T1.N1=T2.N2)
　　依据值在字段N中的 分布，两个语句可能有不同的优化 方案 。 因此 使用绑定变量：
　　SELECT * FROM T1, T2 WHERE (T1.N <= :X) AND (T1.N1=T2.N2)
　　将会由于一些绑定变量的值招致低效的优化 。这时 可以强制 使用文字标量 接替绑定变量 。
　　
　　概念
　　在开始解决 方案之前，这里先 澄清一些 根本概念 。
　　 类似语句(Similar statements)
　　如果任何两个语句只不过文字标量不 雷同， 可以认为它们是 类似的 。
　　这 单纯是一个语义学上的 标准 。
　　例如：以下的语句是 类似的
　　INSERT INTO T VALUES(1, ‘foo’, 4)
　　INSERT INTO T VALUES(2, ‘bar’, 7)
　　INSERT INTO T VALUES(5, ‘alpha’, 11)
　　INSERT INTO T VALUES(10, ‘kappa’, 17)
　　最优共享语句(Optimally sharable statements)
　　 类似语句可能有也可能没有同样的执行 方案 。例如，下面两个语句就有 雷同的执行 方案：
　　INSERT INTO T VALUES(1, ‘foo’, 4)
　　INSERT INTO T VALUES(2, ‘bar’, 7)
　　在本文中，这些语句叫做最优共享语句 。
　　 因此：
　　最优共享语句是 存在 雷同最优 方案的 类似语句 。
　　同样也 象征着最优共享语句 可以共享 雷同的游标，而不会对执行成本有任何的影响 。
　　非最优化共享语句(Sub-optimally sharable statements)
　　另一面，如下面两个语句：
　　SELECT * FROM T1, T2 WHERE (T1.N <= 100) AND (T1.N1 = T2.N2)
　　SELECT * FROM T1, T2 WHERE (T1.N <= 500) AND (T1.N1 = T2.N2)
　　依据(N<=100)和(N<=500)的行数，值在字段N中的 分布，在N, N1或N2上索引的可用性等状况，可能有不同的最优 方案 。例如，第一个语句可能 使用一个在T1上的索引，而第二个语句可能是在T1上做全表扫描 。或者第一个语句可能是作一个哈希衔接而第二个语句可能是做一个嵌套循环衔接 。这些语句响应地 可以当作是非最优化共享语句， 因此：
　　非最优化共享语句是可能 存在不同最优 方案的 类似语句 。
　　同样也 象征着如果非最优化共享语句共享同样的游标，那么在执行效率上可能会存在损失 。
　　最优共享与非最优共享语句
　　最优共享和非最优共享语句的区别并不 单纯是在语义上的 。它依赖于下面的因素：
　　l 文字标量在语句中的位置(例如，是在VALUES子句中还是在WHERE子句中)
　　l 可用的 拜访路径(例如，索引的存在)
　　l 如果一个文字标量浮现在一个包括字段的谓词中(如，N<=100用到了在字段N上的统计值的可用性)，则取决于数据 分布(统计值)和它的可用性
　　l 优化器的算法 使用
　　非共享语句
　　由于 使用同样的游标会产生不正确的 后果，会浮现 类似语句不能共享同一个游标的状况 。这些 类似语句 象征着不同的 事件，或者在执行期间做了 彻底不同的事 。下面的语句 形容了这丝毫：
　　SELECT * FROM T ORDER BY 1,4
　　SELECT * FROM T ORDER BY 2,3
　　在这个例子中，文字标量1,2,3和4指的是 取舍表项中的 名目 。这些语句叫做非共享语句 。 因此有：
　　非共享语句是不能共享同样的执行 方案的 类似语句 。
　　这里最主要的丝毫便是：如果两个非共享语句共享同样的游标，它们其中一个就会得到 舛误的 后果 。
　　
　　解决 方案
　　这一节 形容通过cursor_sharing参数所提供的解决 方案
　　概述
　　新参数cursor_sharing 惟独有可能就同意 类似语句共享游标 。依据参数的值， 类似语句 可以被强制共享 雷同的游标(有可能会 使用非最优 方案)，或者共享 雷同游标而不危及底层执行 方案的安全 。
　　 无论设置cursor_sharing为SIMILAR还是FORCE，Oracle都首先查找 彻底 雷同的语句文本的游标 。如果没有发现，Oracle查找 类似语句文本的游标 。
　　用法
　　参数：cursor_sharing
　　从Oracle 8i Release 2开始，一个新的动态参数cursor_sharing被引入 。在8i中，参数 可以有两个不同的值FORCE和EXACT 。从9i开始，一个新的值SIMILAR被加入 。
　　默许值是EXACT 。它只同意 彻底 雷同文本的语句共享一个游标 。这是早期版本的行为 。
　　SIMILAR参数值使 类似语句共享同样的游标，而不危机执行 方案的安全 。例如：惟独最优共享语句共享游标 。
　　将参数值设为FORCE会 胁迫Oracle对 类似语句共享游标，但存在非最优执行 方案的风险，如，最优共享和非最优共享语句会共享同一个游标 。惟独在非最优执行 方案的风险被共享游标的性能 普及超过的时候，该参数才 可以被设置为FORCE，例如：如果太多的非最优共享语句的硬解析招致了严峻的性能问题 。
　　SQL语句
　　一个新的标记CURSOR_SHARING_EXACT在被SQL语句中被用于在语句级别操纵游标共享 。这个标记 类似于初始化参数cursor_sharing被设置为EXACT，并屏蔽了已经设定的初始化参数的值，也便是：它招致语句共享采纳准确匹配构建的游标 。
　　ALTER SYSTEM 和 ALTER SESSION 命令同意新参数cursor_sharing的设置和转变 。语法如下面的 模式：
　　ALTER SYSTEM SET cursor_sharing = {FORCE | SIMILAR | EXACT}
　　ALTER SYSTEM SET cursor_sharing = {FORCE | SIMILAR | EXACT}
　　动态视图
　　下面的四个动态视图显示了绑定变量的信息：
　　l GV$SQL_BIND_METADATA
　　l V$SQL_BIND_METADATA
　　l GV$SQL_BIND_DATA
　　l V$SQL_BIND_DATA
　　这些视图也包括了内部绑定变量的信息 。内部绑定变量 可以依据视图[G]V$SQL_BIND_DATA中的字段SHARED_FLAG2与消费者绑定变量 划分，内部绑定变量的标记值为256 。
　　只参看内部绑定变量的行，消费者 可以考量下面的语句：
　　SELECT * FROM V$SQL_BIND_DATA WHERE BITAND (SHARED_FLAG2, 256) =256
　　主要利益与折衷
　　考量一个没有 使用绑定变量的 利用，该 利用 反复地 使用 类似语句，大多数的执行都将招致硬解析 。
　　一个不 使用绑定变量的典型 利用可能会有各 品种型的语句：最优共享，非最优共享安和非共享 。关于最优共享语句，共享游 表显而易见是有 好处；非共享语句不能共享同样的游标 。
　　关于非最优共享语句没有一个 容易的答案：共享游标与猎取最优 方案的 比较是硬解析的系统 消费与强制 使用 雷同执行 方案后的性能退化中间的折衷 。 因此，依据系统负载， 利用 特色，资源 制约等，正确的答案是不同的 。这也是Oracle 提供为cursor_sharing提供两个不同的值SIMILAR和FORCE，并把决定权留给消费者的缘由 。SIMILAR是更 传统的 取舍，它仅仅使最优可共享语句共享游标 。采纳FORCE，最优共享和非最优共享语句都被强制共享游标， 后果便不可预测，由于游标可能被共享但执行 方案的性能也减低了 。 因此，由于硬解析造成性能有十分大的影响而且非最优共享语句占十分大的百分比的状况下， 使用FORCE是有 意思的 。另外一个考量的 模式是：在采纳FORCE 之前先尝试SIMILAR 。

　　当cursor_sharing采纳 类似语句共享游标的时候，硬解析转换为软解析 。 留神，由于推断语句 类似性的附加成本，软解析比已 使用绑定变量的 利用的软解析(用绑定变量在内部替换文字标量) 花费要高昂一些 。然而， 彻底 保留在CPU内部，内存和锁竞争任然需求考量 。
　　关于cursor_sharing，Oracle任然首先查找一个准确匹配 。惟独当一个 彻底 雷同文本语句的游标没有找到时，Oracle查找一个 类似语句的游标 。这样做是为了确保当遇到 雷同的没有硬编码文字标量的SQL文本的时候，不会对性能有所影响 。
　　由于在寻觅游标之前置换文字标量， 其余的Oracle优化，像session_cached_cursors和cursor_space_for_time 可以容易地和cursor_sharing整合 。例如，将cursor_sharing和session_cached_cursors设置为一个 正当的值，在文字标量被内部绑定变量置换之后， 类似语句就 可以 使用缓冲 打开游标 。
　　主要 好处的概要如下：
　　l 利用程序不需求做转变
　　l 对已经 使用绑定变量的语句没有副作用
　　l 使用SIMILAR， 时常共享的游标不会影响执行 方案
　　l 作为最终的 步骤，所有的 类似语句都 可以用FORCE强制共享游标
　　忠告
　　混合语句(Mixed statements)
　　混合语句是既有绑定变量也有硬编码文字标量的语句 。如：
　　INSERT INTO T VALUES(5, ‘alpha’, :X)
　　如果是 使用Oracle7 OCI的客户端，混合的 类似语句不会通过cursor_sharing共享游标；在更新的版本中 可以共享游标(从Oracle8 OCI开始) 。实际上，这也和样 实用于在服务器上的PL/SQL存储过程的SQL语句，由于在服务器上的PL/SQL 使用了较老的客户端接口 。
　　通过PL/SQL的静态SQL
　　Cursor_sharing关于在PL/SQL中的静态(嵌入)SQL没有任何影响 。
　　存储概要(Stored outlines)
　　任何存储概要 构建都没有将cursor_sharing设置为FORCE或SIMILAR，当cursor_sharing被设置时(FORCE或 SIMILAR)速度并不会有 晋升 。那是由于存储概要被SQL文本索引，目前的cursor_sharing实现 批改语句文本 。为了 使用带有游标共享的存储概要，它们必须 使用create_stored_outlines参数重建(而且不要 使用 缔造概要语句) 。
　　
　　 消费(Overhead)
　　 使用FORCE或SIMILAR参数，查找为 类似语句 缔造的游标存在一个 消费 。像前面提及的，这包括：
　　l 用原始语句文本查找游标
　　l 用内部绑定变量替换文字标量，而且基于新文本的查找
　　当共享游标工作的时候，这个 消费并不主要，由于大量的硬解析会被 花费很小的软解析替换 。然而，当游标共享没有显而易见的添加的时候，这些 消费会对性能产生负面的影响 。在三种状况下它会 产生时：
　　1. 利用程序没有 使用绑定变量，公布 雷同的语句，而且没有 类似语句
　　如果 利用程序向来用同样的文字标量硬编码执行同样的语句，它会 产生 。这样的 利用程序默许 使用软解析，而且设置游标共享为FORCE或SIMILAR，会使软解析更高昂 。
　　
　　针对这样一个 利用的状况，有一个 秘诀 可以 使用：在共享池暖启动以后，也便是，在所有有 雷同文字标量的语句都被编译了以后，cursor_sharing 可以被设置为FORCE或SIMILAR 。这种状况下，Oralce会 立即发现哪些语句的游标，幸免额外的 消费 。
　　
　　如果在一个 利用中，有一些语句 使用同样的文字标量而有一些语句转变文字标量，这十分的有用 。
　　
　　2. 利用程序公布不同 构造的语句， 因此没有任何 类似语句
　　这样的 利用默许 使用硬解析，设置cursor_sharing为FORCE或SIMILAR会使硬解析更高昂一些 。
　　
　　3. 没有 使用绑定变量的 利用，设置cursor_sharing为SIMILAR，大 部分 类似语句被次最优化共享
　　这样的 利用默许采纳硬解析，将cursor_sharing设为FORCE，大量 使用软解析 。设置cursor_sharing为SIMILAR，将使硬解析更高昂一些 。
　　
　　 使用FORCE
　　 使用FORCE可能会招致一个十分坏的执行 方案被 使用 。在有些状况下，坏的执行 方案和好的执行 方案中间的差别是十分主要的，如，DSS环境 。 因此，Oralce不推举在这种状况下 使用FORCE 。

　　何时 使用游标共享？
　　这一段作一些关于 使用游标共享的 提议 。
　　 使用cursor_sharing=SIMILAR
　　像早先提及的，cursor_sharing并不会伤害 使用绑定变量编写的 利用程序的性能 。设置cursor_sharing为SIMILAR，在大多数状况下， 普及没 使用绑定变量的 利用程序的性能(在前一段提及的两个状况例外) 。 因此，如果没有 使用绑定变量的 利用程序的性能问题，将 cursor_sharing设置为SIMILAR风险最小 。 利用中 使用了绑定变量的 部分 接续共享游标，那些 使用硬编码文字标量的 部分从一些游标共享中获益 。
　　
　　cursor_sharing=SIMILAR是不是会 普及 利用程序性能依赖于下面问题的答案：
　　l 性能低下是由于十分大量的硬解析造成的吗？
　　这 可以通过监控几个指标来推断，如硬解析的 均匀数，解析数/执行数， 均匀响应 工夫，会话的期待事件等等 。
　　
　　l 在共享池中的 使用硬编码文字标量的 类似语句是不是众多？
　　 可以通过动态视图v$sql或v$sqlarea查看 。
　　如果上面两个问题的答案都是 确定的，那么cursor_sharing很可能会 普及性能 。
　　
　　 使用cursor_sharing=FORCE
　　在下面的状况下 可以考量 使用cursor_sharing=FORCE：
　　l 次最优化共享语句的比率十分高，使SIMILAR的作用不大
　　没有很轻松的 步骤找出次最优化语句的比率，除了测试所有的语句 。另外一种 模式是设置cursor_sharing=SIMILAR；如果硬解析是由于没有 类似语句没有 连续的共享游标， 而后有许 频繁最优化语句，FORCE是唯一的解决 方案 。
　　
　　l 利用有硬编码文字标量，而且在执行 工夫上有一些退化， 胁迫 类似语句 使用 雷同游标
　　
　　当 使用SIMIlAR没有协助的时候，考量FORCE作为最终的 目的是有 用处的 。
　　
　　何时应该不 使用cursor_sharing?
　　早先提及的(在“忠告”一节中)，有三种状况， 使用cursor_sharing会有 害处 。那些状况下，没有任何 可以 使用某些cursor_sharing的值共享游标的 类似语句，而且 使用它只会添加解析的 消费 。
　　另一个要记住的 事件是：cursor_sharing为面对一个 使用了文字标量的 利用程序的DBA提供了一个解决 方案 。然而，它并不是 代替 使用绑定变量编写 利用程序，也 可以采纳Oracle提供的 其余优化 。例如， 利用程序 可以 维持频繁执行的解析语句在 打开的游标中，而且在需求的时候，只不过执行它们 。这样的优化是基于深度的 利用程序 常识，而且不能被cursor_sharing匹配 。
　　
　　 论断
　　cursor_sharing的 使用 可以解决有硬解析激发的性能问题，如果 利用程序没有 使用绑定变量 。基于 利用和数据库 特点以及系统资源，参数应该被 理智地设置 。
　　
　　附录：一些性能测量
　　这一段 形容了用Oracle 8i Release 2做的一个试验，验证cursor_sharing 。
　　 形容
　　这个试验的 目标是做一个 根本的验证 。服务器的最大吞吐量被一些客户端 反复地公布一个单一语句测量 。试验做了三次，采纳下面的 特点：
　　1. 只 使用绑定变量
　　有两个 目标： 构建一个基线；确保每个语句不会由于 使用cursor_sharing影响性能 。
　　
　　2. 只 使用文字标量，每个文字标量都有不同的文字
　　公布 类似语句， 期冀从cursor_sharing猎取最大的收益 。
　　
　　3. 每个文字标量都 使用 雷同的文字
　　并不 期冀从cursor_sharing猎取收益，相反， 期冀性能恶化 。理由是测试cursor_sharing软解析的 消费 。
　　
　　在每种状况下只测量解析吞吐量(每秒钟的解析量) 。
　　
　　 后果
　　下面是测试 后果：

　　

Type	cursor_sharing=EXACT	cursor_sharing=FORCE
Binds only	2650	2650
Similar statements	860	2500
1 statement with literals	g3300	2600