Oracle 学习之性能优化（三）绑定变量

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

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------------------------------KINGSQL>  exec :empno := 7782;PL/SQL procedure successfully completed.SQL> select ename from emp where empno = :empno;ENAME------------------------------CLARKSQL>

我们查看一下游标

SQL> COL SQL_TEXT FOR A30SQL> COL SQL_ID FOR A20SQL> SET LINESIZE 200SQL> 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 = :empnoSQL> 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 = :empnoSQL>

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

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

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

1. 建立一张表

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

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

SQL> set timing onSQL> declarebegin  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 onSQL> 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             1t bind variable')insert into t values(8826,'tes 7yuz09vq9h0c4                     1         1             1t bind variable')insert into t values(9905,'tes 97c7m0gxj80cv                     1         1             1t bind variable')insert into t values(9396,'tes 9bvtw8y7080g5                     1         1             1t bind variable')SQL_TEXT                       SQL_ID                    EXECUTIONS          LOADS VERSION_COUNT------------------------------ -------------------- ---------- ---------- -------------insert into t values(9034,'tes ck51y8bu1c0jr                     1         1             1t bind variable')insert into t values(9153,'tes 7cxb26zpcn0q9                     1         1             1t bind variable')insert into t values(9783,'tes 7236x7yva40sq                     1         1             1t bind variable')insert into t values(9491,'tes cn2n05f70810f                     1         1             1

3. 使用绑定变量

SQL> set timing onSQL> declarebegin  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             1bind variable')

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

二、修改初始化参数

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

SQL> show parameter cursor_sharingNAME                                 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 10000SQL> set linesize 200SQL> col SQL_TEXT for a50SQL> col SQL_ID for 520SQL> col SQL_ID for a20SQL> 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 selectedSQL>

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

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

和cursor_sharing=FORCE时，情况一样。

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