Oracle 绑定变量和普通变量效率详解

1 概述

1. sql 语句执行步骤
   (1) sql 语句的执行要经过 "解析、执行、提取" 等几个阶段
   (2) 其中解析最消耗资源，解析的过程中要进行语法、语义和权限的检查
   (3) 如果这些检查都通过了，才开始执行，执行完成之后将 sql 语句的执行计划存储在共享池中 -- '硬解析'
       如果下一次有 '相同的 sql 语句要执行，则不需要解析'，直接按照已经存在的执行计划执行，就可以节省资源 -- '软解析'

2. 绑定变量的作用：'生成相同的 sql 语句，减少语句解析带来的消耗'
   (1) 相同的 sql 语句：完全一样（'大小写，间隔' 等等）

3. 写法：select * from stu_info t where t.sno = :b1;  

2 语法

1. 查询共享池中缓存的 sql 语句
   select * from v$sql t where t.sql_text like '%sql语句%';
   
2. 清空共享池（缓存），切勿在 生产库操作 -- 本地环境玩玩就行
   alter system flush shared_pool;

示例：

select * from stu_info t where t.sno = 1;
select * from stu_info t where t.sno = 2;

-- select * from stu_info t where t.sno = :b1;

查询结果：（2 条缓存记录）

3 示例

基础数据准备：

create table stu_info (
  sno   number(10),
  sname varchar2(30)
);

create table stu_info_bind as select * from stu_info where 1 = 2;

示例：insert 20W 条记录，数据越大，差异越大（update、delete 同理）

DECLARE
  v_init_date     PLS_INTEGER; -- 100 = 1秒
  v_end_date      PLS_INTEGER;
  v_end_date_bind PLS_INTEGER;
BEGIN
  v_init_date := dbms_utility.get_time;

  -- 普通 insert 测试
  FOR i IN 1 .. 200000 LOOP
    INSERT INTO stu_info (sno, sname) VALUES (i, 'a' || i);
  END LOOP;

  v_end_date := dbms_utility.get_time;

  -- 绑定变量 insert 测试
  FOR j IN 1 .. 200000 LOOP
    EXECUTE IMMEDIATE 'INSERT INTO stu_info_bind(sno, sname) VALUES(:b1, :b2)'
      USING j, 'b' || j;
  END LOOP;

  v_end_date_bind := dbms_utility.get_time;

  -- commit;

  -- 执行时间
  dbms_output.put_line('普通 insert 用时: ' ||
                       to_char(v_end_date - v_init_date));
  dbms_output.put_line('绑定变量 insert 用时: ' ||
                       to_char(v_end_date_bind - v_end_date));
END;

查询结果：

普通 insert 用时: 1049
绑定变量 insert 用时: 992