Oracle 11g新特性解析-自适应游标共享-技术开发专区

Oracle 11g新特性解析-自适应游标共享

作者：IT168 开心果译编辑：晓熊 2008-12-31 09:52 来源：IT168�

　　【IT168 技术文档】

　　任何SQL语句优化引擎的核心是判定获得SQL语句目标数据的最廉价和最有效的访问路径的能力，使用绑定变量可以限制需要存储在库缓存中返回相似结果集的SQL语句游标的数量。本文介绍Oracle 11g中的新特性 – 自适应游标共享 – 并说明实际情况中该如何使用它，以及如何限制带有绑定变量的SQL语句的硬解析，同时提高这些语句的执行效率。

　　正如我过去做老师时告诉学生的那样，我有一个好方法可以避免数据库性能降低，说来也很简单，就是要求应用程序开发人员不要在数据库运行任何SQL语句，但这的确引来不少人的笑声和嘲笑，还是回到现实中来吧，讨论一下建立有效SQL语句的途径，我认为没有什么标准可供借鉴，但构造不佳的SQL语句迟早会导致联机事务处理(OLTP)系统、决策支持系统(DSS)或混合数据库表现不佳。

　　“偷窥”绑定变量：这不是作弊

　　幸运的是，Oracle数据库为我们提供了一些很好的工具，用以确定需要改善性能的SQL语句，并为提升它们的性能提供建议，这个方法的核心是当确实需要一个新的执行计划时，我们可以修改基于成本的SQL优化器解析SQL语句的方式。当然，当一条语句第一次执行时必须硬解析，这样优化器可以确定获取所需数据的非常好的路径，因为解析是一个相对费时的操作，因此，DBA通常会限制存储在库缓存中的唯一性游标的数量，特别是在联机事务处理环境下，相同的语句可能会被执行成百上千次，它们为用户会话返回相似的结果集。

　　设置有效的游标共享最简单的方法就是按照应用程序工作量的需要为CURSOR_SHARING初始化参数设定合适的值，将这个参数的值设为SIMILAR告诉优化器当SQL语句完全相同，除了谓词部分外，可以使用游标共享，执行计划提供相等或更优的性能，同样，将其值设为CURSOR_SHARING时，不论是否存在更好的执行计划，告诉优化器强制共享游标，当SQL语句包含绑定变量时，有极好的机会提供这方面的性能优势，但当优化器在不知道是什么值来填充产生的游标时，怎样才能构建一个有效的执行计划呢?

　　早在Oracle 9i就进行了一些尝试，为了克服可能出现的非非常好的的执行计划，引入了绑定变量偷窥，顾名思义，当一个包含绑定变量的SQL语句首次执行时，Oracle会快速查看这些绑定变量的一个真实值，以便构建一个非常好的的执行计划，这个方法的优点很明显：不再是猜测非常好的的执行计划了，因为那样产生的执行计划可能并不是非常好的的，基于成本的优化器使用真实的值来构建执行计划。

　　但不幸的是，这个方法对于非OLTP系统的缺点也很明显，例如，如果决策支持系统下次运行的查询指定了一套绑定变量的值，那此时要想高效地执行查询，需要一个完全不同的执行计划吗?实际上，这种数据仓库环境并不罕见，对于决策支持系统，它可能非常希望有多个可用的执行计划作为候选，因为一个绑定变量集可能返回的结果集只包含几百行的数据，而另一套绑定变量可能返回几百万行数据，因此，Oracle建议保留CURSOR_SHARING作为该初始化参数的默认值，以强制产生一个新的更有效的执行计划。

　　自适应游标共享：更灵活的绑定

　　Oracle 11g提供自适应游标共享(ACS)以克服不该共享时的游标共享，ACS使用了两个新的度量机制：绑定敏感度和绑定感知。

　　绑定敏感度：无论何时，当包含绑定变量的SQL语句首次执行时，优化器在偷窥了绑定变量的值后，会为其标记一个绑定敏感度，以确定语句的谓词，但偷窥结束时也类似，因为它也为后面相同语句相同绑定变量不同值时进行对比，以确定是否要产生新的执行计划。

　　为了说明这些绑定敏感度是如何工作的，我在Oracle 11g的SH示例方案中的SH.SALES表上构造了一个简单的查询，因为它是方案中最大的表了，并且也按时间范围进行分区，如列表1所示：

-- 清空缓冲去缓存和共享池

　　ALTER SYSTEM FLUSH SHARED_POOL;

　　ALTER SYSTEM FLUSH BUFFER_CACHE;

　　--声明绑定变量

　　VARIABLE cust_start NUMBER;

　　VARIABLE cust_end NUMBER;

　　VARIABLE time_start DATE;

　　VARIABLE time_end DATE;

　　VARIABLE total_sold NUMBER;

