oracle自动绑定变量-游标共享的应用技术

上一篇 / 下一篇 2010-12-09 10:06:26 / 个人分类：ORACLE

为了提高sql执行性能，oracle提供了一个为没有使用绑定变量的SQL语句自动设置绑定变量的开关，也就是游标共享机制，这个参数是cursor_sharing，oracle默认的参数值为EXACT：

SQL> show parameter cursor_sharing;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT

cursor_sharing参数有3个值：EXACT、FORCE、SIMILAR，默认是EXACT。设置参数CURSOR_SHARING=FORCE或CURSOR_SHARING=SIMILAR，可把SQL语句的字面值转换成绑定变量，减少解析SQL的时间，下面我举个例子看一下。

打开trace文件设置，把sql trace设置为true，就会在udump目录中增加一个trc文件。

SQL> alter session set sql_trace=true;

Session altered.

SQL> show parameter sql_trace;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean TRUE
SQL>

在cursor_sharing这是为FORCE或者SIMILAR前执行下面没有使用绑定变量的sql语句，这样oracle就会把下面的sql语句以硬解析的形式缓存在共享池中。

当然这和查询结果没有任何关系，查询第几行的数据都可以，这里我们查询的是第1行的数据。

SQL> select username,user_id,ACCOUNT_STATUS from test where rownum=1;

USERNAME USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
MGMT_VIEW 53 OPEN

SQL>

打开自动绑定变量的开关，把cursor_sharing设置为FORCE或者SIMILAR，使用show查看parameter中的参数设置。

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> show parameter cursor_sharing;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE

同样执行没有使用绑定变量的sql语句。

SQL> select username,user_id,ACCOUNT_STATUS from test where rownum=1;

USERNAME USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
MGMT_VIEW 53 OPEN

SQL>

好了，下面一步可以验证我们的结果了。下面是执行两次的sql语句。

SQL> select sql_text from v$sql
2 where sql_text like 'select username,user_id,ACCOUNT_STATUS from test where rownum%';

SQL>

oracle游标共享功能会把sql语句中的常量（也有一些作者叫做直接量，不要混淆哦。）直接换成一个变量，放在共享池中，rownum=:"SYS_B_0"，SYS_B_0是游标共享机制自动加进去的。

这样看上去不错，但是oracle把where rownum=1 换成了 where rownum=:"SYS_B_0" ,这样oracle会把SYS_B_0看成是任何值，这样会对性能造成一定的影响，后者的工作量也会大大增加。前面我已经把sql_trace=true打开啦，我们可以对比一下查询计划。一看就明白啦。

BJ-朱雀(***) 11:24:00
SQL> select username,user_id,ACCOUNT_STATUS from test where rownum < 4;

USERNAME                          USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
MGMT_VIEW                              53 OPEN
SYS                                     0 OPEN
SYSTEM                                  5 OPEN

SQL> select sql_text from v$sql where sql_text like 'select username,user_id,ACCOUNT_STATUS from test where%';

SQL_TEXT
--------------------------------------------------------------------------------
select username,user_id,ACCOUNT_STATUS from test where rownum=1
select username,user_id,ACCOUNT_STATUS from test where rownum=3
select username,user_id,ACCOUNT_STATUS from test where rownum > 3
select username,user_id,ACCOUNT_STATUS from test where rownum <= 4
select username,user_id,ACCOUNT_STATUS from test where rownum=2
select username,user_id,ACCOUNT_STATUS from test where rownum <= 3
select username,user_id,ACCOUNT_STATUS from test where rownum < 4

7 rows selected.

SQL>
BJ-朱雀(***) 11:24:36
我查询出来的sql 语句中都没有一个是变量的全是常量
BJ-朱雀(***) 11:24:55
SQL> show parameter cursor_sharing;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE
BJ-朱雀(***) 11:25:15
cursor_sharin早就设置成force啦。

收藏举报

TAG: