2012/07/04

Before/After Trigger efficiency

Something changes continuously... and trigger efficiency too :).
Please compare Oracle statement about triggers (see "Note" about trigger efficiency)
Oracle 9iOracle 10g and Oracle 11g.

I`ve tested this on 11.1.0.7 (the only database version in my hands now).
Its behavior matches 11g and 9i documents.

I used triggers with empty body like this:

create or replace trigger t1_tr_bu
before update on t1
for each row
begin
null;
end;
/

create or replace trigger t1_tr_au
after update on t1
for each row
begin
null;
end;
/


and receive these results:
without triggers



1 row updated.


Statistics
----------------------------------------------------------
 0  recursive calls
 1  db block gets
 7  consistent gets
 0  physical reads
352  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed


with before trigger




1 row updated.


Statistics
----------------------------------------------------------
 0  recursive calls
 2  db block gets
 7  consistent gets
 0  physical reads
296  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed



with after trigger

1 row updated.


Statistics
----------------------------------------------------------
 0  recursive calls
 1  db block gets
 7  consistent gets
 0  physical reads
 0  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed



2012/03/03

Struggling with ORA-00980


If you are struggling with ORA-00980, and all things seem to be ok. Even you can use synonym with dblink in ordinary sql. But in pl/sql it raises ORA-00980.
As last resort you can try to check global name for databases from both side of dblink. They both must not match each other.
Here is query to check global name: select * from global_name;