FORALL and STATEMENT level trigger

Just an addendum on one of the points in my previous post.

In that post, I pointed out, amongst other things, that for a FORALL .. INSERT statement, the statement level trigger fired only once (basic evidence in previous post).

This was something which surprised me.

Just from further observations:

  • FORALL INSERT – Single statement – i.e. Statement trigger fires once.
  • FORALL UPDATE – Multiple statements – i.e. Statement trigger fires multiple times.
  • FORALL DELETE – Multiple statements – i.e. Statement trigger fires multiple times.

Which kind of backs up my original assertion in the forum thread, which was that when working with a collection and using statement level trigger work, a single INSERT/UPDATE/DELETE using TABLE (CAST ( )) should be preferable to FORALL.

The constant requirement to re-review the basics, previously known “facts”, knowledge gaps and also your assumptions is one of the things which makes working with Oracle so interesting.

SQL> SET serveroutput ON  
SQL> 
SQL> CREATE TABLE test_bulk  
  2  (test_id    NUMBER  
  3  , id_test_1  NUMBER  
  4  , id_test_2  NUMBER  
  5  ); 

Table created.

SQL> CREATE OR REPLACE TRIGGER test_asi  
  2  AFTER INSERT OR UPDATE OR DELETE
  3  ON TEST_BULK  
  4  REFERENCING NEW AS New OLD AS Old  
  5  DECLARE  
  6  begin  
  7     DBMS_OUTPUT.PUT_LINE('expensive action');  
  8  end;  
  9  /

Trigger created.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       INSERT INTO test_bulk(id_test_1,id_test_2) VALUES (1,my_collection_test(i));  
 11  END;  
 12  /  
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk; 

  COUNT(*)
----------
        14

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       UPDATE test_bulk
 11       SET    id_test_1 = 1
 12       ,      id_test_2 = my_collection_test(i)
 13      WHERE id_test_2 = my_collection_test(i);  
 14  END;  
 14  / 
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4    my_collection_test2  my_collection;  
  5  BEGIN  
  6     SELECT LEVEL BULK COLLECT  
  7     INTO   my_collection_test  
  8     FROM   dual  
  9     CONNECT BY LEVEL < 15;  
 10     FORALL i IN 1 .. my_collection_test.COUNT  
 11       DELETE test_bulk
 12       WHERE  id_test_2 = my_collection_test(i)
 13       RETURNING id_test_2 BULK COLLECT INTO my_collection_test2;  
 14  END;  
 15  /  
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL> 

Trigger stuff

My previous experience of compound triggers showed that, in 11.1.0.6 at least, they had a problem.

Regarding standard non-compound triggers, I said on the forums earlier today that I thought a FORALL statement doing an update based on a collection would fire multiple statement level triggers whereas for an INSERT…SELECT statement, the statement level triggers would fire the once.

Turns out I’m wrong, the statement level triggers fire just once from both as was demonstrated easily:

SQL> SET serveroutput ON
SQL> 
SQL> CREATE TABLE test_bulk
  2  (test_id    NUMBER
  3  , id_test_1  NUMBER
  4  , id_test_2  NUMBER
  5  );

Table created.

SQL> 
SQL> CREATE OR REPLACE TRIGGER test_asi
  2  AFTER INSERT
  3  ON TEST_BULK 
  4  REFERENCING NEW AS New OLD AS Old
  5  DECLARE
  6  begin
  7   DBMS_OUTPUT.PUT_LINE('expensive action');
  8  end;
  9  / 

Trigger created.

SQL> 
SQL> select count(*) from test_bulk;

  COUNT(*)
----------
         0

SQL> 
SQL> DECLARE
  2    TYPE my_collection IS TABLE OF NUMBER; 
  3    my_collection_test  my_collection;
  4  BEGIN
  5      SELECT LEVEL BULK COLLECT
  6      INTO   my_collection_test
  7      FROM   dual
  8      CONNECT BY LEVEL < 15;
  9      FORALL i IN 1 .. my_collection_test.COUNT
 10        INSERT INTO test_bulk(id_test_1,id_test_2) VALUES (1,my_collection_test(i)); 
 11  END;
 12  /
