### Oracle里的绑定变量

使用绑定变量，是可以重用解析树和执行计划基础条件.

#### 绑定变量的典型写法

```
--sql
var x number;
exec :x := 7369;
select ename from emp where empno= :x;
--plsql
declare
  vc_name varchar2(10);
begin
  execute immediate 'select ename from emp whre empno= :1' into vc_name using 7369;
  dbms_output.put_line(vc_name);
end;
/
--execute immediate [带绑定变量的sql] using [对应绑定变量的具体输入值]

--dml语句的绑定变量
declare
   vc_sql_1 varchar2(4000);
   vc_sql_2 varchar2(4000);
   n_temp_1 number;
   n_temp_1 number;
begin
  vc_sql_1 := 'insert into emp(empno,ename,job) values(:1,:2,:3)';
  execute immediate vc_sql_1 using 7370,'CUIHUA2','DBA';
  n_temp_1 := sql%rowcount;
  vc_sql_2 := 'insert into emp(empno,enmae,job) values(:1,:1,:1)';
  execute immediate vc_sql_2 using 7371,'CUIHUA3','DBA';
  n_temp_2 := sql%rowcount;
  dbms_output.put_line(to_char(n_temp_1+n_temp_2));
  commit;
end;
/
--using  根据位置传入相关变量参数值

--动态sql可以使用绑定变量,returning 可以和带绑定变量的目标sql连用，目的把受该sql影响的行记录的对应字段值给取出来. 
--eg
declare
    vc_column varchar2(10);
    vc_sql varchar2(4000);
    n_temp number;
    vc_name varchar2(10);
begin
   vc_column := 'empno';
   vc_sql :='delete from emp where ' || vc_column || ' = :1 returning ename into :2';
   execute immediate vc_sql using 7369 returning into vc_name;
   dbms_output.put_line(vc_ename);
   commit;
end;
/
```

###### PL/SQL中批量绑定的典型用法

> 主要优势，一次处理一批数据。

可以见到地将PL/SQL引擎看做专门用来处理PL/SQL代码块中除了sql之外的所有部分(eg:变量、复制、循环等)子系统，SQL引擎用来处理sql语句的子系统。 这里的PL/SQL引擎和SQL引起上下文切换就是指他们之间的交互。

```
--减少交互，提高性能
fetch cursorname bulk collect into [自定义的属组] <limit CN_BATCH_SIZE>

--eg  "forall" 表示一次执行一批sql
declare
    cur_emp sys_refcursor;
    vc_sql  varchar2(4000);
    type namelist is table of varchar2(10);
    enames namelist;
    CN_BATCH_SIZE constant pls_integer :=1000;
begin
   vc_sql := 'select ename from emp where empno > :1';
   open cur_emp for vc_sql using 7900;
   loop
     fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE;
     
     for i in 1..enames.count loop
      dbms_output.put_line(enames(i));
     end loop;
    exit when enames.count < CN_BATCH_SIZE;
    end loop;
close cur_emp;
end;
/
```

##### 绑定变量的使用原则和最佳实践

- OLTP系统，建议使用绑定变量，最好是批量使用，前台代码和后台PL/SQL代码都使用批量绑定。
- 对于OLTP/OLAP混合型，如果有循环，循环内的SQL腰使用批量绑定变量。

page 245

### Oracle里的游标共享

未使用绑定变量再整改代价大，绑定变量的窥探影响
 常规游标解决第一个问题，自适应游标共享解决第二个问题
 
 #### 常规游标共享
 
 
```
--参数cursor_sharing控制
--exact 默认不会自动替换
--similar  会被替换，12c之后过时，问题较多，对于它认为不安全的，都会执行一次硬解析
--force 强制，不再适用于Oracle11g及后续版本
```

> 如果想在不改变一行代码的情况下，设置成force可以，虽然不是理想的方案，省事。

#### 自适应游标

> 启动了绑定变量窥探的情况前提条件下，让目标SQL在其可能的多个执行计划之间“自适应”地做出选择，而不再像之前那样必须刻板地沿用该sql硬解析时所产生的解析树和执行计划。

扩展游标共享做的第一件事情就是将目标sql所对应的Child Cursor标记为Bind Sensitive。 它就是指Oracle觉得某个含绑定变量的目标SQL的执行计划可能会随着所传入的绑定 变量输入值的变化而变化。

