Re: Trigger presence KILLS performance

In article <1018866358.24439.0.nnrp-10.9e984b29_at_news.demon.co.uk>, "Jonathan says...
>
>
>Thomas,
>
>Still does the same on 9.0.1.3 on HP.
>I had a closer look - including snapshots
>of latching and session stats.
>
>In fact the number of redo entries doubles,
>the number of db block changes doubles,
>etc. etc.
>
>Checking the redo log dump, you can see
>that when the trigger does not exist, deletes
>on the index take place as a bulk delete after
>the deletes from the table. When the trigger
>exists, oracle does:
> delete row
> delete index
> delete row
> delete index
> etc.
>
>
>
>
>--
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Author of:
>Practical Oracle 8i: Building Efficient Databases
>
>Next Seminar - Australia - July/August
>http://www.jlcomp.demon.co.uk/seminar.html
>
>Host to The Co-Operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>Thomas Kyte wrote in message ...
>>
>>been digging into this. seems to be an issue.
>>
>>if you do the following:
>>
>>drop table t;
>>create table t as select * from all_objects;
>>create index t_idx on t(owner);
>>alter session set sql_trace=true;
>>delete from t t_with_no_trigger_1 where owner = 'SYS';
>>rollback;
>>create or replace trigger t_trigger
>>before delete on t
>>begin
>> null;
>>end;
>>/
>>delete from t t_with_trigger_1 where owner = 'SYS';
>>rollback;
>>
>
>
>

Ok, here we go -- the long and winding story....

Take this script:

drop table t;
create table t ( x int, owner varchar2(30) ); insert into t select rownum, decode(mod(rownum,2), 1, 'SYS', 'PUBLIC' ) from sys.source$ where rownum <= 20000;
create index t_idx on t(owner);
alter session set sql_trace=true;
delete from t t_with_no_trigger_1 where owner = 'SYS'; rollback;
create or replace trigger t_trigger
before delete on t
begin null; end;
/
delete from t t_with_trigger_1 where owner = 'SYS'; rollback;

Now -- run that in 7.3, 8.0.6, 8.1.6, 8.1.7

What you will find is:

7.3  -- both deletes take 30k consistent mode gets....
8.0.6...8.1.6 -- both deletes take 10k consistem mode gets....
8.1.7 on up -- delete without trigger -- 10k, delete with trigger 30k

What happened is....
in 8.0 a performance enhancing feature, delayed index maintenance, was added. As you can see, on a bulk delete -- this enhanced the performance greatly. An unfortunate side effect of this was a consistent read issue in a special case of distributed queries. Therefore, this optimization (delayed index maintenance) is disabled when

o you have a trigger on the table
o use an index to access it

in order to avoid the other issue.

--
Thomas Kyte (tkyte@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