expensive action            <--------- one message from the statement trigger

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*) from test_bulk;

  COUNT(*)
----------
        14

SQL> 

A pretty basic thing to get wrong.

Anyway, then revisiting the general trigger and bulk documentation, I came across a statement in the documentation about compound triggers that I just didn’t get.

It is when the triggering statement affects many rows that a compound trigger has a performance benefit. This is why it is important to use the BULK COLLECT clause with the FORALL statement. For example, without the BULK COLLECT clause, a FORALL statement that contains an INSERT statement simply performs a single-row insertion operation many times, and you get no benefit from using a compound trigger.
….
If the triggering statement of a compound trigger is an INSERT statement that includes a subquery, the compound trigger retains some of its performance benefit. For example, suppose that a compound trigger is triggered by the following statement:

INSERT INTO Target
SELECT c1, c2, c3
FROM Source
WHERE Source.c1 > 0
For each row of Source whose column c1 is greater than zero, the BEFORE EACH ROW and AFTER EACH ROW sections of the compound trigger execute. However, the BEFORE STATEMENT and AFTER STATEMENT sections each execute only once (before and after the INSERT statement executes, respectively).

Ignore the first bit for now, but I can’t figure out why there would be any difference in benefit between the FORALL and the INSERT … SELECT.

I would assume (given that I’ve now changed my understanding of the statement level trigger firing for FORALL) that in both cases any statement level triggers will once and the row level triggers will fire for each row. So… how does the INSERT… SELECT only have “some of the performance benefit”? Perhaps it’s also talking about the benefits of a bulk statement versus a non-bulk statement – i.e. including the FORALL benefits.

And looking at the 11.2 documentation, this wording has changed so maybe it was just not quite right.

From the 11.2 PLSQL reference:

Performance Benefit of Compound DML Triggers
A compound DML trigger has a performance benefit when the triggering statement affects many rows.

For example, suppose that this statement triggers a compound DML trigger that has all four timing-point sections in Table 9-2:

INSERT INTO Target
SELECT c1, c2, c3
FROM Source
WHERE Source.c1 > 0
Although the BEFORE EACH ROW and AFTER EACH ROW sections of the trigger run for each row of Source whose column c1 is greater than zero, the BEFORE STATEMENT section runs only before the INSERT statement runs and the AFTER STATEMENT section runs only after the INSERT statement runs.

A compound DML trigger has a greater performance benefit when it uses bulk SQL, described in “Bulk SQL and Bulk Binding”.

OK so, no mention there of any difference in performance benefits between FORALL and INSERT…SELECT.

But then talking about the benefits purely for FORALL:

Using Compound DML Triggers with Bulk Insertion
A compound DML trigger is useful for accumulating rows destined for a second table so that you can periodically bulk-insert them. To get the performance benefit from the compound trigger, you must specify BULK COLLECT INTO in the FORALL statement (otherwise, the FORALL statement does a single-row DML operation multiple times).

So, I do wonder what exactly that is alluding to…. will have to do some investigations (but not right now… as usual?).

Anyway, it’s interesting and relevant for me currently because I’m just finishing the refactoring of a process using expensive row-by-row dml and then further expensive row-by-row trigger DML operations. Although I’m not doing this on 11g so compound triggers are an irrelevance.

It’s a shame that I can’t remove the triggers concerned altogether but there are just too many other legacy processes involved.

But the obvious work is to change the row-by-row inserts and updates to work in bulk and then use a combination of before/after statement and before or after row triggers to populate a collection on which the row-by-row trigger DML is done in bulk on the collection.

It will come as no surprise that I’m using INSERT…SELECT not FORALL given my previous misunderstanding of statement level firing timing for FORALL.

But I’d rather use a SQL type collection anyway with INSERT…SELECT to avoid PLS_00436 anyway (still waiting for an upgrade to a recent decent version).