当满足如下三个条件时，目标sql所对应的Child Cursor就会被Oracle标记为Bind Sensitive
- 启动了绑定变量窥探
- 该SQL使用了绑定变量（不管是sql自带还是开启常规游标共享后系统产生的）
- 该SQL使用的是不安全的为此条件（例如范围查询，目标列上有直方图统计信息的等值查询等）

自适应游标共享要做的第二件事将目标SQL所对应的Child Cursor标记为Bind Aware。指Oracle已经确定某个含绑定变量的目标SQL的执行计划会随着所传入的绑定变量输入值的变化而变化

当满足一下两个条件时，目标SQL所对应的子游标就会被标记为Bind Aware
- 该SQL所对应的Child Cursor在之前已经被标记为Bind Sensitive
- 该SQL在接下来连续两次执行时，所对应的runtime统计信息与该sql之前硬解析时所对应的runtime统计信息均存在较大差异。

> 对于自适应游标共享而言，v$sql中列IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE分别用来表示Child Cursor是和否，及是否共享。共享的含义是指存储在该Child Cursor中的解析树和执行计划是否能被重用。非共享不能被重用，并且也会在第一时间被age out出shared Pool.

> 与自适应游标共享两个重要视图，v$sql_cs_statistics(用于显示指定Child Cursor中存储的runtime统计信息)  v$sql_cs_selectivity（用于显示指定的、已经被标记为Bind Aware的子游标中存储的含绑定变量的为此条件所对应的可选择率的范围）

###### 自适应游标的整体流程

1. 第一次执行，硬解析，根据一系列条件(如绑定变量、相关参数、是否有直方图等)来判断是否将该sql所对应的子游标标记为Bind Sensitive。标记后，对象的runtime统计信息额外地存储在该sql的子游标中。
2. 第二次执行，软解析，重用该sql第一次子游标的解析树和执行计划
3. 第三次执行时，如该sql对应的子游标被标记为Bind Sensitive，同时第二次和第三次记录的runtime统计信息和该sql第一次硬解析时所记录的runtime统计信息均存在较大差异，第三次会使用硬解析。产生新的子游标，且Oracle会标记新的子游标为Bind Aware。
4. 标记为Bind Aware的子游标所对应的目标sql，当该sql再次被执行时，Oracle就会根据当前传入的绑定变量值所对应的谓词条件可选择率，来决定此时使用哪种解析方式。
5. 如果硬解析，且所产生的执行计划和原有子游标相同，则除了生成新的子游标之外，还会把存储相同执行计划的原有子游标标记为非共享。

> 隐患：可能导致更多硬解析，因为需要对比可选择率。 可能导致一定数量的额外的子游标挂在同一个父游标下。这会增加软解析时查找匹配子游标的工作量。建议增大shared pool，或禁止自适应游标

> 一条sql绑定变量超过14个时，oracle11g不会使用绑定变量。

### Oracle里的应用类型

#### 应用类型一 硬解析

- 没有使用绑定变量，系统硬解析的比率非常高
- 性能和可扩展性是最差的，可用于olap类型系统
- 将CURSOR_SHARING设置为force，可以有效降低硬解析，提高系统的性能和可扩展性

#### 应用类型二 软解析

每次都会经历 Open Parse,Bind,Execute,Fetch Close

> 参数 SESSION_CACHED_CURSORS 修改为较大值，能进一步提升性能。如果为0，无法通过已缓存的Session Cursor中的父游标来建立目标sql的Session Cursor和父游标之间的联系。

- 有效降低了硬解析
- 执行时，其对应的Session Cursor都需要经历Open Parse,Bind,Execute,Fetch Close
- 由于软解析不断的open，close，以及对库缓存相关Latch的争用(Oracle11g之前的版本)，会影响性能
- SESSION_CACHED_CURSORS 修改为较大值，能进一步提升性能
- 纯粹的软解析很少见，SESSION_CACHED_CURSORS默认非0

#### 应用类型三 软软解析

当Session Cursor对应的sql解析和执行的次数超过3次，Oracle就不会对上述sql执行Close操作，而是标记为Soft Closed.

- open和Close只需经历一次，其他都需要经历，如Parse,Bind,Execute,Fetch

#### 应用类型四 一次解析，多次执行

该类型通过一些手段：PL/SQL代码的循环内部执行目标SQL，使得每一条SQL语句所对应的Session Cursor会反复经历Execute和Fetch，其他只用经历一次。

ITPub博客

【ORACLE】Oracle绑定变量知识梳理

最新文章