变量绑定

示例：

  1. select * from t where id = :B;

变量赋值：

  1. var rm number;
  2. exec :rm:=1;
  3. select * from t where rm=:rm;
  5. exec :rm:=2;
  6. select * from t where rm=:rm;

除了第一条sql做了硬解析之外，后面的sql都是软解析，速度要快一些。

存储过程中使用绑定变量：

  1. begin
  2.     for i in 1..4 loop
  3.         execute immediate 'select * from t where rm= :i' using i;
  4.     end loop;
  5. end;
  6. /

变量绑定的目的：

  • 减少SQL硬解析的次数
  • 减少系统资源开销
  • 减少latch争用

应用场景：

适用于OLTP系统：

  • 用户并发很高
  • 表中有主键
  • 操作的数据少
  • 执行计划基本相同
  • SQL的重复率高

不适用于OLAP系统：

  • 用户少
  • 执行计划多变
  • SQL解析对系统性能影响小

SQL语句的处理

SQL处理流程：

硬解析（Hard Parse）：

  • 语法分析（Syntax Check）
    1. select * fromm t;  -- from关键字写错了，会在语法分析时报错
  • 语义分析（Semantic Check）
    1. select * from 2;  -- 2表名无效，会在语义分析时报错

硬分析步骤：

当SQL语句第一次执行时，都会被硬解析。

软解析（Soft parse）：

  • 使用Hash值到共享池中搜索执行计划
  • 使用现有执行计划执行SQL

当SQL硬解析之后，后续相同的SQL会被软解析，除非SQL被剔除Shared_Pool。

  1. alter system flush shared_pool;  -- 强制刷空Shared Pool; oracle 11g有bug，该语句执行1次之后可能并不会清空，需要执行至少2次

会话对游标的缓存（Softer soft parse）：

session_cached_cursor：

  • 对于已经关闭的cursor，可以把它的信息保留在PGA中，用于后续对cursor的继续调用
  • 如果该参数设置为0，Oracle将会对关闭的游标重新打开

父子游标

同样的SQL，因某些其他的差异，会产生另外的Cursor：

  • 父游标（parent cursor）：第一次运行的SQL
  • 子游标（child cursor）：后续的SQL

如果一个父游标对一个子游标，说明这条sql是没有问题的。如果一个父游标对多个子游标，说明这个SQL语句是有些差异的。

例如，同样的SQL使用了不同的用户操作：

  1. -- test用户下查询emp表
  2. conn test/tiger
  3. select * from emp where 1=0;
  5. -- scott用户下查询emp表
  6. conn scott/tiger
  7. select * from emp where 1=0;

此时便会有2个子游标：

  1. -- 可以查看到2个sql_id相同的子游标
  2. select sql_id, child_number, sql_text
  3. from v$sql
  4. where sql_text = 'select * from emp where 1=0';
  6. -- 可以查看到两个sql游标的权限不同
  7. select sql_id, auth_check_mismatch
  8. from v$sql_shared_cursor
  9. where sql_id = 'aumkd2rvq7g41';

oracle使用SQL语句本身的文本生成的Hash值，所以这两条sql的Hash值相同，生成的是同一个父游标。但是因为操作的是不同用户的表，所以生成的子游标不同。

可以通过V$SQL_SHARED_CURSOR查看游标共享的信息，表中有很多匹配字段表示用哪一项进行匹配。

还有其他方式可以导致相同的SQL产生了不同的子游标，例如优化器不同：

  1. select * from emp where 1=0;
  3. -- 改变优化器
  4. alter session set optimizer_mode=first_rows;
  6. -- 再次查询该sql时，会再生成一条子游标
  7. select * from emp where 1=0;
  9. -- 查看V$SQL_SHARED_CURSOR可以查看到优化器类型匹配值不同
  10. select sql_id, OPTIMIZER_MODE_MISMATCH
  11. from v$sql_shared_cursor
  12. where sql_ID='xxx';

游标共享

游标：游标在用户的会话里，像C语言的指针一样，指向共享池里面的一条SQL语句。

游标共享就是共用这样一个游标入口，使用同一个sql的执行计划和sql信息来解析sql，可以理解为sql执行计划重用。

共享游标参数：cursor_sharing

  1. alter session set cursor_sharing = EXACT;

参数共有3个选项：

  • exact：默认值，只有绝对相同的Sql才允许游标共享。不会出现oracle强制绑定变量的问题。开发人员需要自己绑定变量。
  • force：oracle会强制对sql中谓词上出现的值使用变量来代替
  • similar：寻找相似的游标做共享。

例如，一张表有90000条数据，object_id为主键，对于以下两条SQL：

  1. select * from t where object_id<100;
  2. select * from t where object_id<100000;

如果是exact，那么这是两个完全不同的sql，会为每个sql生成执行计划：第一条sql数据量较少，会走索引扫描；第二条sql数据量很多，会走全表扫描。

如果是force，sql在执行时会被强制绑定变量，变成：

  1. select * from t where object_id<:SYS_B_0;

在执行完第一条sql后，执行计划就生成了，执行第二条sql时不再进行CBO分析，也会根据上一条sql的执行计划去走索引扫描。这也是绑定变量的一个弊端。

如果是similar，sql执行时也会被转换成绑定变量：

  1. select * from t where object_id<:SYS_B_0;

但是，因为object_id是主键索引，oracle认为sql可能根据值的不同而有不同的执行计划（索引扫描、全表扫描），这时oracle会为该SQL生成两个子游标。

变量窥视（bind peeking）

从oracle 9i开始，Oracle在第一次解析SQL（Hard parse）时，如果SQL上有变量绑定，会查看这个变量的值，以便于更准确的指定执行计划；但在后续的分析中（Soft parse），将不会理会这个变量的值。

使用场景：

  • 执行计划几乎不改变（OLTP）
  • 大量的并发
  • 大量的除谓词外几乎相同的SQL

不适用场景：

  • 执行计划会随变量值的变化而变化
  • 少量的SQL（OLAP）

有可能后续SQL的数据量等代价和第一次解析该SQL的代价不同，但是因为变量绑定和bind peeking，后续的SQL还是会沿用第一次硬解析时候生成的执行计划，这样就可能导致一些sql执行效率不高。

可适应性游标共享ACS

Adaptive cursor sharing（ACS）：Oracle 11g用于解决变量绑定带来的负面影响，通过不断观察bind的值，来决定新的SQL是否使用之前的执行计划，解决变量绑定导致后续执行计划不变的问题。

bind peek只在第一次解析时观察bind的值，ACS是每次都观察bind的值。

缺点：

  • 更多的硬解析
  • 产生更多的子游标，需要更多的内存
  • 消耗更多的CPU

在11gR1中,ACS有很多bug，可能会产生很多不必要的子游标，不建议使用。在 11gR2 时，该bug被修复。

使用ACS的前提条件：

  • 绑定变量使用 bind peeking
  • 绑定变量的列上有直方图