绑定变量是Oracle解决硬解析的首要利器，能解决OLTP系统中librarycache的过度耗用以提高性能。然刀子磨的太快，使起来锋利，却容易折断。凡事皆有利弊二性，因地制宜，因时制宜，全在如何权衡而已。本文讲述了绑定变量的使用方法，以及绑定变量的优缺点、使用场合。
一、绑定变量
提到绑定变量，就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过，处于首次运行，则需要对其进行语法分析，语义识别，跟据统计信息生成最佳的执行计划，然后对其执行。而软解析呢，则是由于在librarycache已经存在与该SQL语句一致的SQL语句文本、运行环境，即有相同的父游标与子游标，采用拿来主义，直接执行即可。软解析同样经历语法分析，语义识别，且生成hashvalue，接下来在librarycache搜索相同的hashvalue，如存在在实施软解析。
绑定变量
首先其实质是变量，有些类似于我们经常使用的替代变量，替代变量使用&占位符，只不过绑定变量使用：
替代变量使用时为&variable_para，相应的绑定变量则为：bind_variable_para
通常一个SQL语句包含动态部分和静态部分，占位符实质是SQL语句中容易发生变化的部分，通常为其条件或取值范围。动态部分在一般情况下（数据倾斜除外），对执行计划的生成的影响是微乎其微的。故同一SQL语句不同的动态部分产生的执行计划都是相同的。
二、绑定变量的使用
1、在SQLPlus中使用绑定变量
- SQL> variable eno number;
- 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
- 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
- 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
- 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
-
-
- 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);
-
- 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
-
- NAME VALUE
-
- parse count (total) 394
- parse count (hard) 63
- parse count (failures) 1
-
-
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson_0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
-
- SQL> begin
- 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
-
- NAME VALUE
-
- parse count (total) 401
- parse count (hard) 67
- parse count (failures) 1
|
