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_enameemp.ename%TYPE:=SCOTT;

l_empnoemp.empno%TYPE;

l_cursorINTEGER;

l_retvalINTEGER;

BEGIN

l_cursor:=dbms_sql.open_cursor;/*打开游标*/

dbms_sql.parse(l_cursor,SELECTempnoFROMempWHEREename=: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);/*执行游标*/

IFdbms_sql.fetch_rows(l_cursor)>0/*获取游标*/

THEN

dbms_sql.column_value(l_cursor,1,l_empno);

dbms_output.put_line(l_empno);

ENDIF;

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文本：

 

DROPTABLEt;

 

CREATETABLEt(nNUMBER,vVARCHAR2(4000));

 

ALTERSYSTEMFLUSHSHARED_POOL;

 

VARIABLEnNUMBER

VARIABLEvVARCHAR2(32)

 

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

 

INSERTINTOt(n,v)VALUES(:n,:v);

 

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

 

INSERTINTOt(n,v)VALUES(:n,:v);

 

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

 

INSERTINTOt(n,v)VALUES(:n,:v);

 

SELECTsql_id,child_number,executions

FROMv$sql

WHEREsql_text=INSERTINTOt(n,v)VALUES(:n,:v);

 

droptablet;

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

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

www.2cto.com

四. 绑定变量缺点

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

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

执行如下一段SQL文本：

 

VARIABLEidNUMBER

SETECHOON

ALTERSYSTEMFLUSHSHARED_POOL;

DROPTABLEt;

CREATETABLEt

AS

SELECTrownumASid,rpad(*,100,*)ASpad

FROMdual

CONNECTBYlevel<=1000;

 

ALTERTABLEtADDCONSTRAINTt_pkPRIMARYKEY(id);

 

BEGIN

dbms_stats.gather_table_stats(

ownname=>user,

tabname=>t,

estimate_percent=>100,

method_opt=>forallcolumnssize1

);

END;

/

 

EXECUTE:id:=990;

SELECTcount(pad)FROMtWHEREid<:id;

SELECT*FROMtable(dbms_xplan.display_cursor(NULL,NULL,basic));

EXECUTE:id:=10;

SELECTcount(pad)FROMtWHEREid<:id;

SELECT*FROMtable(dbms_xplan.display_cursor(NULL,NULL,basic));

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

 

五. 绑定变量使用场景

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

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

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

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