[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语句的地方，因为这些语句重复执行的可能性很小！