绑定变量是Oracle解决硬解析的首要利器,能解决OLTP系统中library cache的过度耗用以提高性能。然刀子磨的太快,使起来锋利,却容 易折断。凡事皆有利弊二性,因地制宜,因时制宜,全在如何权衡而已。本文讲述了绑定变量的使用方法,以及绑定变量的优缺点、使用场合。
一、绑定变量
提到绑定变量,就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过,处于首次运行,则需要对其进行语法分析,语 义识别,跟据统计信息生成最佳的执行计划,然后对其执行。而软解析呢,则是由于在library cache已经存在与该SQL语句一致的SQL语句文本 、运行环境,即有相同的父游标与子游标,采用拿来主义,直接执行即可。软解析同样经历语法分析,语义识别,且生成hash value ,接下来 在library cache搜索相同的hash value ,如存在在实施软解析。有关更多的硬解析与软解析以及父游标,子游标请作如下参考: 有关硬解析与软解析,请参考:Oracle 硬解析与软解析 有关父游标、子游标,请参考:父游标、子游标与共享游标 绑定变量 首先其实质是变量,有些类似于我们经常使用的替代变量,替代变量使用&占位符,只不过绑定变量使用: 替代变量使用时为 &variable_para,相应的绑定变量则为 :bind_variable_para 通常一个SQL语句包含动态部分和静态部分,占位符实质是SQL语句中容易发生变化的部分,通常为其条件或取值范围。动态部分在一般情 况下(数据倾斜除外),对执行计划的生成的影响是微乎其微的。故同一SQL语句不同的动态部分产生的执行计划都是相同的。 二、绑定变量的使用 1、在SQLPlus中使用绑定变量
SQL> variable eno number; -->使用variable定义变量
SQL> exec :eno:=7788;
SQL> select ename,job,sal from emp where empno=:eno;
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
SQL> col sql_text format a55
SQL> select sql_id,sql_text,executions from v$sqlarea -->首次查询后在v$sqlarea保存父游标且执行次数EXECUTIONS为1
2 where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------- ----------
dbc6vx6z6n1zv select ename,job,sal from emp where empno=:eno 1
SQL> select sql_id,hash_value,child_number,sql_text from v$sql -->查询视图v$sql查看该SQL对应的子游标,且CHILD_NUMBER为0
2 where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ -------------------------------------------------------
dbc6vx6z6n1zv 3194619899 0 select ename,job,sal from emp where empno=:eno
SQL> exec :eno:=7369;
SQL> select ename,job,sal from emp where empno=:eno; -->再次对变量赋值并查询
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
SQL> exec :eno:=7521
SQL> select ename,job,sal from emp where empno=:eno;
ENAME JOB SAL
---------- --------- ----------
WARD SALESMAN 1250
SQL> select sql_id,sql_text,executions from v$sqlarea -->视图v$sqlarea中EXECUTIONS值为3,对应的SQL被执行了3次
2 where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------- ----------
dbc6vx6z6n1zv select ename,job,sal from emp where empno=:eno 3
-->视图v$sql中对应的子游标也实现了完全共享,保持CHILD_NUMBER为0
SQL> select sql_id,hash_value,child_number,sql_text from v$sql
2 where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ -------------------------------------------------------
dbc6vx6z6n1zv 3194619899 0 select ename,job,sal from emp where empno=:eno
2、PL/SQL块中使用绑定变量
SQL> create table t(id number,val number); -->首先创建表t
SQL> get get_parse.sql
1 select name,value from v$mystat a join v$statname b
2* on a.statistic#=b.statistic# where b.name like 'parse count%';
SQL> @get_parse.sql -->获得当前的解析情况,此时hard parase 为63
NAME VALUE
------------------------- ----------
parse count (total) 394
parse count (hard) 63
parse count (failures) 1
-->下面的pl/sql代码中,Oracle实现自动变量自动绑定,执行了30次的insert操作,但oracle认为每次执行的语句都是一样的
/**************************************************/
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: robinson_0612@hotmail.com */
/* QQ: 645746311 */
/**************************************************/
SQL> begin -->执行pl/sql代码,向表t中插入30条记录
2 for i in 1..30 loop
3 insert into t values(i,i*2);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> @get_parse -->代码执行后的结果,硬解析数量仅仅增加了3次
NAME VALUE
------------------------- ----------
parse count (total) 401
parse count (hard) 67
parse count (failures) 1
3、在存储过程或包中使用绑定变量
-->存储过程和保重,对参数的传递即是使用自动绑定变量来实现,因此编程人员无须操心绑定变量问题,如下例所示:
SQL> create or replace procedure ins_t(p_id in number,p_value in number) -->创建一个过程用于向表t插入记录
2 as
3 begin
4 insert into t values(p_id,p_value);
5 commit;
6 end;
7 /
Procedure created.
SQL> select sid,serial# from v$session where username='SCOTT'; -->获得当前用户的sid,serial#
SID SERIAL#
---------- ----------
1084 938
SQL> exec dbms_monitor.session_trace_enable(session_id=>1084,serial_num=>938); -->对当前的session启用跟踪
PL/SQL procedure successfully completed.
SQL> exec ins_t(31,62); -->执行存储过程
PL/SQL procedure successfully completed.
SQL> exec ins_t(32,64);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable(session_id=>1084,serial_num=>938); -->关闭对session的跟踪
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 180
SQL> COLUMN trace_file FORMAT A100
SQL> SELECT s.sid, -->获得跟踪文件位置
2 s.serial#,
3 p.spid,
4 pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
5 '_ora_' || p.spid || '.trc' AS trace_file
6 FROM v$session s,
7 v$process p,
8 v$parameter pa
9 WHERE pa.name = 'user_dump_dest'
10 AND s.paddr = p.addr
11 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
SID SERIAL# SPID TRACE_FILE
---------- ---------- ------------ --------------------------------------------------------------
1084 938 10883 /u02/database/CNMMBO/udump/cnmmbo_ora_10883.trc
SQL> SQL>
SQL> ho pwd
/users/oracle
-->使用tkprof工具格式化跟踪文件便于阅读
SQL> ho tkprof /u02/database/CNMMBO/udump/cnmmbo_ora_10883.trc /users/oracle/ins_t.txt explain=goex_admin/goex_admin
TKPROF: Release 10.2.0.3.0 - Production on Fri Sep 9 12:55:18 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> ho cat /users/oracle/ins_t.txt -->查看跟踪文件
......
BEGIN ins_t(31,62); END;
......
INSERT INTO T -->可以看到insert into语句中使用了绑定变量
VALUES
(:B2 ,:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.11 0.11 2 281 27 2
.......
4、在动态SQL中是使用绑定变量
-->动态SQL中不能自动使用绑定变量,需要手动设定绑定变量
SQL> @get_parse -->获得当前hard parse解析情况,此时为120
NAME VALUE
------------------------- ----------
parse count (total) 533
parse count (hard) 120
parse count (failures) 1
SQL> begin
2 for i in 1..30 loop
3 execute immediate 'insert into t values(:1,:2)' using i,i+i-2; -->动态SQL使用绑定变量,该语句将执行30次
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> @get_parse --> 动态SQL执行后,尽管执行了30次,但硬解析数量仅仅增加了2次
NAME VALUE
------------------------- ----------
parse count (total) 537
parse count (hard) 122
parse count (failures) 1
SQL> set serveroutput on;
SQL> get get_sal.sql -->下面的pl/sql中使用了绑定变量
1 DECLARE
2 TYPE emp_cur IS REF CURSOR;
3 my_emp_cur emp_cur;
4 my_emp_rec emp%ROWTYPE;
5 BEGIN
6 OPEN my_emp_cur FOR 'select * from emp where deptno=:dno' USING 10;
7 LOOP
8 FETCH my_emp_cur INTO my_emp_rec;
9 EXIT WHEN my_emp_cur%NOTFOUND;
10 dbms_output.put_line(my_emp_rec.ename||'''s salary is : '||my_emp_rec.sal);
11 END LOOP;
12* END;
13 /
CLARK's salary is : 4900
KING's salary is : 5000
MILLER's salary is : 1300
PL/SQL procedure successfully completed.
SQL> /
CLARK's salary is : 4900
KING's salary is : 5000
MILLER's salary is : 1300
PL/SQL procedure successfully completed.
SQL> select sql_text,executions,sql_id from v$sqlarea where sql_text like 'select * from emp where deptno=:dno%';
SQL_TEXT EXECUTIONS SQL_ID
--------------------------------------------- ---------- -------------
select * from emp where deptno=:dno 2 c1nx6x02h655a
三、绑定变量的优缺点及使用场合 优点: 可以在library cache中共享游标,避免硬解析以及与之相关的额外开销 在大批量数据操作时将呈数量级来减少闩锁的使用,避免闩锁的竞争 缺点: 绑定变量被使用时,查询优化器会忽略其具体值,因此其预估的准确性远不如使用字面量值真实,尤其是在表存在数据倾斜(表上的数 据非均匀分布)的列上会提供错误的执行计划。从而使得非高效的执行计划被使用。 使用场合: OLTP 在OLTP系统中SQL语句重复执行频度高,但处理的数据量较少,结果集也相对较小,尤其是使用表上的索引来缩小中间结果集,其 解析时间通常会接近或高于执行时间,因此该场合适合使用绑定变量。 OLAP 在OLAP系统中,SQL语句执行次数相对较少,但返回的数据量较大,因此多数情况下倾向于使用权标扫描更高效,其SQL语句执行时 间远高于其解析时间,因此使用绑定变量对于总响应时间影响不大。而且增加生成低效执行计划的风险。即在在OLAP系统中使用字 面量的性能高于使用绑定变量。 注意: 对于实际的数据库对象,如(表,视图,列等),不能使用绑定变量替换,只能替换字面量。如果对象名是在运行时生成的,则需要对其 用字符串拼接,同时,sql只会匹配已经在共享池中相同的对象名。