Oracle学习之性能优化(三)绑定变量

  根据Oracle 学习之性能优化(二)游标中的描述,我们知道如下两条语句是不共享的。

创新互联建站主要从事成都网站建设、成都网站制作、网页设计、企业做网站、公司建网站等业务。立足成都服务广元,十年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:13518219792

select * from emp where empno=7698;
select * from emp where empno=7566;

  这样就造成每次执行用户的查询都要进行硬解析,但是我们知道,其他这两个语句的执行计划应该是相同。那么有什么方法能避免不必要的硬解析吗?这里我们提供2种方法。

一、绑定变量

SQL> variable empno number;
SQL> exec :empno := 7839;

PL/SQL procedure successfully completed.

SQL> select ename from emp where empno = :empno;

ENAME
------------------------------
KING

SQL>  exec :empno := 7782;

PL/SQL procedure successfully completed.

SQL> select ename from emp where empno = :empno;

ENAME
------------------------------
CLARK

SQL>

我们查看一下游标

SQL> COL SQL_TEXT FOR A30
SQL> COL SQL_ID FOR A20
SQL> SET LINESIZE 200
SQL> SELECT sql_id,sql_text,executions,loads,version_count
  FROM v$sqlarea
 WHERE sql_text LIKE '%:empno'; 

SQL_ID		     SQL_TEXT			    EXECUTIONS	    LOADS VERSION_COUNT
-------------------- ------------------------------ ---------- ---------- -------------
f6r0kqk0hsa7s	     select ename from emp where em	     2		1	      1
		     pno = :empno

SQL> SELECT sql_id,sql_text,loads,child_number,parse_calls
  FROM v$sql
 WHERE sql_text LIKE '%:empno';

SQL_ID		     SQL_TEXT				 LOADS CHILD_NUMBER PARSE_CALLS
-------------------- ------------------------------ ---------- ------------ -----------
f6r0kqk0hsa7s	     select ename from emp where em	     1		  0	      2
		     pno = :empno
SQL>

可见,父子游标都被共享啦。

在OLTP环境中,一定要使用绑定变量以避免系统有太多的硬解析。

我们验证一下,不使用绑定变量和使用了绑定变量后,性能到底有没有提升。

1. 建立一张表

SQL> create table t(id int,text varchar2(100));

Table created.

2. 不使用绑定变量向表中插入10000行记录

SQL> set timing on
SQL> declare
begin
  for i in 1 .. 10000 loop
     execute immediate 'insert into t values('||i||',''test bind variable'')';
  end loop;
  commit;
end;
/ 

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.43

系统产生了非常多的游标

SQL> set pause on
SQL> SELECT sql_text,
       sql_id,
       EXECUTIONS,
       LOADS,
       VERSION_COUNT
  FROM v$sqlarea
 WHERE sql_text LIKE 'insert into t%';   


