oracle绑定变量学习

绑定变量(binding variable)

一、为什么要绑定变量

    在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.
一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的门闩（latch）资源，
严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。

   当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，
如果有的话只须执行软分析即可，否则就得进行硬分析。而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。
绑定变量能够使得每次提交的sql语句都完全一样。

   在Oracle中存在两种类型的SQL语句，一类为DDL语句，他们是从来不会共享使用的，也就是每次执行都需要进行硬解析。
还有一类就是DML语句，他们会根据情况选择要么进行硬解析，要么进行软解析。


二、在Oracle中SQL语句的解析步骤如下：
1、 语法检测。判断一条SQL语句的语法是否符合SQL的规范，比如执行：SQL> selet * from emp; 我们就可以看出由于Select关键字少了一个“c”，这条语句就无法通过语法检验的步骤了。
2、 语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确，用户是否有权限访问或更改相应的表或列。
3、 检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该SQL语句将会被软解析，也就是可以重用已解析过的语句的 执行计划和优化方案，可以忽略语句解析过程中最耗费资源的步骤。这个步骤又可以分为两个步骤：
（1）验证SQL语句是否完全一致。
     Oracle将会对传递进来的SQL语句使用HASH函数运算得出HASH值，再与共享池中现有语句的 HASH值进行比较看是否一一对应。
     现有数据库中SQL语句的HASH值我们可以通过访问v$sql、v$sqlarea、v$sqltext等数据字典中的HASH_VALUE列查询得出。

（2）验证SQL语句执行环境是否相同。比如同样一条SQL语句，一个查询会话加了/*+ first_rows */的HINT，
另外一个用户加/*+ all_rows */的HINT，他们就会产生不同的执行计划，尽管他们是查询同样的数据。

4、 Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。
    如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，
最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。
这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，
Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。


三、绑定变量示例
普通sql语句：
SQL> select * from mytest where random_id=30;
SQL> select * from mytest where random_id=31;
SQL> select * from mytest where random_id=32;

含绑定变量的sql 语句：
SQL> select * from mytest where random_id=:x;
SQL> select * from mytest where random_id='&random_id';

Sql*plus 中使用绑定变量：
SQL> set timing on
SQL> variable y number;
SQL> exec :y :=39;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> select * from mytest where random_id=:y;
SQL> exec :y :=21;
PL/SQL procedure successfully completed.
SQL> select * from mytest where random_id=:y;   ---改变y的值，重复执行此sql

pl/sql中使用绑定变量：
pl/sql很多时候都会自动绑定变量，如下例：
SQL> create or replace procedure p_mytest(p_no in number)
as
  begin
    update mytest set random_id=random_id+2 where random_id = p_no;
    commit;
  end;
/
Procedure created.
Elapsed: 00:00:00.17

SQL> exec p_mytest(20);   ---执行该存储过程
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

在pl/sql中，引用变量即是引用绑定变量.
但在pl/sql中动态sql并不是这样,在vb，java以及其他应用程序中都得显式地利用绑定变量。

SQL> select sql_id,hash_value,plan_hash_value,executions,sql_text from v$sql where upper(sql_text) like 'SELECT * FROM MYTEST%';

SQL_ID        HASH_VALUE PLAN_HASH_VALUE      EXECUTIONS SQL_TEXT
------------- ---------- --------------- ---------- --------------------------------------------------------------------------------
fvbfv6r7xhrx0 3487063968      1137542690          5 select * from mytest where ROWNUM <10
cw7d99wy2d7sa 1009164042      1137542690          3 select * from mytest where ROWNUM <5
6dwn6j9cw1dp2 1505801890      1692938441          2 select * from mytest where random_id=:x    ---包含一次软解析
g7mfz99r6dfpw 1852226236      1692938441          1 select * from mytest where random_id='39'
8v2bw6pq8tx7m 1821177075      1692938441          0 select * from mytest where random_id=30
0zyay1mv7kvys 4135153624      1137542690          2 select * from mytest where rownum<5
bdbtr8cb7fzka  376929866      1692938441          1 select * from mytest where random_id=20

说明：每条语句都有一个唯一的HASH_VALUE,相当于你语句的一个ID，
v$sql和v$sql_plan的hash_value,如果两条相同的话ORACLE会认为这两条语句是同一条语句，不会再次做硬解析，plan_hash_value是执行计划的hash值，数值的大小没啥意义。

