Re: Cascading triggers: too many open cursors?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 5 Jul 2001 10:39:47 -0700
Message-ID: <9i28p301h9i@drn.newsguy.com>

In article <dxZ07.12118$DW1.501132_at_iad-read.news.verio.net>, gamma_at_clark.net says...
>
>[Oracle8i]
>
>I have three triggers created by the following statements:
>
>create trigger delete1 after delete on table1 for each row
>begin
> delete from table2 where column1 = :old.column1;
>end;
>.
>/
>create trigger delete2 after delete on table2 for each row
>begin
> delete from table3 where column1 = :old.column1;
>end;
>.
>/
>create trigger delete3 after delete on table3 for each row
>begin
> delete from table4 where column1 = :old.column1;
>end;
>.
>/
>
>In other words, cascading triggers. There are four tables named
>table1, table2, table3, and table4 all with (let's say) a single
>column named "column1". Assuming the tables have many rows in common,
>a typical delete from table1 will propagate to the other three.
>
>The question is, how many cursors are opened by such a cascade? How
>many are open at once? I came up with 6:
>- one for each trigger
>- one for each delete statement
>
>The trouble is, my tests seem to indicate that it takes many more.
>If I set OPEN_CURSORS to 16 in init.ora, I get a "too many open
>cursors" error somewhere in the middle trigger(!).
>

you shouldn't, I just coded up:

ops$tkyte_at_ORA817.US.ORACLE.COM> create trigger delete3 after delete on table3 for each row
  2 begin
  3 delete from table4 where column1 = :old.column1;   4
  5 for x in ( select sql_text

  6                 from v$open_cursor
  7                   where sid = ( select sid
  8                                from v$mystat
  9                               where rownum=1)
 10             )
 11    loop
 12      dbms_output.put_line( x.sql_text );

 13 end loop;
 14
 15 for x in ( select value

 16                 from v$mystat
 17                where statistic# = ( select statistic#
 18                                       from v$statname
 19                                      where name = 'opened cursors current' )
 20             )
 21    loop
 22      dbms_output.put_line( x.value || ' currently opened cursors' );

 23 end loop;
 24 end;
 25 /

Trigger created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> delete from table1;

DELETE FROM TABLE4 WHERE COLUMN1 = :b1
DELETE FROM TABLE2 WHERE COLUMN1 = :b1
DELETE FROM TABLE3 WHERE COLUMN1 = :b1

delete from table1
SELECT SQL_TEXT FROM V$OPEN_CURSOR WHERE SID = (SELECT S 6 currently opened cursors

1 row deleted.

ops$tkyte_at_ORA817.US.ORACLE.COM> show parameter open

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
open_cursors                         integer 16

worked just fine

HOWEVER, the correct approach is to NOT use triggers for this - you should use ON DELETE CASCADE with referential integrity. Let the server do the work for you -- faster and easier.

>I've been trying to find the answer to this problem for days, going
>through _Oracle8: The Complete Reference_ and the online docs, with
>little luck so far. Nothing spells out where all the cursors are
>coming from.
>
>Any help would be greatly appreciated. The schema I'm designing is
>somewhat dynamic, and I need to know whether the above will work,
>or if OPEN_CURSORS would have to be set to a ridiculously high
>figure that makes this unworkable.
>

what do you consider "rdiculously high". I typically run with a minimum of 1000.

>Another thing: if I make cascading insert triggers going in the
>other direction, they seem to work fine. Why?
>
>In fact, if anyone knows of a way to trace through and find out when
>cursors are opening and what they're opening for, that'd be a big
>help.
>
>
>--
>
>Paul Brinkley
>gamma_at_clark.net
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jul 05 2001 - 12:39:47 CDT