However, what is also interesting about the compound trigger documentation is the usage of a flush threshold in the examples.

See here

CREATE OR REPLACE TRIGGER maintain_employee_salaries
  FOR UPDATE OF salary ON employees
    COMPOUND TRIGGER

-- Declarative Part:
-- Choose small threshhold value to show how example works:
  threshhold CONSTANT SIMPLE_INTEGER := 7;

  TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
  salaries  salaries_t;
  idx       SIMPLE_INTEGER := 0;

  PROCEDURE flush_array IS
    n CONSTANT SIMPLE_INTEGER := salaries.count();
  BEGIN
    FORALL j IN 1..n
      INSERT INTO employee_salaries VALUES salaries(j);
    salaries.delete();
    idx := 0;
    DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
  END flush_array;

  -- AFTER EACH ROW Section:

  AFTER EACH ROW IS
  BEGIN
    idx := idx + 1;
    salaries(idx).employee_id := :NEW.employee_id;
    salaries(idx).change_date := SYSDATE();
    salaries(idx).salary := :NEW.salary;
    IF idx >= threshhold THEN
      flush_array();
  END IF;
  END AFTER EACH ROW;

  -- AFTER STATEMENT Section:

  AFTER STATEMENT IS
  BEGIN
   flush_array();
  END AFTER STATEMENT;
END maintain_employee_salaries;

The alternative is to controlling the number of rows (via the size of the collection) being updated at anyone time in the INSERT or UPDATE statement.

This example above though seems much better and in hindsight I wish I’d done that in this piece of work that I’ve done recently.

Beware the compound trigger

In 11g, Oracle introduced a new feature in the compound trigger.

Through many years and versions, the solution to the mutating table / ora-04091 problem was often a clumsy combination of packaged global, before statement trigger, after row trigger and after statement trigger.

However, in 11g, the introduction of the compound trigger provided a much more compact solution, where you could declare your global, your statement level logic and any row level code in a single trigger.

Examples from the Oracle documentation:

Trouble is, under certain simple circumstances, they don’t work.

Take this illustration abstracted from a proposed VPD labelling solution on 11.1.0.6.

The illustration is a little artificial because all distractions have been removed but it’s taken from a real world example.

Essentially, three updates via a package cause an ORA-07445 but three direct update statements do not. Take a look:


dominic@DOM11G>prompt ---------- cleanup previous creates
---------- cleanup previous creates
dominic@DOM11G>drop table compound_trigger_test_tab1;

Table dropped.

Elapsed: 00:00:01.02
dominic@DOM11G>drop package compound_trigger_test_pkg;

Package dropped.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create table
---------- create table
dominic@DOM11G>
dominic@DOM11G>create table compound_trigger_test_tab1
  2  (col1 number
  3  ,col2 number);

Table created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- insert data
---------- insert data
dominic@DOM11G>
dominic@DOM11G>insert into compound_trigger_test_tab1 values (1,1);

1 row created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create compound trigger
---------- create compound trigger
dominic@DOM11G>
dominic@DOM11G>CREATE OR REPLACE TRIGGER compound_trigger_test_tri1
  2  FOR INSERT OR UPDATE ON compound_trigger_test_tab1
  3  REFERENCING NEW AS NEW OLD AS OLD
  4  COMPOUND TRIGGER
  5   --
  6   TYPE r_ctt IS RECORD 
  7   (col1    NUMBER
  8   ,col2    NUMBER);
  9   --
 10   TYPE t_ctt IS TABLE OF r_ctt
 11   INDEX BY PLS_INTEGER;
 12   --
 13   vt_ctt t_ctt;
 14   --
 15  BEFORE STATEMENT
 16  IS
 17  BEGIN
 18     --
 19     DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::before statement');
 20     NULL;
 21     --
 22  END BEFORE STATEMENT;
 23  --
 24  BEFORE EACH ROW
 25  IS
 26  BEGIN
 27      --
 28      DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::before row');
 29      NULL;
 30      --
 31  END BEFORE EACH ROW;
 32  --
 33  AFTER EACH ROW
 34  IS
 35  BEGIN
 36     --
 37     DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::after row');
 38     NULL;
 39     --
 40  END AFTER EACH ROW;
 41  --
 42  AFTER STATEMENT
 43  IS
 44    i PLS_INTEGER;
 45  BEGIN
 46     --
 47     DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::after statement');
 48     i := vt_ctt.FIRST;
 49     --
 50     WHILE (i IS NOT NULL)
 51     LOOP
 52         --
 53         NULL;
 54         --
 55         i := vt_ctt.NEXT(i);
 56         --
 57     END LOOP;
 58     --
 59  END AFTER STATEMENT;
 60  --
 61  END compound_trigger_test_tri1;
 62  /