查看某个sql具体的执行计划：
SQL> select plan_table_output from table(dbms_xplan.display_awr('g7mfz99r6dfpw'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g7mfz99r6dfpw
--------------------
select * from mytest where random_id='39'
Plan hash value: 1692938441
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    24 (100)|          |
|   1 |  TABLE ACCESS FULL| MYTEST |   107 |   213K|    24   (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
17 rows selected


1.对比区别
1.1 未绑定变量
set echo on;
set serveroutput on; (把执行结果显示出来)
set timing on(打开记时器.)

SQL> declare
  type rc is ref cursor;
  l_rc rc;
  l_du all_objects.OBJECT_NAME%type;
  l_ti number default dbms_utility.get_time;   --dbms_utility.get_time本身是一个时间的数值
begin
  for i in 1 .. 1000 loop
    open l_rc for 'select object_name from all_objects where object_id=' || i;
    fetch l_rc into l_du;
  close l_rc;
  end loop;
  dbms_output.put_line(round((dbms_utility.get_time-l_ti)/100,2) ||'seconds');  --通过差集/100 可以大概预估整个过程执行时间
end;
/
64.2seconds
PL/SQL procedure successfully completed.
Elapsed: 00:01:04.31                      ---用时64s

1.2 绑定变量
SQL> declare
  type rc is ref cursor;
  l_rc rc;
  l_du all_objects.OBJECT_NAME%type;
  l_ti number default dbms_utility.get_time;
begin
  for i in 1 .. 1000 loop
    open l_rc for 'select object_name from all_objects where object_id=:x' using i;
    fetch l_rc  into l_du;
  close l_rc;
  end loop;
  dbms_output.put_line(round((dbms_utility.get_time-l_ti)/100,2) ||'seconds');
end;
/
.52seconds

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.55                    ---用时不超过1s


2）绑定方法
2.1 静态绑定变量
set serveroutput on;
set timing on;
declare
  l_num number;
  l_p1  int;
begin
  l_p1:=5;
  select count(*) into l_num from mytest where random_id = l_p1;
  dbms_output.put_line(l_num);
end;
/
94
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
在上面的情况，Oracle会自己绑定变量，select语句只会编译一次。

2.2 动态绑定变量
declare
  l_sql varchar2(2000);
  l_num number;
  l_p1  int;
begin
  l_p1:=5;
  l_sql:='select count(*) into :z from mytest where random_id =:y';
  execute immediate l_sql into l_num using l_p1;
  dbms_output.put_line(l_num);
end;
/
94
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00

2.3 dbms_output的绑定变量使用
declare
  l_id  integer;
  l_ti  number default dbms_utility.get_time;
  l_num integer;
begin
  l_id := dbms_sql.open_cursor;
  for i in 1 .. 1000 loop
    dbms_sql.parse(l_id,'insert into t3 values(:username,:user_id,sysdate)',dbms_sql.v7);
    dbms_sql.bind_variable(l_id, 'username', 'test' || to_char(i));
    dbms_sql.bind_variable(l_id, 'user_id', i);
    l_num := dbms_sql.execute(l_id);
  end loop;
  dbms_sql.close_cursor(l_id);
  dbms_output.put_line(round((dbms_utility.get_time - l_ti) / 100, 2) ||'seconds');
end;
/
.4seconds
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.41                        ---用时0.4s

查看数据:
SQL> select count(*) from t3;
  COUNT(*)
----------
      1000

SQL> select * from t3 where rownum <5;
USERNAME   USER_ID RECORD_TIME
------------------------------ ---------- ------------------------------
test879       879 25-DEC-15 03.46.31.000000 PM
test880       880 25-DEC-15 03.46.31.000000 PM
test881       881 25-DEC-15 03.46.31.000000 PM
test882       882 25-DEC-15 03.46.31.000000 PM

说明：
     dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句;
     dbms_sql.bind_variable(v_cursor, ':sid', s_id);   --绑定输入参数;

四、查询占用资源较多的sql
select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem ;   --占用内存超过100k的sql内容

五. 绑定变量使用限制条件是什么?
    为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA的共享池(shared buffer pool)中
的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,
ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

   数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.
当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,
要达成共享,SQL语句必须完全相同(包括空格,换行等).
共享的语句必须满足三个条件:

A. 字符级的比较:
当前被执行的语句和共享池中的语句必须完全相同.
例如:
SELECT * FROM EMP;
和下列每一个都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;

B. 两个语句所指的对象必须完全相同:
例如:tina.mytest

C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)
下面两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)
select * from mytest where random_id=:x;
select * from mytest where random_id=:y;


补充：在Java中的使用绑定变量
Java代码
PreparedStatement pstmt = conn.prepareStatement("select * from table_name where id = ?" );;  
pstmt.setInt(1 ,1 );;  
rset = pstmt.executeQuery();;  
...  
pstmt.setInt(1 ,2 );;  
rset = pstmt.executeQuery();;  

这样Oracle数据库就知道你实际上用的都是同一条sql语句，会以这样的形式：
select * from table_name where id = :1

解析执行后存放在sql区域里面，当以后再有一样的sql的时候，把参数替换一下，就立刻执行，不需要再解析sql了。
既加快了sql执行速度，也不会占有过多SGA的share pool。

String v_id = 'xxxxx';
String v_sql = 'select name from ta where id = ? '; //嵌入绑定变量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();
在Java中，结合使用setXXX 系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL 语句的性能。