[20120726]建立約束和使用繫結變數.txt

[20120726]建立約束和使用繫結變數.txt

昨天檢查awr報表檔案，發現：
select condition from cdef$ where rowid=:1
這條語句執行次數很高，因為查詢的where條件使用rowid=:1,應該不會是使用者的程式執行，而是某種遞迴的呼叫。

直接在google輸入select condition from cdef$ where rowid=:1,發現如下連結：


原來我當時偷懶，直接使用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語句的地方，因為這些語句重複執行的可能性很小！