Re: Trigger presence KILLS performance

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;

>