Trigger created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>show errors
No errors.
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create package header
---------- create package header
dominic@DOM11G>
dominic@DOM11G>CREATE OR REPLACE PACKAGE compound_trigger_test_pkg
  2  AS
  3    --
  4    PROCEDURE p_update_tab1 (
  5      i_col1                     in     compound_trigger_test_tab1.col1%TYPE
  6    );
  7    --
  8  END;
  9  /

Package created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create package body
---------- create package body
dominic@DOM11G>
dominic@DOM11G>CREATE OR REPLACE PACKAGE BODY compound_trigger_test_pkg
  2  AS
  3    --
  4    PROCEDURE p_update_tab1 (
  5      i_col1                     in     compound_trigger_test_tab1.col1%TYPE
  6    )
  7    AS
  8    BEGIN
  9       --
 10       DBMS_OUTPUT.PUT_LINE('compound_trigger_test_pkg.p_update_tab1');
 11       UPDATE compound_trigger_test_tab1
 12       SET    col2 = col2
 13       WHERE  col1 = i_col1;
 14       --
 15    END p_update_tab1;
 16  END;
 17  /

Package body created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- run update via package test 1 - should be ok
---------- run update via package test 1 - should be ok
dominic@DOM11G>exec compound_trigger_test_pkg.p_update_tab1(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- run update via package 2 - should be ok
---------- run update via package 2 - should be ok
dominic@DOM11G>exec compound_trigger_test_pkg.p_update_tab1(2);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- run update via package 3 - should go bang
---------- run update via package 3 - should go bang
dominic@DOM11G>exec compound_trigger_test_pkg.p_update_tab1(3);
BEGIN compound_trigger_test_pkg.p_update_tab1(3); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Elapsed: 00:00:00.05
dominic@DOM11G>conn dominic@dom11g
Enter password: *******
Connected.
dominic@DOM11G>UPDATE compound_trigger_test_tab1
  2  SET    col2 = col2
  3  WHERE  col1 = 1;

1 row updated.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>UPDATE compound_trigger_test_tab1
  2  SET    col2 = col2
  3  WHERE  col1 = 2;

0 rows updated.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>UPDATE compound_trigger_test_tab1
  2  SET    col2 = col2
  3  WHERE  col1 = 3;

0 rows updated.

Elapsed: 00:00:00.00
dominic@DOM11G>

Alert log and trace file reveal the cause to be an ORA-07445:


Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x448] [PC:0x1B6A3B8, kkxtexe()+988]
Errors in file /app/oracle/diag/rdbms/dom11g/DOM11G/trace/dom11G_ora_4860.trc  (incident=845912):
ORA-07445: exception encountered: core dump [kkxtexe()+988] [SIGSEGV] [ADDR:0x448] [PC:0x1B6A3B8] [Address not mapped to object] []
ORA-21779: duration not active

So, for the moment, I would not go near them.
I’ve got an SR raised (it’s all I seem to doing these days is find bugs in 11.1.0.6 (more on the incompatibility between VPD and Materialized Views another time) and 11.1.0.7)
It’s back to multiple row and statement level triggers.

Follow

Get every new post delivered to your Inbox.

Join 72 other followers