14 Replies Latest reply: Jun 24, 2009 2:02 AM by 704664 RSS

    Row Trigger does not scale

    704664
      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
          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
            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
              Sorry it is my fault. I meant Automatic Segment Space Management (not ASMM nor AMM).


              Bartek
              • 4. Re: Row Trigger does not scale
                704664
                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
                  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
                    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
                      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
                        Anonymous file server: rapidshare.com
                        • 9. Re: Row Trigger does not scale
                          704664
                          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
                            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
                              Timur Akhmadeev
                              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
                                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
                                  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
                                    Hello!

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