This discussion is archived
14 Replies Latest reply: Jun 24, 2009 12:02 AM by 704664 RSS

Row Trigger does not scale

704664 Newbie
Currently Being Moderated
Hello!

I've been developing DB designs for a couple of years, but now crushed on a scalability problem in Oracle (11g).

Below is a reproducing scenario,
In short:
A before-dml-row-trigger does need more time, if the table gets populated (at INSERT).
Example:
If the table is empty, a row needs 0.7 ms for insert. If the table has 100k Rows, this exact same insert needs about 5 ms.
It seems, that the trigger does something like a full table scan befor the pl/sql block begins.
General, this behaviour is not explainable to me.
Does anyone have a similar experience?
Can anyone try it on a 10g version?
Can anyone explain this behaviour?

Now the reproducing scenario.
Small explanation: fill_part inserts 10k rows into t2, and outputs the mean time of the inserts.
fill_all calls fill_part 10 times (after truncating t2), so the outputed times are for inserting 0..10k, 10k..20k, 20k..30k ... and so on.
The first try is without trigger (= disabled trigger), very nice constant values.
The second try with trigger do_nothing. The values starts higher, which is ok, because a trigger needs time. But the values are increasing!!!...
-----
SQL> create table t as
select owner, object_name, object_id, data_object_id, object_type, created
from all_objects
where rownum<=10000;

Table created.

SQL> select count(1) from t;

COUNT(1)
--
10000

SQL> create table t2 as select * from t where rownum=0;

Table created.

SQL> set serverout on
SQL> CREATE OR REPLACE PROCEDURE fill_part
IS
CURSOR ref_cur IS SELECT * FROM t;
time_start DATE;
time_end DATE;
diff NUMBER;
BEGIN
time_start := sysdate;
FOR ref IN ref_cur
LOOP
INSERT INTO t2 VALUES (ref.owner, ref.object_name, ref.object_id, ref.data_object_id, ref.object_type, ref.created);
COMMIT;
END LOOP;
time_end := sysdate;
diff := round((time_end-time_start)*24*60*60);
dbms_output.put_line('Needed ' || diff || ' seconds.');
END fill_part;
/

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE fill_all
IS
BEGIN
dbms_output.put_line('Truncating..');
execute immediate 'truncate table t2';
dbms_output.put_line('Start');
FOR i IN 1..10
LOOP
fill_part;
END LOOP;
END fill_all;
/

Procedure created.

SQL> exec fill_all;
Truncating..
Start
Needed 3 seconds.
Needed 4 seconds.
Needed 4 seconds.
Needed 3 seconds.
Needed 4 seconds.
Needed 3 seconds.
Needed 4 seconds.
Needed 3 seconds.
Needed 4 seconds.
Needed 4 seconds.

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE TRIGGER DO_NOTHING
BEFORE INSERT OR UPDATE OR DELETE ON t2
FOR EACH ROW
BEGIN
NULL;
END;
/

Trigger created.

SQL> exec fill_all;
Truncating..
Start
Needed 7 seconds.
Needed 11 seconds.
Needed 13 seconds.
Needed 19 seconds.
Needed 25 seconds.
Needed 29 seconds.
Needed 35 seconds.
Needed 40 seconds.
Needed 44 seconds.
Needed 49 seconds.

PL/SQL procedure successfully completed.

