用共享游标提升 SQL 性能

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 Privateexecutionstate User Session 2 Privateexecutionstate 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,4SELECT * 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_METADATAl V$SQL_BIND_METADATAl GV$SQL_BIND_DATAl 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的静态SQLCursor_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软解析的耗费。在每种情况下只测量解析吞吐量（每秒钟的解析量）。结果下面是测试结果：

Typecursor_sharing=EXACTcursor_sharing=FORCEBinds only26502650Similar statements86025001 statement with literalsg33002600