Oracle SQL 使用绑定变量bind variable

• 列出使用绑定变量的优点
• 使用绑定扫视
• 使用自适应游标共享

Cursor Sharing 游标共享和不同的文字值

 

如果在您使用的 SQL 语句中，为 WHERE 子句条件提供了不同的文字值，则会在库高速缓存中存储许多版本的几乎完全相同的 SQL 语句。因为每条 SQL 语句是使用不同值提交的，不能在库高速缓存中找到此语句，因此您必须执行所有步骤来处理新的 SQL 语句。这样不仅常常会导致不必要的语句分析，还会导致库高速缓存很快被填满，因为所有不同的语句都存储在其中。

在以此方式进行编码时，您没有利用游标共享。

但是，根据提供的文字值，优化程序可能会生成不同的执行计划。例如，可能存在大量 JOBS，其中 MIN_SALARY 大于 12000。另一方面，可能只有很少的 JOBS，其中 MIN_SALARY 大于 18000。数据分布上的差异可能表明需要添加一个索引，以便可以根据查询中提供的值使用不同的计划。本幻灯片展示了这样的情况。正如您所看到的，第一个查询和第三个查询使用相同的执行计划，但第二个查询使用了另一个执行计划。

从性能角度看，有独立游标时性能较好。但是，这不是很经济，因为您可以为本例中的第一个查询和最后一个查询实现共享游标。

注：在幻灯片示例中，第一个查询和第三个查询的 V$SQL.PLAN_HASH_VALUE 是
相同的。

 

 

 

Curosr Sharing 游标共享和绑定变量

 

 

如果使用绑定变量，而不是针对不同文字值发出不同语句，则可避免额外的分析活动（在理论上）。这是因为优化程序会认识到该语句已经过分析，因此决定重用相同的执行计划，即便在下次重新执行相同的语句时您指定了不同的绑定值，也是如此。

在幻灯片的示例中，绑定变量名为 min_sal。它将与 JOBS 表的 MIN_SALARY 列进行比较。不必发出三条不同的语句，发出使用绑定变量的一条语句即可。在执行时，会使用相同的执行计划，用特定值替换变量。

但是，从性能角度看，这不是最佳方案，因为在三次执行中，只有两次您获得了最佳性能。从另一方面看，这样做非常经济，因为您只需要在库高速缓存中存储一个共享游标便可执行全部三条语句。

 

SQL*Plus 中的绑定变量

 

 

SQL> variable job_id varchar2(10) 
SQL> exec :job_id := 'SA_REP'; 
 
PL/SQL procedure successfully completed. 
 
SQL> select count(*) from employees where job_id = :job_id; 
 
  COUNT(*) 
---------- 
        30 
 
SQL> exec :job_id := 'AD_VP'; 
 
PL/SQL procedure successfully completed. 
 
SQL> select count(*) from employees where job_id = :job_id; 
 
  COUNT(*) 
---------- 
         2 

可以在 SQL*Plus 会话中使用绑定变量。在 SQL*Plus 中，使用 VARIABLE 命令定义绑定变量。然后，通过使用 EXEC[UTE] 命令执行赋值语句可以为变量赋值。自此以后，对该变量的任何引用都使用您赋予的值。
在幻灯片的示例中，将 SA_REP 赋给变量后执行了第一个 Select Count 操作。结果是 30。然后，将 AD_VP 赋给变量，得到的计数是 2。

 

 

Oracle Enterprise Manager 中的绑定变量

 

 

在 Oracle Enterprise Manager 的“SQL Worksheet（SQL 工作表）”页中（请参见“Database Home（数据库主页）”的“Related Links（相关链接）”区域中的“SQL Worksheet（SQL 工作表）”链接），可以指定某条 SQL 语句应当使用绑定变量。可以通过选中“Use bind variables for execution（在执行中使用绑定变量）”复选框完成此任务。选中此复选框后，会生成几个字段，您可以在其中输入绑定变量值。可在 SQL 语句中使用以冒号开头的变量名称引用这些值。变量的引用顺序定义了哪个变量获得哪个值。第一个被引用的变量将获得第一个值，第二个变量获得第二个值，依此类推。如果更改了语句中变量的引用顺序，则可能需要更改值列表以与此顺序一致。

 

 

绑定变量扫视

 

 

如果查询中使用文字，优化程序可以使用这些文字值来确定最佳计划。但是，如果使用绑定变量，优化程序仍需要基于查询中条件的值选择最佳计划，但在 SQL 文本中无法方便地查看这些值。这意味着，分析 SQL 语句时，系统需要能够查看绑定变量的值，以确保选择适合于这些值的有效计划。优化程序通过扫视绑定变量中的值来完成此操作。在硬分析 SQL 语句时，优化程序估算每个绑定变量的值，并将其用作输入值来确定最佳计划。在第一次分析该查询确定好执行计划后，以后只要执行相同的语句，不管所用的绑定值为何，都会重用该计划。

