违反约束导致的系统开销

如果我们插入的记录违反了约束，那么ORACLE会将约束的名称抛给我们，此种错误会有一定的系统开销，因为在后台ORACLE进行递归SQL调用进行约束名的查询。

 

看如下一个间的例子：

> CREATE TABLE TEST
  2  AS
  3  SELECT * FROM ALL_OBJECTS;

Table created.

> ALTER TABLE TEST ADD PRIMARY KEY(OBJECT_ID);

Table altered.

首先创建了一个带有主键的表。

> set timing on

> alter session set sql_trace=true;

Session altered.

Elapsed: 00:00:00.00
> declare
  2    cursor mycursor is select * from test;
  3    test_rec test%rowtype;
  4  begin
  5     open mycursor;
  6     loop
  7       fetch mycursor into test_rec;
  8       exit when mycursor%notfound;
  9       begin
 10          insert into test values test_rec;
 11       exception
 12          when dup_val_on_index then
 13            null;
 14       end;
 15     end loop;
 16     commit;
 17     close mycursor;
 18  end;
 19  /

PL/SQL procedure successfully completed.

Elapsed: 00:07:56.83

> alter session set sql_trace=false;

Session altered.

Elapsed: 00:00:00.00
> select count(1) from test;

  COUNT(1)
----------
    391820

Elapsed: 00:00:00.06

391820条记录插入的时间大约为8分钟，当然由于主键重复导致这391820条记录全部拒绝掉。

[oracle@dbserver udump]$ tkprof dbtest_ora_390012.trc test.txt

TKPROF: Release 10.2.0.4.0 - Production on Tue Jan 10 12:24:11 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

在后台跟踪文件中可以找到如下的SQL语句：

select /*+ rule */ c.name, u.name
from
 con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and
  c.owner# = u.user#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   391820      0.16       5.27          0          0          0           0
Execute 391820      0.18       9.57          0          0          0           0
Fetch   391820      0.69      18.18          0    3526380          0      391820
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1175460      1.03      33.04          0    3526380          0      391820

Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS  (cr=9 pr=0 pw=0 time=131 us)
      1   NESTED LOOPS  (cr=6 pr=0 pw=0 time=96 us)
      1    TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=51 us)
      1     INDEX RANGE SCAN I_CDEF4 (cr=2 pr=0 pw=0 time=36 us)(object id 53)
      1    TABLE ACCESS BY INDEX ROWID CON$ (cr=3 pr=0 pw=0 time=42 us)
      1     INDEX UNIQUE SCAN I_CON2 (cr=2 pr=0 pw=0 time=28 us)(object id 49)
      1   TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=32 us)
      1    INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=8 us)(object id 11)

********************************************************************************

这个SQL就是INSERT INTO TEST出现错误导致的递归SQL语句，主要用来检索引起冲突的唯一约束的主键名（即下面的yansp.SYS_C002103602）。
譬如：

> insert into test select * from test where rownum=1;
insert into test select * from test where rownum=1
*
ERROR at line 1:
ORA-00001: unique constraint (yansp.SYS_C002103602) violated

Elapsed: 00:00:00.01

本例中因为检索违反约束的约束名导致了33.04秒的系统开销。

 

 

如果大部分语句都是违法约束的，因此上面的SQL可以修改为：

SQL> declare
  2    cursor mycursor is select * from test;
  3    test_rec test%rowtype;
  4    v_objid test.object_id%type;
  5  begin
  6     open mycursor;
  7     loop
  8       fetch mycursor into test_rec;
  9       exit when mycursor%notfound;
 10       begin
 11          select object_id into v_objid
 12          from test where object_id=test_rec.object_id;
 13       exception
 14          when no_data_found then
 15            insert into test values test_rec;
 16       end;
 17     end loop;
 18     commit;
 19     close mycursor;
 20  end;
 21  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.01
SQL>

因此程序中避免违反约束的产生也是提高性能的一种方法。

 

譬如：

for x in ( set of records )
   loop
        begin
              insert into table
        exception
              when dup_val_on_index then update ...
        end
   end loop;

可以修改为:

   for x in ( set of records )
   loop
       update ...
       if ( sql%rowcount = 0 )
       then
            insert ....
       end if;
   end loop

或者：

   insert into t
   select :pk, :bv1, :bv2, :bv3 from dual
    where not exists (select 1 from T where pk = :pk );
 
   if ( sql%rowcount = 0 )
   then
      duplication record logic here or just raise dup_val_on_index
   end if;