compile trigger "reuse settings " making difference? ORA-04020 2006-01-16 - By zhu chao
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
<div>hi, all,</div>
<div> We have a case, when adding column to database table, caused
sessions hang. Our oracle is 9205 running on solaris.</div>
<div> </div>
<div>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.
</div>
<div> </div>
<div>2. The same code give out orror in 817 instance. but does not
error in test 9205 version. </div>
<div> </div>
<div> 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.
</div>
<div> </div>
<div>you can test with the following test code:</div>
<p>set line 200 echo on<br>col object_name format a40</p>
<p>create table test as select * from dba_tables; <br>alter table test add last
_modified date; </p>
<p> create or replace trigger trig_test before update on test<br>
for each row<br> begin<br> :new.last_analyzed:=sysdate;<br> 
;end;<br>/</p>
<p> </p>
<p> update test set owner='A' WHERE ROWNUM=1;<br> <br> ALTER
SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';<br> <br>
SELECT OWNER,LAST_ANALYZED FROM test WHERE OWNER='A';<br> <br>
ALTER TABLE test ADD TESTCOL DATE;
<br> <br> SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;<br>
<br> select object_name,status from user_objects where object_name=
'TRIG_TEST' or object_name='TRIG2';<br> set timing on<br>  
; ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 8';
</p>
<p> update test set owner='A-TRITEST' WHERE OWNER='A';<br>
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT off';</p>
<p><br> SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;<br>
select object_name,status from user_objects where object_name='TRIG_TEST'
or object_name='TRIG2';</p>
<p> </p>
<p> </p>
<div><br>-- <br>Regards<br>Zhu Chao<br><a href="http://www.cnoug.org">www.cnoug
.org</a><br> </div>
|
|