此功能是在 Oracle9i Database R2 中引入的。Oracle Database 11g 更改了此行为。

 

 

绑定变量扫视

 

 

在某些情况下，绑定变量扫视会导致优化程序选择不太理想的计划。之所以发生这种情况，是因为在为查询的所有后续执行确定计划时使用的都是绑定变量的第一个值。所以，即使后续执行提供不同的绑定值，也使用同一计划。对于具有不同绑定变量值的执行，也许其它计划更合适。例如，当一个特定索引的选择性随列值有非常大的变化时。对于较低的选择性，全表扫描可能更快。对于较高的选择性，索引范围扫描可能更适合。如幻灯片所示，计划 A 可能适合于 min_sal 的第一个值和第三个值，但可能不是第二个值的最佳选择。假设只有很少高于 18000 的 MIN_SALARY 值，且计划 A 为全表扫描。在此情况下，对于第二个执行，全表扫描可能不是一个好计划。

因此绑定变量是有利的，因为它们可实现更多的游标共享，从而减少了 SQL 分析。但是，在这种情况下，对于某些绑定变量值而言，游标共享可能会导致选择不太理想的计划。对于决策支持系统 (DSS) 环境而言，这是一个不使用绑定变量的有效理由。在决策支持系统环境中，查询的分析只占提交查询时需做工作的一个很小百分比。分析也许一转眼的工夫就会完成，但执行可能会花费几分钟或几小时。使用较慢的计划执行时，不值得花费成本来节省分析时间。

 

游标共享增强

 

• Oracle8i 引入了仅其中的文字值不同的 SQL 语句的共享。
• Oracle9i 扩展了此功能，仅将共享语句界定为相似语句，而不是强制执行。
• 相似：不管文字值为何，都使用同一执行计划

 

SQL> SELECT * FROM employees
  2  WHERE employee_id = 153;

 

• 不相似：对于不同的文字值可能使用不同的执行计划

SQL> SELECT * FROM employees
  2  WHERE department_id = 50;

 

Oracle8i 引入了仅其中的文字值不同的 SQL 语句的共享。优化程序并不是在每次执行具有不同文字值的相同语句时制定一个执行计划，而是生成一个通用的执行计划，将其用于此语句的所有后续执行。

由于仅使用一个执行计划，而不是许多个，所以您应针对您的应用程序测试此功能，然后再确定是否启用它。这就是 Oracle9i 扩展此功能，仅将语句作为相似语句来共享的原因。换句话说，就是只有在优化程序确信执行计划独立于所使用的文字值时才使用该执行计划。例如，请考虑一个查询，其中 EMPLOYEE_ID 是主键：

SQL> SELECT * FROM employees WHERE employee_id = 153;

用任何值进行替代都会生产相同的执行计划。因此，对于多次出现的使用不同文字值执行的相同语句，优化程序只生成一个计划是安全的。

另一方面，假设在相同 EMPLOYEES 表中 DEPARTMENT_ID 列值的范围很广。例如，部门 50 包含的员工数可能超过员工总数的三分之一，而部门 70 可能只包含一两个员工。

 

请参见两个查询：

SQL> SELECT * FROM employees WHERE department_id = 50;

SQL> SELECT * FROM employees WHERE department_id = 70;

如果针对 DEPARTMENT_ID 列具有直方图统计信息，并且该列包含的数据存在偏差，则仅使用一个执行计划共享同一游标将是不安全的。在这种情况下，根据先执行哪条语句，执行计划可能包含全表（或索引快速完全）扫描，或者使用简单的索引范围扫描。

 

CURSOR_SHARING 参数

 

• CURSOR_SHARING 参数值：

–FORCE

–EXACT（默认值）

–SIMILAR

• 可以使用下列项更改 CURSOR_SHARING：

–ALTER SYSTEM

–ALTER SESSION

–初始化参数文件

• CURSOR_SHARING_EXACT 提示

CURSOR_SHARING 初始化参数的值决定优化程序如何使用绑定变量处理语句：

• EXACT：完全禁用文字值替换
• FORCE：对所有文字值都执行共享
• SIMILAR：仅对安全的文字值执行共享

在以前的版本中，您可能仅选择 EXACT 或 FORCE 选项。这会导致优化程序对语句进行检查，以确保仅对安全文字值执行替换。在执行此操作时，它可以使用有关任何可用索引的性质的信息（唯一或非唯一），以及在索引或基表（包括直方图）上收集的统计信息。

可以使用 ALTER SYSTEM SET CURSOR_SHARING 或 ALTER SESSION SET CURSOR_SHARING 命令覆盖初始化文件中的 CURSOR_SHARING 值。

CURSOR_SHARING_EXACT 提示会导致系统执行 SQL 语句，而不尝试用绑定变量替换文字值。

 

强制游标共享：示例

 

SQL> alter session set cursor_sharing = FORCE;

SELECT * FROM jobs WHERE min_salary > 12000;

SELECT * FROM jobs WHERE min_salary > 18000;

SELECT * FROM jobs WHERE min_salary > 7500;

 

SELECT * FROM jobs WHERE min_salary > :”SYS_B_0″

 

 

 

在幻灯片的示例中，由于使用 ALTER SESSION 命令强制游标共享，所有仅文字值不同的查询会被自动覆盖，从而使用名为 SYS_B_0 的、由系统生成的同一绑定变量。结果，您最后会具有一个子游标，而不是三个。

注：自适应游标共享也可能适用，在这种情况下可能会生成另一个子游标。

 

自适应游标共享：概览

 

• 自适应游标共享：
• 仅允许针对使用绑定变量的语句智能地共享游标
• 用于在游标共享和优化之间找到一个平衡点
• 具有以下优点：

–自动检测各种不同的执行什么情况下能从不同的执行计划受益

–将生成的子游标数限制到最少

–是自动机制，无法关闭

 

设计绑定变量，是为了使 Oracle DB 可以针对多条 SQL 语句共享单个游标，以减少分析 SQL 语句所使用的共享内存量。然而，游标共享和 SQL 优化是两个相互冲突的目标。编写带文字值的 SQL 语句可为优化程序提供更多的信息，无疑，这有利于生成更好的执行计划，但大量的硬分析会导致内存和 CPU 开销增加。Oracle9i Database 首次尝试推出了一个折衷的解决方案 — 允许共享使用不同文字值的相似 SQL 语句。对于使用绑定变量的语句，Oracle9i 还引入了绑定扫视概念。为了从绑定扫视中受益，假定使用游标共享，且假定语句的不同调用使用相同的执行计划。若使用不同的执行计划时语句的不同调用受益显著，则绑定扫视对生成有效的执行计划就不再有用。

为了尽可能解决此问题，Oracle Database 11g 引入了自适应游标共享。此功能是一项更复杂的策略，它不会盲目地共享游标，如果与分析时间和内存使用量开销相比，使用多个执行计划所带来的收益更大，则会为使用绑定变量的每条 SQL 语句生成多个执行计划。然而，由于使用绑定变量的目的是共享内存中的游标，因此在确定需要生成的子游标数目时必须采取一种折衷的方法。

 

自适应游标共享：体系结构

 

 

使用自适应游标共享时，在此幻灯片中所示的方案中将执行下列步骤：

1. 游标照常会先开始进行硬分析。如果发生绑定扫视，且使用直方图计算包含绑定变量的谓词的选择性，则该游标将被标记为对绑定敏感的游标。此外，还会存储一些有关包含绑定变量的谓词的信息，包括谓词选择性。在该幻灯片的示例中，所存储的谓词选择性是一个以 (0.15,0.0025) 为中心的立方体。由于进行了初始硬分析，将使用已扫视的绑定确定初始执行计划。执行游标后，绑定值和游标的执行统计信息存储在该游标中。

在下次使用一组新的绑定值执行该语句时，系统会执行常规软分析，并查找供执行使用的匹配游标。执行结束时，会将执行统计信息与当前存储在游标中的执行统计信息进行比较。然后，系统观察所有先前运行的统计信息模式（请参阅下一张幻灯片中的 V$SQL_CS_… 视图），并确定是否将游标标记为能识别绑定的游标。

 

2. 下一次对此查询进行软分析时，如果游标已是能够识别绑定的，则会使用能识别绑定的游标匹配。假设具有新的一组绑定值的谓词选择性现在是 (0.18,0.003)。由于选择性用作能识别绑定的游标匹配的一部分，并且该选择性在现有立方体内，因此该语句使用现有子游标的执行计划运行。
3. 下一次对此查询进行软分析时，假设具有一组新绑定值的谓词选择性现在是 (0.3,0.009)。由于该选择性不在现有立方体内，所以找不到子游标匹配项。因此，系统会执行硬分析，在本例中生成了一个具有另一个执行计划的新子游标。此外，新选择性立方体将存储为该新子游标的一部分。执行该新子游标后，系统会将绑定值和执行统计信息存储在该游标中。
4. 下一次对此查询进行软分析时，假设具有一组新绑定值的谓词选择性现在是 (0.28,0.004)。由于该选择性不在现有的某个立方体内，系统将执行硬分析。假设此时硬分析生成与第一个执行计划相同的执行计划。因为该计划与第一个子游标相同，所以将合并这两个子游标。也就是说，这两个立方体将合并为一个较大的新立方体，并删除其中一个子游标。下次执行软分析时，如果选择性位于该新立方体内，子游标将匹配。

 

