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

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

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

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

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

--清空缓冲去缓存和共享池ALTERSYSTEM FLUSH SHARED_POOL;ALTERSYSTEM FLUSH BUFFER_CACHE;--声明绑定变量VARIABLE cust_startNUMBER;

VARIABLE cust_endNUMBER;

VARIABLE time_start DATE;

VARIABLE time_end DATE;

VARIABLE total_soldNUMBER;

VARIABLE total_qtyNUMBER;--测试#1BEGIN:cust_start :=2;

:cust_end :=38;

:time_start :='01 JAN 1998';

:time_end :='31 MAR 1998';SELECTSUM(amount_sold)

,SUM(quantity_sold)INTO:total_sold

,:total_qtyFROMsh.salesWHEREcust_idBETWEEN:cust_startAND:cust_endANDtime_idBETWEEN:time_startAND: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 FORMAT99999999999HEADING'Hash|Value'COL plan_hash_value FORMAT99999999999HEADING'Plan|Hash|Value'COL iba_flag FORMAT A06 HEADING'Bind|Aware?'COL sql_text FORMAT A80 HEADING'SQL Text'SELECTsql_id

,hash_value

,plan_hash_value

,is_bind_sensitive ibs_flag

,is_bind_aware iba_flag

,sql_textFROMv$sqlWHERE((is_bind_sensitive<>'N')OR(is_bind_aware<>'N'))ORDERBYhash_value

;

TTITLEOFF--目的:显示当前自适应游标共享元数据的分布情况TTITLE'Histograms for Adaptive Cursor Sharing|(from V$SQL_CS_HISTOGRAM)'COL hash_value FORMAT99999999999HEADING'Hash|Value'COL sql_id FORMAT A16 HEADING'SQL ID'COL child_number FORMAT9999HEADING'Chld|#'COL bucket_id FORMAT9999HEADING'Bckt|ID#'COLcountFORMAT999999HEADING'Exec-|ution|Count'SELECThash_value

,sql_id

,child_number

,bucket_id

,countFROMv$sql_cs_histogram

;

TTITLEOFF--目的:显示使用了自适应游标共享的游标执行统计情况TTITLE'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_STATISTICS)'COL hash_value FORMAT99999999999HEADING'Hash|Value'COL sql_id FORMAT A16 HEADING'SQL ID'COL child_number FORMAT9999HEADING'Chld|#'COL bind_set_hash_value FORMAT99999999999HEADING'Hash|Value'COL peeked FORMAT A05 HEADING'Peek?'COL executions FORMAT999999HEADING'# of|Exec-|utions'COL rows_processed FORMAT999999HEADING'# of|Rows'COL buffer_gets FORMAT999999HEADING'Buffer|Gets'COL cpu_time FORMAT999999HEADING'CPU|Time'SELECThash_value

,sql_id

,child_number

,bind_set_hash_value

,peeked

,executions

,rows_processed

,buffer_gets

,cpu_timeFROMv$sql_cs_statistics

;

TTITLEOFF--目的:显示自适应游标共享决定两个不同绑定变量的游标是否要创建新的执行计划的选择性度量TTITLE'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_SELECTIVITY)'COL hash_value FORMAT99999999999HEADING'Hash|Value'COL sql_id FORMAT A16 HEADING'SQL ID'COL child_number FORMAT9999HEADING'Chld|#'COL range_id FORMAT9999HEADING'Rng|ID#'COL low FORMAT A12 HEADING'Low Value'COL high FORMAT A12 HEADING'High Value'COL predicate FORMAT A80 HEADING'Predicates'SELECThash_value

,sql_id

,child_number

,range_id

,low

,high

,predicateFROMv$sql_cs_selectivity

;

TTITLEOFF

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

列表3：

SQL StatementsWithBind Sensitivity Enabled (fromV$SQL)PlanBind

Hash Hash Sensi-Bind

SQL ID Value Value tive? Aware? SQLText---------------- ------------ ------------ ------ ------87qtpurhk664g3777173647787661731Y NSELECTSUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD)FROMSH.SALESWHERECUST_IDBETWEEN:B4AND:B3ANDTIME_IDBETWEEN:B2AND:B1

page1HistogramsforAdaptiveCursorSharing

(fromV$SQL_CS_HISTOGRAM)Exec-Hash Chld Bckt ution

Value SQL ID # ID#Count------------ ---------------- ----- ----- -------377717364787qtpurhk664g000377717364787qtpurhk664g011377717364787qtpurhk664g020Selectivity MetricsforAdaptiveCursorSharing

(fromV$SQL_CS_STATISTICS)

#ofHash Chld HashExec-#ofBuffer CPU

Value SQL ID # Value Peek? utions Rows Gets Time------------ ---------------- ----- ------------ ----- ------- -------377717364787qtpurhk664g04302390Y1109831780

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

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

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