绑定变量之基本概念
在oracle数据库对于一条SQL，可能存在硬解析、软解析以及软软解析。
硬解析(Hard Parse)是指Oracle在执行目标SQL时，在库缓存找不到可以重用的解析树和执行计划，而不得不从头开始解析SQL并生成相应的Parent Cursor和Child Cursor的过程。硬解析不仅仅耗费CPU等硬件资源，更重要的是其会导致闩(Shared Pool Latch以及Library Cache Latch)等争用。大量的硬解析会严重影响系统的性能及其扩展性，尤其对于OLTP系统而言。
那么怎样才能降低OLTP应用系统硬解析的数量呢？-----绑定变量。绑定变量的实质就是用一种特殊类型的变量后者称之为占位符替代SQL语句中的动态部分，从而保证每次提交的语句都一样。Oracle数据库中绑定变量的语法规则为：":variable_name"。下面通过具体实验来说明绑定变量的具体用法。
1.简单SELECT语句使用绑定变量
执行不含任何绑定变量的SQL语句如下：
查询v$sqlarea视图：
两条语句唯一的区别在于WHERE条件不同，且可以看到两条语句的执行计划都是索引范围扫描。仅仅由于SQL文本的细微差别，导致数据库不得不进行两次硬解析，占用了系统的宝贵资源。
我们尝试用绑定变量重复上面的工作，看一下硬解析的次数以及效果。
VERSION_COUNT=1,说明两条语句只进行了一次硬解析（从v$sql输出结果也印证了这一点），并执行了EXECUTIONS=2次。相比前面未使用绑定变量的情况，使用绑定变量减少了硬解析的次数。
2.存储过程使用绑定变量
分别创建两个PROCEDURE，两个存储过程的作用一样都是向表插入10万条数据，唯一不同的是其中PROC1未使用变量，PROC2使用绑定变量。
下面具体看一下硬解析次数以及执行时间:
proc1的执行情况如下：
执行前后硬解析次数增加了10万次左右(100313-312=100001),每一次insert 都进行一次硬解析。同时注意到执行时间为1分钟23秒。
proc2的执行情况：
执行前后硬解析次数仅增加2次(100347-100345)，执行时间仅用了11秒。
看到PROC1的执行时间几乎是PROC2执行时间的8倍。
3.JAVA语言使用绑定变量的情况
java PrepareStatement对象，可以将sql语句做预编译操作，被封装的sql语句可以包含动态参数，减少编译的次数，提高数据库性能.PrepareStatement的具体使用方法如下：
【总结】
TOM曾说过：Oracle将已解析、已编译的SQL连同其他内容存储在共享池(shared pool)中，这个是系统全局区(System Golbal Area,SGA)中一个非常重要的共享内存结构。如果你确实想让Oracle缓慢运行，甚至几近停顿，只要根本不使用绑定变量就可以办到，足以见绑定变量的重要性。
硬解析(Hard Parse)是指Oracle在执行目标SQL时，在库缓存找不到可以重用的解析树和执行计划，而不得不从头开始解析SQL并生成相应的Parent Cursor和Child Cursor的过程。硬解析不仅仅耗费CPU等硬件资源，更重要的是其会导致闩(Shared Pool Latch以及Library Cache Latch)等争用。大量的硬解析会严重影响系统的性能及其扩展性，尤其对于OLTP系统而言。
那么怎样才能降低OLTP应用系统硬解析的数量呢？-----绑定变量。绑定变量的实质就是用一种特殊类型的变量后者称之为占位符替代SQL语句中的动态部分，从而保证每次提交的语句都一样。Oracle数据库中绑定变量的语法规则为：":variable_name"。下面通过具体实验来说明绑定变量的具体用法。
1.简单SELECT语句使用绑定变量
执行不含任何绑定变量的SQL语句如下：
点击(此处)折叠或打开
-
select /*no_bind*/ * from emp where empno=7902;
- ...省略输出内容
-
-
//查看执行计划
- SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));
-
PLAN_TABLE_OUTPUT
-
----------------------------------------------------------------------------------------------------
-
SQL_ID 8qng5nrymtdc1, child number 0
-
-------------------------------------
- select /*no_bind*/ * from emp where empno=7902
- Plan hash value: 2949544139
-
--------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | | | 1 (100)| |
-
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
-
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
-
--------------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
- ---------------------------------------------------
-
2 - access("EMPNO"=7902)
-
select /*no_bind*/ * from emp where empno=7876;
-
- ...省略输出内容
-
- //查看执行计划
- select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
-
PLAN_TABLE_OUTPUT
-
----------------------------------------------------------------------------------------------------
-
SQL_ID 0x1jqmhdwjg7p, child number 0
-
-------------------------------------
- select /*no_bind*/ * from emp where empno=7876
- Plan hash value: 2949544139
-
--------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | | | 1 (100)| |
-
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
-
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
- --------------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("EMPNO"=7876)
点击(此处)折叠或打开
-
SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%no_bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
-
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-
------------------------------------------------------------ ------------- ------------- ----------
-
select /*no_bind*/ * from emp where empno=7902 8qng5nrymtdc1 1 1
- select /*no_bind*/ * from emp where empno=7876 0x1jqmhdwjg7p 1 1
我们尝试用绑定变量重复上面的工作，看一下硬解析的次数以及效果。
点击(此处)折叠或打开
-
var x number;
-
exec :x := 7902;
-
select /*bind*/ * from emp where empno=:x;
-
...省略输出内容
-
exec :x :=7876;
-
select /*bind*/ * from emp where empno=:x;
-
...省略输出内容
-
-
SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
-
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-
------------------------------------------------------------ ------------- ------------- ----------
-
select /*bind*/ * from emp where empno=:x 5cnzfrvqdqccg 1 2
-
-
SELECT t.SQL_TEXT,t.SQL_ID,t.CHILD_NUMBER,t.EXECUTIONS FROM v$sql t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
-
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS
-
------------------------------------------------------------ ------------- ------------ ----------
- select /*bind*/ * from emp where empno=:x 5cnzfrvqdqccg 0 2
2.存储过程使用绑定变量
分别创建两个PROCEDURE，两个存储过程的作用一样都是向表插入10万条数据，唯一不同的是其中PROC1未使用变量，PROC2使用绑定变量。
点击(此处)折叠或打开
-
CREATE TABLE T (A INT);
- //未使用绑定变量
-
CREATE OR REPLACE PROCEDURE PROC1
-
AS
-
BEGIN
-
FOR i IN 1 .. 100000
-
LOOP
-
EXECUTE IMMEDIATE 'INSERT INTO T VALUES('||i|| ')' ;
-
END LOOP ;
-
END ;
- //使用绑定变量
-
CREATE OR REPLACE PROCEDURE PROC2
-
AS
-
BEGIN
-
FOR i IN 1 .. 100000
-
LOOP
-
EXECUTE IMMEDIATE 'INSERT INTO T VALUES(:X)' USING i;
-
END LOOP ;
- END ;
proc1的执行情况如下：
点击(此处)折叠或打开
- select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
-
NAME VALUE
-
---------------------------------------------------------------- ----------
-
parse count (total) 505
-
parse count (hard) 312
-
parse count (failures) 4
-
parse count (describe) 0
-
-
exec proc1;
-
Elapsed: 00:01:23.09
-
- select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
-
NAME VALUE
-
---------------------------------------------------------------- ----------
-
parse count (total) 100515
-
parse count (hard) 100313
-
parse count (failures) 4
- parse count (describe) 0
proc2的执行情况：
点击(此处)折叠或打开
- select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
-
NAME VALUE
-
---------------------------------------------------------------- ----------
-
parse count (total) 100570
-
parse count (hard) 100345
-
parse count (failures) 4
- parse count (describe) 0
-
Elapsed: 00:00:00.01
-
- SQL> exec proc2;
- PL/SQL procedure successfully completed.
-
Elapsed: 00:00:11.02
-
- select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
-
NAME VALUE
-
---------------------------------------------------------------- ----------
-
parse count (total) 100573
-
parse count (hard) 100347
-
parse count (failures) 4
- parse count (describe) 0
看到PROC1的执行时间几乎是PROC2执行时间的8倍。
3.JAVA语言使用绑定变量的情况
java PrepareStatement对象，可以将sql语句做预编译操作，被封装的sql语句可以包含动态参数，减少编译的次数，提高数据库性能.PrepareStatement的具体使用方法如下：
点击(此处)折叠或打开
-
for (int i =1 ;i<=1000;i++ ) {
-
v_sql ="select object_name from objects where object_id= :x ";
-
stmt=conn.prepareStatement(v_sql);
-
stmt.setString(1,Integer.toString(i));
-
rset = stmt.executeQuery();
-
stmt.close();
- }
【总结】
TOM曾说过：Oracle将已解析、已编译的SQL连同其他内容存储在共享池(shared pool)中，这个是系统全局区(System Golbal Area,SGA)中一个非常重要的共享内存结构。如果你确实想让Oracle缓慢运行，甚至几近停顿，只要根本不使用绑定变量就可以办到，足以见绑定变量的重要性。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29827284/viewspace-2138106/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
