《oracle变量绑定与变量窥视合集》数据库环境leo1@leo1select*fromv$version;banner------------------------------------------------------------------------
《oracle 变量绑定与变量窥视合集》
数据库环境
leo1@leo1> select * from v$version;
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production
pl/sql release 11.2.0.1.0 - production
core 11.2.0.1.0 production
tns for linux: version 11.2.0.1.0 - production
nlsrtl version 11.2.0.1.0 - production
一 用示例说明绑定变量的应用领域是oltp而不是olap
变量绑定：这是一个老生常谈的话题，我所理解的绑定就是执行计划的绑定，我所理解的变量就是谓词替换的变量。
变量绑定机制：要说机制不得不说一下sql执行的过程，三部曲：解析 –> 执行 -> 取操作，美国空间，而绑定变量就发生在解析这一步，而解析又分成硬解析和软解析。
硬解析：当一条sql语句第一次执行时，首先生成执行计划，并把这个执行计划存放到shared_pool的library cache中，这个过程叫做硬解析。
软解析：如果sql语句已经被硬解析过了，那么可以直接从library cache中抽取现成的执行计划来重用，这个过程叫做软解析，目的减少生成执行计划这方面的资源消耗。为什么这么说呢，硬解析会消耗一些系统资源，尤其是cpu的资源，从而影响系统的效率，如果能把这方面的影响消除，那么对系统当然是多多益善了，香港虚拟主机，哈 多侃了几句。
sql详细执行过程：当oracle接收到一条sql语句时，首先会把这条sql语句字符做成哈希值，然后到library cache中寻找是否有和这个哈希值相匹配的sql存在，如果有就直接使用这个sql的执行计划去执行当前的sql语句，最后将结果返回给用户。如果没有找到相同的哈希值，oracle会认为这是一条新的sql，将会重新生成执行计划来执行（在这个过程中先要检查语法分析和语义分析），最后将结果返回给用户。
实验
下面我们演示一下绑定变量和非绑定变量在资源消耗上的差异
leo1@leo1> drop table leo1 purge; 清理环境
table dropped.
leo1@leo1> drop table leo2 purge;
table dropped.
leo1@leo1> create table leo1 as select * from dba_objects; 创建leo1
table created.
leo1@leo1> create table leo2 as select * from dba_objects; 创建leo2
table created.
leo1@leo1> alter session set tracefile_identifier='bind_variable'; 设置trace文件标识
session altered.
leo1@leo1> alter session set sql_trace=true; 启动trace功能，追踪sql资源消耗情况
session altered.
leo1@leo1> begin
for i in 1..100 loop
execute immediate 'select * from leo1 where object_id=:i' using i;
end loop;
end;
/
pl/sql procedure successfully completed.
我们对一条sql执行了100次并采用了绑定变量技术，oracle对这条sql只有一次硬解析，没有软解析，反复执行100次。
leo1@leo1> alter session set sql_trace=false; 关闭trace功能
session altered.
leo1@leo1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select * from leo1 where %';
sql_text parse_calls loads executions
-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------
select * from leo1 where object_id=:i 1 1 100
sql_text：我们跟踪的sql语句
parse_calls：硬解析+软解析次数 1次 只有硬解析没有软解析
loads：硬解析次数 1次
executions：执行次数 100次
虽说值隐藏在变量中，但在解析环节oracle认为是一样的
[oracle@leonarding1 trace]$ tkprof leo1_ora_16433_bind_variable.trc bind_variable.txt sys=no
tkprof: release 11.2.0.1.0 - development on fri feb 1 13:18:08 2013
copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.
使用tkprof工具过滤和汇总trace文件的，sys=no 不输出sys用户递归语句，默认yes，实际上设置成no更具有可读性
--------- 下面是bind_variable.txt文件信息
********************************************************************************
sql id: 0b74y9utb0b6r #这就是sql语句字符的哈希值
plan hash: 2716644435
select *
from
leo1 where object_id=:i
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
parse 1 0.00 0.00 0 0 0 0