　　VARIABLE total_qty NUMBER;

　　-- 测试#1

　　BEGIN

　　:cust_start := 2;

　　:cust_end := 38;

　　:time_start := '01 JAN 1998';

　　:time_end := '31 MAR 1998';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

　　这个查询使用了四个绑定变量来确定SH.SALES.TIME_ID和SH.SALES.CUST_ID的开始和结束范围。

　　自适应游标共享元数据：Oracle 11g提供了三个新的视图，并在v$sql视图中添加了两个新列以便让Oracle DBA确定优化器是否已经决定SQL语句是否适合自适应游标共享，优化器使用业务规则将SQL语句的执行计划进行分类以便共享：

　　在列表2中我在这些视图上构造一些简单的查询及格式化输出。

　　列表2 ：

-- 目的：显示优化器选择哪个SQL语句进行自适应游标共享

　　TTITLE 'SQL Statements With Bind Sensitivity Enabled|(from V$SQL)'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL plan_hash_value FORMAT 99999999999 HEADING 'Plan|Hash|Value'

　　COL iba_flag FORMAT A06 HEADING 'Bind|Aware?'

　　COL sql_text FORMAT A80 HEADING 'SQL Text'

　　SELECT

　　sql_id

　　,hash_value

　　,plan_hash_value

　　,is_bind_sensitive ibs_flag

　　,is_bind_aware iba_flag

　　,sql_text

　　FROM v$sql

　　WHERE ((is_bind_sensitive <> 'N') OR (is_bind_aware <> 'N'))

　　ORDER BY hash_value

　　;

　　TTITLE OFF

　　--目的:显示当前自适应游标共享元数据的分布情况

　　TTITLE 'Histograms for Adaptive Cursor Sharing|(from V$SQL_CS_HISTOGRAM)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL bucket_id FORMAT 9999 HEADING 'Bckt|ID#'

　　COL count FORMAT 999999 HEADING 'Exec-|ution|Count'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,bucket_id

　　,count

　　FROM v$sql_cs_histogram

　　;

　　TTITLE OFF

　　-- 目的:显示使用了自适应游标共享的游标执行统计情况

　　TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_STATISTICS)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL bind_set_hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL peeked FORMAT A05 HEADING 'Peek?'

　　COL executions FORMAT 999999 HEADING '# of|Exec-|utions'

　　COL rows_processed FORMAT 999999 HEADING '# of|Rows'

　　COL buffer_gets FORMAT 999999 HEADING 'Buffer|Gets'

　　COL cpu_time FORMAT 999999 HEADING 'CPU|Time'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,bind_set_hash_value

　　,peeked

　　,executions

　　,rows_processed

　　,buffer_gets

　　,cpu_time

　　FROM v$sql_cs_statistics

　　;

　　TTITLE OFF

　　-- 目的:显示自适应游标共享决定两个不同绑定变量的游标是否要创建新的执行计划的选择性度量

　　TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_SELECTIVITY)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL range_id FORMAT 9999 HEADING 'Rng|ID#'

　　COL low FORMAT A12 HEADING 'Low Value'

　　COL high FORMAT A12 HEADING 'High Value'

　　COL predicate FORMAT A80 HEADING 'Predicates'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,range_id

　　,low

　　,high

　　,predicate

　　FROM v$sql_cs_selectivity

　　;

　　TTITLE OFF

　　我将在本文剩下的部分中使用到它们以说明自适应游标共享是如何工作的，此外，在列表3中我显示了在这个元数据上第一次执行这个语句的影响。

　　列表3：

　　第一次这个语句被硬解析后，它的游标自动被标记为绑定敏感，但还没有绑定感知，查询的绑定变量值在第一次执行期间被放在自适应游标共享三个直方图的中间位置。

　　绑定感知：一旦SQL语句的游标被标记为绑定敏感，优化器可能还会决定将其视为绑定感知，优化器是通过检查提供给绑定变量的值是否与相同查询后面的执行计划匹配来实现的，如果优化器决定它可以使用现有的执行计划，那就只需要更新游标执行直方图以反应语句的执行情况，换句话说，如果绑定变量值发了重大变化，优化器可能会决定创建一个全新的子游标和执行计划，如果是这样的话，Oracle 11g也会存储自适应游标共享元数据中的子游标的相对选择性。

　　我觉得它有助于把这些选择性评级作为“电子云”或影响范围的中心点，Oracle文档了使用的术语是“选择性立方体”，在随后游标的执行过程中，优化器会使用游标最近执行的统计信息与现有的选择性统计信息进行比较，如果它观察到大多数执行都使用系统的选择性范围，游标将会被标记为绑定感知。

　　我用同一个查询的另外两次执行来说明了这个概念，但使用了完全不同的绑定变量，如列表4所示：

　　列表4：　

