SQL & PL/SQL

Hello,

I have strange problem - when on table exists before update trigger(in its body is nothing important, maybe problem is on other triggers aswell), then repeating update on this table is slower and slower on 11g, on 10g it is ok. Problem is on windows and linux platform aswell.

I created some scripts to simulate this behavior, in table is 60 000 row. In one loop I update all 60 000 rows from table and measure time.

Can someone tell me where problem is and if it is possible to manage it with some settings or anyway ? In our application it is big problem, similar operation takes days ......

thanks tomas

times for 10g:
Trigger disabled
Loop 1 - 6,13 secs
Loop 2 - 2,92 secs
Loop 3 - 3,19 secs
Loop 4 - 2,95 secs
Loop 5 - 3,39 secs
Trigger enabled
Loop 1 - 4,83 secs
Loop 2 - 3,78 secs
Loop 3 - 4,75 secs
Loop 4 - 3,91 secs
Loop 5 - 3,81 secs

times for 11g:
Trigger disabled
Loop 1 - 2,27 secs
Loop 2 - 2,4 secs
Loop 3 - 2,3 secs
Loop 4 - 2,42 secs
Loop 5 - 2,36 secs
Trigger enabled
Loop 1 - 8,01 secs
Loop 2 - 17,22 secs
Loop 3 - 29,21 secs
Loop 4 - 58,43 secs
Loop 5 - 115,59 secs

script for create test table

CREATE TABLE SD3Test 
   (MPID INTEGER NOT NULL ENABLE, 
 ROWCOUNT NUMBER(10,0), 
 CLOSEDROWCOUNT NUMBER(10,0), 
 AMOUNT NUMBER(18,2) DEFAULT 0 NOT NULL ENABLE, 
 LOCALAMOUNT NUMBER(18,2) DEFAULT 0 NOT NULL ENABLE, 
 CLOSED CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE);
 
ALTER TABLE SD3Test ADD CONSTRAINT SD3TestPK PRIMARY KEY (MPID);

--fill test data
DECLARE mCnt INTEGER;
BEGIN
 mCnt := 0;
 FOR mCnt IN 1..60000 loop
  INSERT INTO SD3Test (MPID, Amount, LocalAmount, RowCount, ClosedRowCount)
  VALUES(mCnt, 0, 0, 0, 0);
 END loop;
END;
.
/

--create trigger
CREATE OR REPLACE TRIGGER SD3TestAU
  BEFORE UPDATE ON SD3Test FOR EACH ROW
DECLARE
 mInt INTEGER;
BEGIN
  /*original code of our trigger, but performance problem is 
 not depend on it, just depend on existence of this trigger
 IF ((:new.RowCount=:new.ClosedRowCount) and (:new.RowCount>0)) THEN
  :new.Closed:='A';
 ELSE 
  :new.Closed:='N';
 end if;
 */
 mInt := 1;
END;
.
/

script for run test

 
--test for updates with trigger disabled and enabled
set serveroutput on;
DECLARE 
  mCnt INTEGER;
  mLoop INTEGER;
  mDisEna INTEGER;
  mStart NUMBER;
  mStop NUMBER;
BEGIN
 --2 loops - first with disabled trigger, second with enabled trigger
 FOR mDisEna IN 1..2 loop
   -- 
  IF mDisEna = 1 THEN
   execute immediate 'ALTER TRIGGER SD3TestAU disable';
   dbms_output.put_line('Trigger disabled');
  ELSE 
   execute immediate 'ALTER TRIGGER SD3TestAU enable';
   dbms_output.put_line('Trigger enabled');
  END IF;
  -- 3 inner loops for measure time
  FOR mLoop IN 1..3 loop
    --update on all records in table
   mStart := dbms_utility.get_time;
   FOR mCnt IN 1..60000 loop 
    UPDATE SD3Test
     SET
    ROWCOUNT = ROWCOUNT+1,
    CLOSEDROWCOUNT = CLOSEDROWCOUNT+1,
    AMOUNT = AMOUNT + mCnt,
    LOCALAMOUNT = LOCALAMOUNT + mCnt
    WHERE
     MPID = mCnt; 
   END loop;
   mStop := dbms_utility.get_time;
   dbms_output.put_line('Loop ' || mLoop || ' - ' || round((mStop - mStart)/100,2) || ' secs');
  END loop;
 END loop;
END;
.
/