提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

oracle11g中SQL优化（SQL TUNING）新特性之Adaptive Cursor Sharing (ACS)

SQL 优化 特性 oracle11g cursor Tuning Adaptive Sharing
2023-09-11 14:14:42 时间

1.   ACS简单介绍

Oracle Database 11g提供了Adaptive Cursor Sharing (ACS)功能，以克服以往不该共享的游标被共享的可能性。ACS使用两个新指标：sensitivity and bindawareness来实施该特点。

2.   ACS机制

2.1.  Adaptive Cursor Sharing Metadata：Oracle 11g也提供了三个新视图和动态视图V$SQL的两个新列来同意DBA来确定优化器是否已经确定一个SQL语句为一个ACS的候选，而且，也能够通过它们来观察优化器分类SQL语句用以共享的运行计划所使用的业务规则：

视图

描写叙述

V$SQL

Two new columns are added:

  • IS_BIND_SENSITIVE indicates if a SQL statement is bind-sensitive. If this column contains a value of (Y)es, it means that the optimizer peeked at the values of the statement’s bind variables so that it can calculate each predicate’s selectivity.
  • Likewise, IS_BIND_AWARE indicates if the optimizer has also decided that the statement’s cursor is bind-aware after additional execution of the statement.

V$SQL_CS_HISTOGRAM

Distributes the frequency (within a three-bucket histogram) at which Oracle 11g used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed.

V$SQL_CS_SELECTIVITY

Contains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges. These values are also termed the cursor’s selectivity cube.

V$SQL_CS_STATISTICS

Lists the statistics of whether and/or how often an Adaptive Cursor has been shared. The PEEKED column will display a value of Y(es) if the bind set had been used to build the Adaptive Cursor.

表-1 ACS 视图

2.2.  Bind Sensitivity：当带有绑定变量的SQL语句首次被解析时，在优化器窥探了绑定变量的值，并确定了语句谓词的相关选择率后。把该游标标记为 bind-sensitive（绑定敏感的）。期间也保留了这些敏感測量值，以便今后带有相同变量、不同值的相同语句再次运行时进行比較，看一个已经存在的运行计划能否被新绑定变量值的语句利用。

2.3.  Bind Awareness：一旦一个SQL语句的游标被标为 bind-sensitive，优化器也能够确定游标是bind-aware。通过检查随后运行的相同SQL语句绑定变量的值和全部匹配计划已被捕获的绑定变量的值，优化器完毕这个步骤。假设优化器确定该语句能利用已存在的计划，那么，仅仅须要更新游标运行柱状图来反应语句的运行就可以。另外，假设绑定变量值足够不同。优化器或许决定创建一个新的子游标和运行计划。一旦这些发生，Oracle11g也把子游标的相关选择率存储到ACS元数据中。在游标随后的运行过程中。优化器比較存在的统计选择性数据和游标近期运行的统计数据，假设观察到大多数运行使用近似相同的选择性范围。那么，游标将会被标记为bind-aware。

当查询以一套超出一个已存在的bind-aware的游标绑定变量的选择率范围界限的不同的绑定变量值被运行时会发生什么呢？在该语句的硬解析期间，优化器或许只决定扩展那个游标的选择率范围来包括新的变量值。通过产生一个合并两套绑定变量值的新游来做到这点。可见在必要的时候，也不过添加了新游标。Oracle11g中。ACS特点默认被开启。且全然独立于CURSOR_SHARING參数。

2.4.  对SQLPlan Management (SPM)影响：最后。假设你了解古Oracle11g的SPM特点，你可能想知道ACS和SPM是否会相互影响，以下做一些简短的总结：

假设初始化參数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES已被设置为TRUE来开启自己主动计划捕获，那么，带绑定变量的一个SQL语句将被标作相应的能用和可接受的运行计划。

假设相同语句的第二个运行计划被创建——对ACS特点来说这并不新奇——那么。计划只被添加到语句的计划历史里，但并能被立马使用，由于SPM要求新运行计划首先被验证为一个较好的计划。

不幸的是。这意味着一个好的计划或许被忽视掉，哪怕是其游标的选择性范围可能会导致一个较好的性能。绕过该问题的一个非常好的办法是保持自己主动计划捕获为默认设置False，接着把全部library cahce里的子游标都捕获到SMB里去。这将会迫使ACS产生的游标的全部计划都被标为SQL PLAN BASELINES。

3.   限制

Oracle11gR2为止，ACS特性存在下面限制(当下面场景出现时，会导致ACS不会把游标标记为bind sensitive)：

Ø  扩展游标共享被关闭；

Ø  查询中没绑定变量。

Ø  某些參数被设置（比如：绑定变量窥探被置为flase）；

Ø  语句正使用并行查询时；

Ø  语句使用了hints。

Ø  Outlines正被使用；

Ø  查询为递归查询；

Ø  绑定变量数超过14；

4.   关闭及开启

Ø  ACS默认情况下是开启的。为了关闭ACS，我们须要改动下面三个參数：

alter system set"_optimizer_extended_cursor_sharing_rel"=none;

 altersystem set "_optimizer_extended_cursor_sharing"=none;

 altersystem set "_optimizer_adaptive_cursor_sharing"=false;

Ø  为了开启ACS，我们须要改动下面四个參数：

_optim_peek_user_binds=true（一定要开启绑定变量窥视）_optimizer_adaptive_cursor_sharing=TRUE（下面三个參数默认开启ACS）_optimizer_extended_cursor_sharing=UDO

_optimizer_extended_cursor_sharing_rel=SIMPLE

5.   注意：

Ø  相关hint：Oracle11g中有个新的 hint。当使用此hint时，即使把ACS特性关掉，ACS特性在语句级依旧会生效，该hint的语法为：/*+ BIND_AWARE*/。

Ø  关于Outlines：在Outlines存在的场景下，不管在系统级启用还是语句级通过hint启用，ACS都会失效。

6.   结论：

Oracle Database 11g的新特点ACS功能为带绑定变量的SQL语句运行计划高效共享提供了一个简单的方法。因为仅仅有当绑定变量值选择率必要时，ACS也会产生一个新的运行计划。因此，共享游标的数目会保持最小。

 

相关文章