自适应游标共享：视图

下列视图提供有关自适应游标共享使用情况的信息：

 

这些视图确定查询是否是能识别绑定的查询，是否为自动处理的，无需用户输入。但是，有关所发生的操作的信息显示在 V$ 视图中，这样在遇到问题时，您可以进行诊断。V$SQL 中新增了几列：

• IS_BIND_SENSITIVE：指示游标是否是对绑定敏感的，值为 YES | NO。符合以下情况的查询称为对绑定敏感的查询：计算谓词选择性时优化程序为其扫视绑定变量值，并且绑定变量值的更改可能导致不同的计划。
• IS_BIND_AWARE：指示游标是否为能识别绑定的游标，值为 YES | NO。位于游标高速缓存中、已标记为使用能识别绑定的游标共享的游标称为识别绑定的游标。
• V$SQL_CS_HISTOGRAM：显示跨三个存储桶执行历史记录直方图的执行计数的分布情况。
• V$SQL_CS_SELECTIVITY：显示为包含绑定变量且在游标共享检查中使用了其选择性的每个谓词存储在游标中的选择性立方体或范围。它包含谓词文本和选择性范围的下限值和上限值。

• V$SQL_CS_STATISTICS：自适应游标共享监视查询的执行，在一段时间内收集相关的信息，并使用此信息确定是否切换为对该查询使用能识别绑定的游标。该视图汇总了它收集的用于做出该决定的信息。对于许多执行来说，它跟踪已处理的行数、缓冲区获取数和 CPU 时间。如果构建游标时使用了绑定集，则 PEEKED 列的值为 YES，否则为 NO。

自适应游标共享：示例

 

 

请考虑该幻灯片中的数据。JOB_ID 列具有相应的直方图统计信息，这些统计信息表示 SA_REP 比 AD_ASST 多出现数千次。在这种情况下，如果使用文字值而不使用绑定变量，查询优化程序会发现 AD_ASST 值出现在不足 1% 的行中，而 SA_REP 值出现在近三分之一的行中。如果表包含一百万以上的行，则针对每个值的查询执行计划是不同的。AD_ASST 查询会导致索引范围扫描，因为具有该值的行很少。SA_REP 查询会导致全表扫描，因为具有该值的行很多，这样可以更高效地读取整个表。但是，实际上，起初使用绑定变量会导致对这两个值使用相同的执行计划。因此，即使对于其中每个值存在更好的不同计划，也使用相同的计划。

在使用绑定变量执行几次此查询后，系统会考虑查询的绑定识别，此时系统会基于绑定值更改计划。这意味着基于绑定变量值对查询使用最佳计划。

 

 

与自适应游标共享交互

• CURSOR_SHARING：

–如果 CURSOR_SHARING <> EXACT，则可能会使用绑定变量重写包含文字的语句。

–如果语句被重写，则可能会对其应用自适应游标共享。

• SQL 计划管理 (SPM)：

–如果将 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 设置为 TRUE，则仅使用生成的第一个计划。

–一种解决方法是：将此参数设置为 FALSE，然后运行您的应用程序，直到将所有计划都加载到游标高速缓存中。

–手动将游标高速缓存加载到相应的计划基线中。

 

• 自适应游标共享与 CURSOR_SHARING 参数无关。此参数的设置决定是否用系统生成的绑定变量替换文字。如果替换，则自适应游标共享的行为跟用户在开始时已提供绑定一样。
• 如果使用 SPM 自动计划捕获，则将为使用绑定变量的 SQL 语句捕获的第一个计划标记为相应的 SQL 计划基线。如果同一 SQL 语句存在其它计划（自适应游标共享可能就是这种情况），则该计划将添加到 SQL 语句计划历史记录中，并对其进行标记以便验证：不会立即使用该计划。因此，即使自适应游标共享提供了基于一组新绑定值的新计划，在该计划得到验证前 SPM 也不允许使用它。这样就退回到了 10g 的行为，该语句的所有后续执行仅使用基于第一组绑定值生成的计划。一种可能的解决方法是：将自动计划捕获设置为 False，然后将系统运行一段时间，在使用具有绑定的 SQL 语句拥有的所有计划填充游标高速缓存后，将整个计划直接从游标高速缓存加载到相应的 SQL 计划基线。通过执行此操作，默认情况下，单个 SQL 语句的所有计划都将被标记为 SQL 基线计划。此课程不介绍 SQL 计划管理。请参阅《SQL 性能优化》课程或《面向管理员的新增功能》课程。