SQL_TEXT		       SQL_ID		    EXECUTIONS	    LOADS VERSION_COUNT
------------------------------ -------------------- ---------- ---------- -------------
insert into t values(9156,'tes ah7vdgtnj80b1		     1		1	      1
t bind variable')

insert into t values(8826,'tes 7yuz09vq9h0c4		     1		1	      1
t bind variable')

insert into t values(9905,'tes 97c7m0gxj80cv		     1		1	      1
t bind variable')

insert into t values(9396,'tes 9bvtw8y7080g5		     1		1	      1
t bind variable')


SQL_TEXT		       SQL_ID		    EXECUTIONS	    LOADS VERSION_COUNT
------------------------------ -------------------- ---------- ---------- -------------

insert into t values(9034,'tes ck51y8bu1c0jr		     1		1	      1
t bind variable')

insert into t values(9153,'tes 7cxb26zpcn0q9		     1		1	      1
t bind variable')

insert into t values(9783,'tes 7236x7yva40sq		     1		1	      1
t bind variable')

insert into t values(9491,'tes cn2n05f70810f		     1		1	      1

3. 使用绑定变量

SQL> set timing on
SQL> declare
begin
  for i in 1 .. 10000 loop
     execute immediate 'insert into t values(:x,''test bind variable'')' using i;
  end loop;
  commit;
end;
/ 

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43

使用绑定变量,执行速度快了很多倍。

SQL> SELECT sql_text,
       sql_id,
       EXECUTIONS,
       LOADS,
       VERSION_COUNT
  FROM v$sqlarea
 WHERE sql_text LIKE 'insert into t%:x%'; 


SQL_TEXT		       SQL_ID		    EXECUTIONS	    LOADS VERSION_COUNT
------------------------------ -------------------- ---------- ---------- -------------
insert into t values(:x,'test  0nhbks92x50kk		 10000		1	      1
bind variable')

执行计划只有一个,被执行了10000次。

二、修改初始化参数

系统提供了一个初始化参数

SQL> show parameter cursor_sharing
NAME				     TYPE			       VALUE
------------------------            -------------------               ------------------
cursor_sharing			     string			       EXACT

CURSOR_SHARING determines what kind of SQL statements can share the same cursors. 

Values: 

  • FORCE
    Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal. 

  • SIMILAR
    Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. 

  • EXACT
    Only allows statements with identical text to share the same cursor

1. 清空shared_pool

SQL> set pagesize 10000
SQL> set linesize 200
SQL> col SQL_TEXT for a50
SQL> col SQL_ID for 520
SQL> col SQL_ID for a20
SQL> alter system flush shared_pool;

System altered.

SQL> SELECT sql_text,
       sql_id,
       EXECUTIONS,
       LOADS,
       VERSION_COUNT
  FROM v$sqlarea
 WHERE sql_text LIKE 'insert into t%';   2    3    4    5    6    7  

no rows selected

SQL>

2. 将cursor_sharing改为FORCE,执行查询

SQL>  alter session set cursor_sharing=force;

Session altered.

Elapsed: 00:00:00.02
SQL> declare
begin
  for i in 1 .. 10000 loop
     execute immediate 'insert into t values('||i||',''test bind variable'')';
  end loop;
  commit;
end;
/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15

3. 查看游标情况

SQL> SELECT sql_text,
       sql_id,
       EXECUTIONS,
       LOADS,
       VERSION_COUNT
  FROM v$sqlarea
 WHERE sql_text LIKE 'insert into t%';

no rows selected

SQL> /

SQL_TEXT					   SQL_ID		EXECUTIONS	LOADS VERSION_COUNT
-------------------------------------------------- -------------------- ---------- ---------- -------------
insert into t values(9966,'test bind variable')    07xgdm0uwn5gb		 1	    3		  1
insert into t values(10000,'test bind variable')   76rf2hx2w45m1		 1	    3		  1
insert into t values(9969,'test bind variable')    bfayz4q1j4b37		 1	    3		  1
insert into t values(9984,'test bind variable')    48t1dy0tahgxh		 1	    3		  1
insert into t values(9998,'test bind variable')    04bhmfjq8hhtu		 1	    3		  1
insert into t values(9967,'test bind variable')    0njsupf834kn0		 1	    3		  1
insert into t values(9999,'test bind variable')    6uhdudx8k4rv3		 1	    3		  1
insert into t values(9975,'test bind variable')    0tqj0jcamsspu		 1	    3		  1
insert into t values(9957,'test bind variable')    a6as35h3wwu00		 1	    3		  1
insert into t values(9970,'test bind variable')    58m581pqq8v4j		 1	    3		  1
insert into t values(9982,'test bind variable')    3gh2q9f2wnxr8		 1	    3		  1
insert into t values(9977,'test bind variable')    1t0t0jz9y0zg9		 1	    3		  1
insert into t values(9988,'test bind variable')    111vappsrszy7		 1	    3		  1
insert into t values(9994,'test bind variable')    8kvjy7tns10vq		 1	    3		  1
insert into t values(9963,'test bind variable')    c1w951tadx4tb		 1	    3		  1
insert into t values(9993,'test bind variable')    10h2mbxvtt9tm		 1	    3		  1
insert into t values(9981,'test bind variable')    gv77ng7kndhty		 1	    3		  1
insert into t values(9978,'test bind variable')    0v7773365tj70		 1	    3		  1
insert into t values(9974,'test bind variable')    astu71gzn1uw5		 1	    3		  1
insert into t values(9960,'test bind variable')    09d0bxcsndxzm		 1	    3		  1
insert into t values(9959,'test bind variable')    7ht6qzyy0jz5w		 1	    3		  1
insert into t values(9965,'test bind variable')    dgz4fbhzgpzh5		 1	    3		  1
insert into t values(9989,'test bind variable')    3srf852y19zx6		 1	    3		  1
insert into t values(9995,'test bind variable')    1r6tp423v613x		 1	    3		  1
insert into t values(9976,'test bind variable')    9vxdayk3yq1nn		 1	    3		  1
insert into t values(9958,'test bind variable')    9ptg2jd30k6d8		 1	    3		  1
insert into t values(9968,'test bind variable')    akt2u5gn1y9kp		 1	    3		  1
insert into t values(×××,'test bind variable')    ch5rx2b3ja9x8		 1	    3		  1
insert into t values(9962,'test bind variable')    a2p68fsk6abwz		 1	    3		  1
insert into t values(9997,'test bind variable')    f0474tah8ubzq		 1	    3		  1
insert into t values(9972,'test bind variable')    gzqpvbrsn6ggk		 1	    3		  1
insert into t values(9983,'test bind variable')    ah9r6ghzsugmp		 1	    3		  1
insert into t values(9979,'test bind variable')    2cvqu9h5wagva		 1	    3		  1
insert into t values(9996,'test bind variable')    3h90mc46sqmzr		 1	    3		  1
insert into t values(9961,'test bind variable')    7t8njvfx8fn4y		 1	    3		  1
insert into t values(9987,'test bind variable')    1qxhj0g7cuw8u		 1	    3		  1
insert into t values(9991,'test bind variable')    5n2jahrk5z258		 1	    3		  1
insert into t values(:"SYS_B_0",:"SYS_B_1")	   950r47takm3c4	      9953	    1		  1
insert into t values(9971,'test bind variable')    fyb5pvjuqz4d0		 1	    3		  1
insert into t values(9955,'test bind variable')    1adu3pctt76bp		 1	    3		  1
insert into t values(9990,'test bind variable')    62pp4zqc9r767		 1	    3		  1
insert into t values(9973,'test bind variable')    adb60k3nxr9mk		 1	    3		  1
insert into t values(9985,'test bind variable')    gz4hry47rzhvt		 1	    3		  1
insert into t values(9986,'test bind variable')    b54fdtcu47v0d		 1	    3		  1
insert into t values(9980,'test bind variable')    fvwh53nh7zvhk		 1	    3		  1
insert into t values(9956,'test bind variable')    1vcjq6rm9gx72		 1	    3		  1
insert into t values(9964,'test bind variable')    a06un7tf1rxgu		 1	    3		  1
insert into t values(9954,'test bind variable')    0nb4synx6bxqv		 1	    3		  1

48 rows selected.

SQL>

4. 再次清空shared_pool

SQL> alter system flush shared_pool;

System altered.

5. 将cursor_sharing改为SIMILAR,执行查询

SQL> alter session set cursor_sharing=similar;

Session altered.

Elapsed: 00:00:00.03
SQL> declare
begin
  for i in 1 .. 10000 loop
     execute immediate 'insert into t values('||i||',''test bind variable'')';
  end loop;
  commit;
end;
/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.14

5. 查看共享游标

SQL> SELECT sql_text,
       sql_id,
       EXECUTIONS,
       LOADS,
       VERSION_COUNT
  FROM v$sqlarea
 WHERE sql_text LIKE 'insert into t%';  2    3    4    5    6    7  

SQL_TEXT					   SQL_ID		EXECUTIONS	LOADS VERSION_COUNT
-------------------------------------------------- -------------------- ---------- ---------- -------------
insert into t values(9966,'test bind variable')    07xgdm0uwn5gb		 1	    4		  1
insert into t values(10000,'test bind variable')   76rf2hx2w45m1		 1	    4		  1
insert into t values(9969,'test bind variable')    bfayz4q1j4b37		 1	    4		  1
insert into t values(9984,'test bind variable')    48t1dy0tahgxh		 1	    4		  1
insert into t values(9998,'test bind variable')    04bhmfjq8hhtu		 1	    4		  1
insert into t values(9967,'test bind variable')    0njsupf834kn0		 1	    4		  1
insert into t values(9999,'test bind variable')    6uhdudx8k4rv3		 1	    4		  1
insert into t values(9975,'test bind variable')    0tqj0jcamsspu		 1	    4		  1
insert into t values(9957,'test bind variable')    a6as35h3wwu00		 1	    4		  1
insert into t values(9970,'test bind variable')    58m581pqq8v4j		 1	    4		  1
insert into t values(9982,'test bind variable')    3gh2q9f2wnxr8		 1	    4		  1
insert into t values(9977,'test bind variable')    1t0t0jz9y0zg9		 1	    4		  1
insert into t values(9988,'test bind variable')    111vappsrszy7		 1	    4		  1
insert into t values(9994,'test bind variable')    8kvjy7tns10vq		 1	    4		  1
insert into t values(9963,'test bind variable')    c1w951tadx4tb		 1	    4		  1
insert into t values(9993,'test bind variable')    10h2mbxvtt9tm		 1	    4		  1
insert into t values(9981,'test bind variable')    gv77ng7kndhty		 1	    4		  1
insert into t values(9978,'test bind variable')    0v7773365tj70		 1	    4		  1
insert into t values(9974,'test bind variable')    astu71gzn1uw5		 1	    4		  1
insert into t values(9960,'test bind variable')    09d0bxcsndxzm		 1	    4		  1
insert into t values(9959,'test bind variable')    7ht6qzyy0jz5w		 1	    4		  1
insert into t values(9965,'test bind variable')    dgz4fbhzgpzh5		 1	    4		  1
insert into t values(9989,'test bind variable')    3srf852y19zx6		 1	    4		  1
insert into t values(9995,'test bind variable')    1r6tp423v613x		 1	    4		  1
insert into t values(9976,'test bind variable')    9vxdayk3yq1nn		 1	    4		  1
insert into t values(9958,'test bind variable')    9ptg2jd30k6d8		 1	    4		  1
insert into t values(9968,'test bind variable')    akt2u5gn1y9kp		 1	    4		  1
insert into t values(×××,'test bind variable')    ch5rx2b3ja9x8		 1	    4		  1
insert into t values(9962,'test bind variable')    a2p68fsk6abwz		 1	    4		  1
insert into t values(9997,'test bind variable')    f0474tah8ubzq		 1	    4		  1
insert into t values(9972,'test bind variable')    gzqpvbrsn6ggk		 1	    4		  1
insert into t values(9983,'test bind variable')    ah9r6ghzsugmp		 1	    4		  1
insert into t values(9979,'test bind variable')    2cvqu9h5wagva		 1	    4		  1
insert into t values(9996,'test bind variable')    3h90mc46sqmzr		 1	    4		  1
insert into t values(9961,'test bind variable')    7t8njvfx8fn4y		 1	    4		  1
insert into t values(9987,'test bind variable')    1qxhj0g7cuw8u		 1	    4		  1
insert into t values(9991,'test bind variable')    5n2jahrk5z258		 1	    4		  1
insert into t values(:"SYS_B_0",:"SYS_B_1")	   950r47takm3c4	      9953	    1		  1
insert into t values(9971,'test bind variable')    fyb5pvjuqz4d0		 1	    4		  1
insert into t values(9955,'test bind variable')    1adu3pctt76bp		 1	    4		  1
insert into t values(9990,'test bind variable')    62pp4zqc9r767		 1	    4		  1
insert into t values(9973,'test bind variable')    adb60k3nxr9mk		 1	    4		  1
insert into t values(9985,'test bind variable')    gz4hry47rzhvt		 1	    4		  1
insert into t values(9986,'test bind variable')    b54fdtcu47v0d		 1	    4		  1
insert into t values(9980,'test bind variable')    fvwh53nh7zvhk		 1	    4		  1
insert into t values(9956,'test bind variable')    1vcjq6rm9gx72		 1	    4		  1
insert into t values(9964,'test bind variable')    a06un7tf1rxgu		 1	    4		  1
insert into t values(9954,'test bind variable')    0nb4synx6bxqv		 1	    4		  1

48 rows selected.

和cursor_sharing=FORCE时,情况一样。

这两种方法都不推荐使用,有bug 。建议规范前台业务查询,尽量使用绑定变量。


本文题目:Oracle学习之性能优化(三)绑定变量
本文地址:http://myzitong.com/article/igceds.html