您现在的位置： 网页制作教程网 >> 数据库教程 >> 数据库技巧 >> 文章正文

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。

[1] [2] [3] 下一页