Oracle 游标与绑定变量

 

oracle执行SQL语句就是打开游标，解析游标，执行游标，关闭游标的过程。了解游标的这几个阶段，我们也就弄清楚了SQL执行过程，这是本文要介绍的第一个内容。另外，在java编程中，我们通常说要使用预处理的形式来写SQL语句（比如：select  * from table where A = ?），也就是绑定变量的形式。因为，这样效率高。那么，为什么使用绑定变量就比不使用绑定变量（比如：select * from table where A = '123'）要效率高呢？这是本文要介绍的第二个内容。

        一. 游标的生命周期

       一条sql语句的执行过程，就是一个游标的生命周期。如下图所示：

     1. 打开游标：系统为这个游标分配一个内存结构。

        2. 解析游标：将一条SQL与这个游标关联。解析这条sql语句，将解析的结果加载到共享池中。

        3. 定义输出变量：如果这条SQL返回数据，先定义接收数据的变量。

        4. 定义输入变量：如果SQL语句使用了绑定变量，提供他们的值。

        5. 执行游标：执行SQL语句。

        6. 获取游标：如果SQL语句有返回数据，接收返回的数据。

        7. 关闭游标：释放第一步分配的内存，供其他游标使用，但是第二步解析的SQL结果（也就是共享游标）不会被释放，以期待被重新使用。

        我们可以通过一段PL/SQL代码来看一下游标的这几个步骤：      

DECLARE
  l_ename emp.ename%TYPE := 'SCOTT';
  l_empno emp.empno%TYPE;
  l_cursor INTEGER;
  l_retval INTEGER;
BEGIN
  l_cursor := dbms_sql.open_cursor; /*打开游标*/
  dbms_sql.parse(l_cursor, 'SELECT empno FROM emp WHERE ename = :ename', 1); /*解析游标*/
  dbms_sql.define_column(l_cursor, 1, l_empno); /*定义输出变量*/
  dbms_sql.bind_variable(l_cursor, ':ename', l_ename); /*定义输入变量*/
  l_retval := dbms_sql.execute(l_cursor); /*执行游标*/
  IF dbms_sql.fetch_rows(l_cursor) > 0  /*获取游标*/
  THEN
    dbms_sql.column_value(l_cursor, 1, l_empno);
    dbms_output.put_line(l_empno);
  END IF;
  dbms_sql.close_cursor(l_cursor); /*关闭游标*/
END;

 

        二. 游标的解析过程

       在游标的这几个过程中，我们唯一能影响的就是解析过程。解析过程的快与慢，与我们写的sql语句有直接关系。那么游标的解析过程（也就是SQL的解析过程）是怎样的呢？看下图：

       1. 包含VPD的约束条件检查：如果系统中使用了虚拟私有数据库，并且被解析的SQL语句中引用的某张表激活了它的话，安全策略生成的约束条件会被添加到where条件中（说实话，这个我也没看懂，先不管）

       2. 语法，语义以及访问权限检查：就是检查我们写的SQL写得对不对，引用的表是否存在等。

       3. 将父游标保存到库缓存：如果没有找到共享的父游标，就会在库缓存中缓存这个父游标。父游标保存的是这条SQL的文本信息，今后如果重新执行这条SQL语句，这个父游标是可以重用的。

       4. 逻辑优化与物理优化：生成这条SQL所有可能的执行计划，然后根据执行计划的开销，选择开销最小的一条执行计划。

       5. 将子游标保存到库缓存：上一步选择的最优执行计划信息和当前的执行环境，会当做子游标的信息保存到库缓存，并与父游标关联。

       总之，父游标保存的是SQL文本信息，今后可以被重用。子游标保存的是当前执行环境下所选择的这条SQL最优的执行计划，如果父游标被重用，执行环境没变，那么子游标也会被重用。

       当父游标和子游标都可重用，那么只需要执行前2步，此时对应的解析称为软解析。如果父游标与子游标都不可重用，所有的步骤都执行的时候，就是我们说的硬解析。因为硬解析里面的逻辑优化与物理优化是非常依赖cpu的操作，所以硬解析相对而言是比较耗时的。也就是我们为什么说要尽可能避免硬解析。

       三. 绑定变量优点

       绑定变量可以有效消除硬解析，我们执行如下一段SQL文本：

DROP TABLE t;

CREATE TABLE t (n NUMBER, v VARCHAR2(4000));

ALTER SYSTEM FLUSH SHARED_POOL;

VARIABLE n NUMBER
VARIABLE v VARCHAR2(32)

EXECUTE :n := 1; :v := 'Helicon';

INSERT INTO t (n, v) VALUES (:n, :v);

EXECUTE :n := 2; :v := 'Trantor';

INSERT INTO t (n, v) VALUES (:n, :v);

EXECUTE :n := 3; :v := 'Kalgan';

INSERT INTO t (n, v) VALUES (:n, :v);

SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

drop table t;

        我们会发现最后一个select语句执行的结果如下图所示：

 

     说明对应这条insert语句只生成了一个父游标，只是这个父游标被执行了3次。也就是除开第一次插入的时候，我们进行了硬解析。接下来2次，我们都进行的是软解析。

       四. 绑定变量缺点

       影响oracle选择效率低下的执行计划。

       由于使用绑定变量，父游标和子游标都能共享重用（除开第一次硬解析，其他每次都是软解析）。子游标每次都重用（除开第一次），那么执行计划每一次都相同。假如子游标里面的执行计划确认进行的是全表扫描，因为第一次要查询这个表里面绝大部分数据，oracle认为执行全表扫描快。如果第二次只需要扫描很小一部分数据，执行索引扫描比较快的话。由于子游标重用，还执行的是全表扫描。我们可以看一个例子：

      执行如下一段SQL文本：

VARIABLE id NUMBER
SET ECHO ON
ALTER SYSTEM FLUSH SHARED_POOL;
DROP TABLE t;
CREATE TABLE t
AS
SELECT rownum AS id, rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level <= 1000;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

BEGIN
  dbms_stats.gather_table_stats(
    ownname          => user,
    tabname          => 't',
    estimate_percent => 100,
    method_opt       => 'for all columns size 1'
  );
END;
/

EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

       我们发现当 id=10, 也就是查询非常小部分数据的时候，仍然执行的是全表扫描，如下图：

       

       五. 绑定变量使用场景

       什么时候应该使用绑定变量，什么时候又应该避免呢？

       我们可以看到，使用绑定变量主要是为了避免硬解析，也就是加快SQL的解析时间，但是有可能导致Oracle重用效率低下的执行计划。也就是延长SQL的执行时间。这个时候我们应该权衡，这条SQL是解析时间比较长还是执行时间。

       1. 如果一次只处理小部分数据，解析时间等于或者高于执行时间，那么建议使用绑定变量。

       2. 如果一次处理大批量数据，执行时间高于解析时间几个数量级，那么没必要使用绑定变量，加快那么一点解析时间微不足道。而且还有可能导致oracle重用效率低下的执行计划，大大影响SQL的执行速度。