-- Execution #2

　　-----

　　BEGIN

　　:cust_start := 42999;

　　:cust_end := 50000;

　　:time_start := '01 JAN 1997';

　　:time_end := '31 MAR 1998';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

　　/

　　-----

　　-- Execution #3

　　-----

　　BEGIN

　　:cust_start := 1000;

　　:cust_end := 1400;

　　:time_start := '01 JAN 1996';

　　:time_end := '31 MAR 1997';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

　　为查询游标指定的自适应游标共享元数据产生的变化显示在列表5中。

　　列表5：　

SQL Statements With Bind Sensitivity Enabled

　　(from V$SQL)

　　Plan Bind

　　Hash Hash Sensi- Bind

　　SQL ID Value Value tive? Aware? SQL Text

　　---------------- ------------ ------------ ------ ------

　　87qtpurhk664g 3777173647 2855975716 Y Y SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN

　　:B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

　　87qtpurhk664g 3777173647 787661731 Y N SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN

　　:B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

　　Histograms for Adaptive Cursor Sharing

　　(from V$SQL_CS_HISTOGRAM)

　　Exec-

　　Hash Chld Bckt ution

　　Value SQL ID # ID# Count

　　------------ ---------------- ----- ----- -------

　　3777173647 87qtpurhk664g 1 0 1

　　3777173647 87qtpurhk664g 1 1 0

　　3777173647 87qtpurhk664g 1 2 0

　　3777173647 87qtpurhk664g 0 0 1

　　3777173647 87qtpurhk664g 0 1 1

　　3777173647 87qtpurhk664g 0 2 0

　　Selectivity Metrics for Adaptive Cursor Sharing

　　(from V$SQL_CS_STATISTICS)

　　# of

　　Hash Chld Hash Exec- # of Buffer CPU

　　Value SQL ID # Value Peek? utions Rows Gets Time

　　------------ ---------------- ----- ------------ ----- ------- -------

　　3777173647 87qtpurhk664g 1 1601990286 Y 1 1 2 0

　　3777173647 87qtpurhk664g 0 4302390 Y 1 1098 3178 0

　　Selectivity Metrics for Adaptive Cursor Sharing

　　(from V$SQL_CS_SELECTIVITY)

　　Hash Chld Rng

　　Value SQL ID # ID# Low Value High Value Predicates

　　------------ ---------------- ----- ----- ------------ ------------

　　3777173647 87qtpurhk664g 1 0 0.000616 0.000753 <=B1

　　3777173647 87qtpurhk664g 1 0 0.900000 1.100000 >=B2

　　3777173647 87qtpurhk664g 1 0 0.109520 0.133858 <=B3

　　3777173647 87qtpurhk664g 1 0 0.821710 1.004312 >=B4

　　注意，Oracle 11g已经为hash值为2855975716的SQL语句创建了新的子游标，不将它们都标记为绑定敏感和绑定感知，元数据中为这些游标指定的选择性度量值也更新了。

　　当绑定变量的值超出了现有绑定感知游标影响的范围时，执行包含这个绑定变量的查询会发生什么?在语句的硬解析期间，优化器可能只会选择扩大选择范围，以包括新的绑定值，这是通过创建新的子游标结合这两套绑定变量值，然后删除旧的、范围小的游标来实现的，显然，这样只会产生几个的确需要的几个子游标。

　　那么如何激活这一新功能呢?好消息是在Oracle 11g中默认就已经启动了，它完全与CURSOR_SHARING初始化参数无关，这大大增加了在OLTP/DSS系统中SQL语句使用绑定变量的机会。

　　对SQL计划管理(SPM)的影响：如果你读过我之前写的SQL计划管理方面的文章，你可能会疑惑自适应游标共享是否会影响SQL计划管理捕获和保存SQL执行计划到SQL管理基础库中的功能，下面列出它们之间交互的摘要信息：

　　如果初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES被设置为TRUE以激活自动捕获执行计划，那么带有绑定变量的SQL语句也会被标记为启用和接收执行计划。

　　如果同一个语句构建了第二个执行计划 – 并不是自适应游标共享 – 那么该计划只会简单地添加到语句的计划历史中，但它不会立即被使用，因为SPM首先会要求校验这个新的执行计划。

　　不幸的是，这意味着一个很好的执行计划会被忽略，解决这个问题的一个好办法是将自动捕获计划设置为FALSE，然后在库缓存中将所有子游标捕获到SMB中，这样将会强制所有子游标的计划被标记为SQL计划基线。

　　结语

　　Oracle 11g的新特性自适应游标共享为包含有绑定变量的SQL语句有效共享执行计划提供了一个更简单的方法，但只有绑定变量有值时才有意义，自适应游标共享有时也会产生新的执行计划，但共享的游标会保持相对小的数量。

关注我们