用共享游标提升 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
 
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)