SQL>
  • 1. Re: Row Trigger does not scale
    brtk Journeyer
    Currently Being Moderated
    Do you use Atomatic Segment Memory Management or Free Lists?
    Do you have any index on that table?

    Bartek
  • 2. Re: Row Trigger does not scale
    704664 Newbie
    Currently Being Moderated
    Do you use Atomatic Segment Memory Management or Free Lists?
    Atomatic Memory Management is enabled.
    How does that have an impact in this case?

    Do you have any index on that table?
    As you see above: no.
    In our original application, there are indexes on the table, but that does not effect this behaviour.
  • 3. Re: Row Trigger does not scale
    brtk Journeyer
    Currently Being Moderated
    Sorry it is my fault. I meant Automatic Segment Space Management (not ASMM nor AMM).


    Bartek
  • 4. Re: Row Trigger does not scale
    704664 Newbie
    Currently Being Moderated
    Sorry it is my fault. I meant Automatic Segment Space Management (not ASMM nor AMM).
    So, its ASSM, hu? ;)
    Yes, this is enabled for this tablespace.
    Again:
    Why could this have an effect to this problem?

    Btw: oracle has its whole wait-cycles in "CPU" during this test.
  • 5. Re: Row Trigger does not scale
    P.Forstmann Guru
    Currently Being Moderated
    I cannot reproduce your problem with Oracle XE on a Windows XP PC.

    Could you please split your scenario in 2 steps and for each step:
    1. enable tracing at beginning with:
    exec dbms_monitor.session_trace_enable(waits=>true);
    2. disable tracing at the end with:
    exec dbms_monitor.session_trace_disable;
    For each trace file generated in USER_DUMP_DEST, run:
    TKPROF <input .trc> <output file> sort=prsela,fchela,exela
    and post the 2 TKPROF output file.
  • 6. Re: Row Trigger does not scale
    brtk Journeyer
    Currently Being Moderated
    Why could this have an effect to this problem?
    I had a theory for this issue, but I couldn't reproduce your scenario (10.2.0.1 linux under vmware). My times are more less stable.

    Probably the problem is very specific to your instalation and doesn't depend only on the condition you presented. Can you prepare awr/statspack report?

    Bartek
  • 7. Re: Row Trigger does not scale
    704664 Newbie
    Currently Being Moderated
    Hello!

    I have now all informations (tkprof and statspack)
    The files are more huge than small.. is there a way where the files can be uploaded in this forum?

    Btw.
    I found by accident:
    The time of the trigger depends not on the rows which are already loaded into the table, but
    the number of calls from the trigger itself:
    (Part from above scenario continued)
    ---------------
    SQL> exec fill_all;
    Truncating..
    Start
    Needed 7 seconds.
    Needed 10 seconds.
    Needed 14 seconds.
    Needed 19 seconds.
    Needed 25 seconds.
    Needed 30 seconds.
    Needed 35 seconds.
    Needed 41 seconds.
    Needed 45 seconds.
    Needed 50 seconds.

    PL/SQL procedure successfully completed.

    SQL> exec fill_all;
    Truncating..
    Start
    Needed 55 seconds.
    Needed 60 seconds.
    Needed 64 seconds.
    Needed 68 seconds.
    Needed 72 seconds.
    Needed 77 seconds.
    Needed 81 seconds.
    Needed 85 seconds.
    Needed 90 seconds.
    Needed 95 seconds.

    PL/SQL procedure successfully completed.

    SQL> select count(1) from t2;

    COUNT(1)
    --
    100000

    SQL> truncate table t2;

    Table truncated.

    SQL> exec fill_all;
    Truncating..
    Start
    Needed 99 seconds.
    Needed 103 seconds.
    Needed 109 seconds.
    Needed 113 seconds.
    Needed 118 seconds.
    Needed 126 seconds.
    Needed 131 seconds.
    Needed 140 seconds.
    Needed 139 seconds.
    Needed 145 seconds.

    PL/SQL procedure successfully completed.

    SQL> select count(1) from t2;

    COUNT(1)
    --
    100000

    SQtruncate table t2;

    Table truncated.

    SQL> alter trigger do_nothing disable;

    Trigger altered.

    SQL> exec fill_part;
    Needed 5 seconds.

    PL/SQL procedure successfully completed.

    SQL> alter trigger do_nothing enable;

    Trigger altered.

    SQL> truncate table t2;

    Table truncated.

    SQL> exec fill_part;
    Needed 148 seconds.

    PL/SQL procedure successfully completed.

    SQL> drop trigger do_nothing;

    Trigger dropped.

    SQL> truncate table t2;

    Table truncated.

    SQL> CREATE OR REPLACE TRIGGER DO_NOTHING
    BEFORE INSERT OR UPDATE OR DELETE ON t2
    FOR EACH ROW
    BEGIN
    NULL;
    END;
    /

    Trigger created.

    SQL> exec fill_part;
    Needed 8 seconds.

    PL/SQL procedure successfully completed.

    SQL>
  • 8. Re: Row Trigger does not scale
    brtk Journeyer
    Currently Being Moderated
    Anonymous file server: rapidshare.com
  • 9. Re: Row Trigger does not scale
    704664 Newbie
    Currently Being Moderated
    Ok, I have a server for my own ;)

    Statspack of the two scenarios:
    http://www.ulite.de/sp_5_6.lst
    http://www.ulite.de/sp_7_8.lst

    tkprofs of the two scenarios:
    http://www.ulite.de/step1.tkprof
    http://www.ulite.de/step2.tkprof

    I could not read more out of this reports as: the trigger needs CPU time...
  • 10. Re: Row Trigger does not scale
    brtk Journeyer
    Currently Being Moderated
    I looked at the "SQL ordered by CPU" statspack report. In 5_6 there are 67,573 executions of insert into t2. In 7_8 there are 42,567.
    Did both experiments was really the same?

    Bartek
  • 11. Re: Row Trigger does not scale
    680087 Pro
    Currently Being Moderated
    Most likely it's [Bug #6400175|https://metalink2.oracle.com/metalink/plsql/f?p=130:14:7267517791581461885::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,6400175.8,1,0,1,helvetica].

    Performance degradation is very significant, and is proportional to the number of times DML is performed. Thanks for the case.
  • 12. Re: Row Trigger does not scale
    Solomon Yakobson Guru
    Currently Being Moderated
    I can't reproduce your results:
    SQL> select * from v$version
      2  /
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    SQL> create table t as
      2  select owner, object_name, object_id, data_object_id, object_type, created
      3  from all_objects
      4  where rownum<=10000
      5  /
    
    Table created.
    
    SQL> create table t2 as select * from t where rownum=0
      2  /
    
    Table created.
    
    SQL> CREATE OR REPLACE PROCEDURE fill_part
      2  IS
      3  CURSOR ref_cur IS SELECT * FROM t;
      4  time_start DATE;
      5  time_end DATE;
      6  diff NUMBER;
      7  BEGIN
      8  time_start := sysdate;
      9  FOR ref IN ref_cur
     10  LOOP
     11  INSERT INTO t2 VALUES (ref.owner, ref.object_name, ref.object_id, ref.data_object_id, ref.object_type, ref.created);
     12  COMMIT;
     13  END LOOP;
     14  time_end := sysdate;
     15  diff := round((time_end-time_start)*24*60*60);
     16  dbms_output.put_line('Needed ' || diff || ' seconds.');
     17  END fill_part;
     18  /
    
    Procedure created.
    
    SQL> CREATE OR REPLACE PROCEDURE fill_all
      2  IS
      3  BEGIN
      4  dbms_output.put_line('Truncating..');
      5  execute immediate 'truncate table t2';
      6  dbms_output.put_line('Start');
      7  FOR i IN 1..10
      8  LOOP
      9  fill_part;
     10  END LOOP;
     11  END fill_all;
     12  /
    
    Procedure created.
    
    SQL> exec fill_all;
    Truncating..
    Start
    Needed 2 seconds.
    Needed 2 seconds.
    Needed 2 seconds.
    Needed 2 seconds.
    Needed 3 seconds.
    Needed 2 seconds.
    Needed 2 seconds.
    Needed 2 seconds.
    Needed 3 seconds.
    Needed 2 seconds.
    
    PL/SQL procedure successfully completed.
    
    SQL> CREATE OR REPLACE TRIGGER DO_NOTHING
      2  BEFORE INSERT OR UPDATE OR DELETE ON t2
      3  FOR EACH ROW
      4  BEGIN
      5  NULL;
      6  END;
      7  /
    
    Trigger created.
    
    SQL> exec fill_all;
    Truncating..
    Start
    Needed 2 seconds.
    Needed 3 seconds.
    Needed 3 seconds.
    Needed 2 seconds.
    Needed 3 seconds.
    Needed 2 seconds.
    Needed 3 seconds.
    Needed 2 seconds.
    Needed 3 seconds.
    Needed 3 seconds.
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SY.
  • 13. Re: Row Trigger does not scale
    brtk Journeyer
    Currently Being Moderated
    Hi,

    It seems that Timur is right.
    I reproduced your case in 11.1.0.6.
    The delay is proportional more than lineary (expotential?) to session_cached_cursors parameter.
    The workaround is to set it zero.

    Bartek
  • 14. Re: Row Trigger does not scale
    704664 Newbie
    Currently Being Moderated
    Hello!

    I've proofed: its this bug.
    I cannot reproduce the behaviour with 11.1.0.7.0

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points