[20120726]建立约束和使用绑定变量.txt

昨天检查awr报表文件，发现：
select condition from cdef$ where rowid=:1
这条语句执行次数很高，因为查询的where条件使用rowid=:1,应该不会是用户的程序执行，而是某种递归的调用。

直接在google输入select condition from cdef$ where rowid=:1,发现如下链接：
http://jonathanlewis.wordpress.com/2006/12/14/constraints-inserts-and-bind/

原来我当时偷懒，直接使用toad导出insert语句导入别的数据库，这些insert语句没有使用绑定变量，而且正好这个表有个出生日期。我做了限制,限制出生日期必须大于'1900/1/1'，还有其他2个约束，一共3个约束。

按照链接的介绍当没有绑定变量插入时，insert时每次都需要读取sys.cdef$的信息：

    Oracle doesn't keep long columns cached in the dictionary cache (rowcache) so every time it optimises a new statement
that uses a check constraint (and the condition column is a long column) it has to re-read the constraint definition from
the database – just as it does with the view definition when you optimise a statement that uses a view.
--我的测试对定义not null字段不进行再次检查。

自己在测试机器如下：

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t ( id number check (id > 0), name varchar2(10) not null); 

SQL> column condition format a30

SQL> select obj#,condition from sys.cdef$ where obj# in (select object_id from dba_objects where wner=user and object_name='T');

      OBJ# CONDITION

---------- ------------------------------

    101266 "NAME" IS NOT NULL

    101266 id > 0

--可以发现存在两个约束在表T上。

2.测试:

alter system flush shared_pool;

SQL> select sql_text,executions from v$sql where sql_text like 'select condition from cdef$ where rowid=:1%';

no rows selected

begin 

 for i in 1..1000 loop 

  execute immediate 'insert into t values(' || i || ',''test'')'; 

 end loop; 

end; 

/

SQL> column sql_text format a60

SQL> select sql_text,executions from v$sql where sql_text like 'select condition from cdef$ where rowid=:1%';

SQL_TEXT                                                     EXECUTIONS

------------------------------------------------------------ ----------

select condition from cdef$ where rowid=:1                         1001

--可以发现select condition from cdef$ where rowid=:1执行了1001次。
--再次执行上面过程(注意我修改了循环的开始与结束值，如果不改，前面的insert sql语句已经在shared pool,

select condition from cdef$ where rowid=:1%的executions次数应该不存在变化)!

begin 

 for i in 1001..2000 loop 

  execute immediate 'insert into t values(' || i || ',''test'')'; 

 end loop; 

end; 

/

SQL> select sql_text,executions from v$sql where sql_text like 'select condition from cdef$ where rowid=:1%';

SQL_TEXT                                                     EXECUTIONS

------------------------------------------------------------ ----------

select condition from cdef$ where rowid=:1                         2001

--可以发现执行次数从1001=>2001.

3.可以发现如果insert的语句不使用绑定变量，在遇到有约束的字段就会执行1次select condition from cdef$ where rowid=:1%。
而对于像name varchar2(10) not null的约束仅仅访问1次。

再次修改约束条件，加入如下：

alter table t add constraint name_is_not_null check (name is not null);

SQL> select obj#,condition from sys.cdef$ where obj# in (select object_id from dba_objects where wner=user and object_name='T');

      OBJ# CONDITION

---------- ------------------------------

    101266 "NAME" IS NOT NULL

    101266 id > 0

    101266 name is not NULL

执行如下：

rollback;

begin 

 for i in 2001..3000 loop 

  execute immediate 'insert into t values(' || i || ',''test'')'; 

 end loop; 

end; 

/

SQL> select sql_text,executions from v$sql where sql_text like 'select condition from cdef$ where rowid=:1%';

SQL_TEXT                                                     EXECUTIONS

------------------------------------------------------------ ----------

select condition from cdef$ where rowid=:1                         4001

--可以发现执行次数从2001=>4001.增加了2000次。

4.改用绑定变量看看结果如何:

SQL> alter session set cursor_sharing = force ;

rollback;

begin 

 for i in 3001..4000 loop 

  execute immediate 'insert into t values(' || i || ',''test'')'; 

 end loop; 

end; 

/

SQL> select sql_text,executions from v$sql where sql_text like 'select condition from cdef$ where rowid=:1%';

SQL_TEXT                                                     EXECUTIONS

------------------------------------------------------------ ----------

select condition from cdef$ where rowid=:1                         4003

--可以发现执行次数从4001=>4003.仅仅增加2次。

总结：
又给使用绑定变量的使用找到一个好的理由，虽然遇到这种情况的不多，不过像insert语句，在表有主键的情况下，不可能一条语句执行多次！
一般在程序没有使用绑定的情况下，我的建议都是先修改有DML语句的地方，因为这些语句重复执行的可能性很小！