绑定变量是Oracle解决硬解析的首要利器,能解决OLTP系统中librarycache的过度耗用以提高性能。然刀子磨的太快,使起来锋利,却容易折断。凡事皆有利弊二性,因地制宜,因时制宜,全在如何权衡而已。本文讲述了绑定变量的使用方法,以及绑定变量的优缺点、使用场合。
一、绑定变量
提到绑定变量,就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过,处于首次运行,则需要对其进行语法分析,语义识别,跟据统计信息生成最佳的执行计划,然后对其执行。而软解析呢,则是由于在librarycache已经存在与该SQL语句一致的SQL语句文本、运行环境,即有相同的父游标与子游标,采用拿来主义,直接执行即可。软解析同样经历语法分析,语义识别,且生成hashvalue,接下来在librarycache搜索相同的hashvalue,如存在在实施软解析。
绑定变量
首先其实质是变量,有些类似于我们经常使用的替代变量,替代变量使用&占位符,只不过绑定变量使用:
替代变量使用时为&variable_para,相应的绑定变量则为:bind_variable_para
通常一个SQL语句包含动态部分和静态部分,占位符实质是SQL语句中容易发生变化的部分,通常为其条件或取值范围。动态部分在一般情况下(数据倾斜除外),对执行计划的生成的影响是微乎其微的。故同一SQL语句不同的动态部分产生的执行计划都是相同的。
二、绑定变量的使用
1、在SQLPlus中使用绑定变量
SQL> variable eno number;-->使用variable定义变量
SQL>exec:eno:=7788;
SQL>selectename,job,salfromempwhereempno=:eno;
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
SQL> col sql_text format a55
SQL>selectsql_id,sql_text,executionsfromv$sqlarea-->首次查询后在v$sqlarea保存父游标且执行次数EXECUTIONS为1
2wheresql_textlike'%select ename,job,sal%'andsql_textnotlike'%from v$sql%';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------- ----------
dbc6vx6z6n1zvselectename,job,salfromempwhereempno=:eno 1
SQL>selectsql_id,hash_value,child_number,sql_textfromv$sql-->查询视图v$sql查看该SQL对应的子游标,且CHILD_NUMBER为0
2wheresql_textlike'%select ename,job,sal%'andsql_textnotlike'%from v$sql%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ -------------------------------------------------------
dbc6vx6z6n1zv 3194619899 0selectename,job,salfromempwhereempno=:eno
SQL>exec:eno:=7369;
SQL>selectename,job,salfromempwhereempno=:eno;-->再次对变量赋值并查询
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
SQL>exec:eno:=7521
SQL>selectename,job,salfromempwhereempno=:eno;
ENAME JOB SAL
---------- --------- ----------
WARD SALESMAN 1250
SQL>selectsql_id,sql_text,executionsfromv$sqlarea-->视图v$sqlarea中EXECUTIONS值为3,对应的SQL被执行了3次
2wheresql_textlike'%select ename,job,sal%'andsql_textnotlike'%from v$sql%';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------- ----------
dbc6vx6z6n1zvselectename,job,salfromempwhereempno=:eno 3
-->视图v$sql中对应的子游标也实现了完全共享,保持CHILD_NUMBER为0
SQL>selectsql_id,hash_value,child_number,sql_textfromv$sql
2wheresql_textlike'%select ename,job,sal%'andsql_textnotlike'%from v$sql%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ -------------------------------------------------------
dbc6vx6z6n1zv 3194619899 0selectename,job,salfromempwhereempno=:eno
2、PL/SQL块中使用绑定变量
SQL>createtablet(id number,val number);-->首先创建表t
SQL> get get_parse.sql
1selectname,valuefromv$mystat ajoinv$statname b
2*ona.statistic#=b.statistic#whereb.namelike'parse count%';
SQL> @get_parse.sql-->获得当前的解析情况,此时hard parase 为63
NAMEVALUE
------------------------- ----------
parsecount(total) 394
parsecount(hard) 63
parsecount(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条记录
2foriin1..30 loop
3insertintotvalues(i,i*2);
4endloop;
5commit;
6end;
7 /
PL/SQLproceduresuccessfully completed.
SQL> @get_parse-->代码执行后的结果,硬解析数量仅仅增加了3次
NAMEVALUE
------------------------- ----------
parsecount(total) 401
parsecount(hard) 67
parsecount(failures) 1
31/3123>