compile trigger "reuse settings" making difference? ORA-04020

From: zhu chao <zhuchao@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 16 Jan 2006 03:59:40 -0800

hi, all,
   We have a case, when adding column to database table, caused sessions
hang. Our oracle is 9205 running on solaris.

1.    The same code in test 9205 does not report error, but in production
database with several thousand connections, it caused high session when most
other session waiting on library cache pin/library cache lock.   Hanganalyze
does not report a valid blocker.

2.    The same code give out orror in 817 instance. but does not error in
test 9205 version.

   What I noticed from trace file, is , the 9205 version, compiled the
trigger with reuse setting, but 817 instance doesnot. Is this making the
difference?Anyone has some experience with that?  All the SQL are executed
by oracle internally. Recursive SQL.

you can test with the following test code:

set line 200 echo on
col object_name format a40

create table test as select * from dba_tables;
alter table test add last_modified date;

 create or replace trigger trig_test before update  on test
 for each row
 begin
 :new.last_analyzed:=sysdate;
 end;
/



  update test set owner='A' WHERE ROWNUM=1;

  ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

  SELECT OWNER,LAST_ANALYZED FROM test WHERE OWNER='A';

  ALTER TABLE test ADD TESTCOL DATE;

  SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;

   select object_name,status from user_objects where object_name='TRIG_TEST'
or object_name='TRIG2';
   set timing on
   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 8';

 update test set owner='A-TRITEST' WHERE OWNER='A';
   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT off';


  SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;
   select object_name,status from user_objects where
object_name='TRIG_TEST'  or object_name='TRIG2';





--
Regards
Zhu Chao
www.cnoug.org

compile trigger "reuse settings" making difference? ORA-04020

Other related posts: