Sometimes you trip up on Triggers

Me and a colleague once spent a couple of days on a simple delete statement. A
Pro*C application seemed to be running slow and the developer had identified this
portion :
EXEC SQL
delete
FROM
WHERE LOTID = :sqlLotId;

We looked at table statistics. We looked at the approximate number of rows per LOTID.
We looked at the index on LOTID. We used AUTOTRACE to see that we were encountering 8 consistent gets for 25 rows returned when running a SELECT for a single LOTID.

I started wondering about waits (enqueues, buffer busy, gc cache etc).
Then, and only then, did I run a 10046 trace.
Till then, I had figured "one table, one column driver, an EXPLAIN PLAN OR AUTOTRACE should suffice".

The 10046 trace immediately showed me where the problem began -- a "BEFORE EACH ROW" DELETE Trigger which was copying the rows into a History table and was firing for each of the 25 rows being deleted for a LOTID. And then when I looked at the History table, I found an "AFTER STATEMENT" INSERT Trigger which was, wonder of wonders, doing a Full Table Scan of the History table because it needed to delete some row from the History table !

Every Delete on the initial table would fire it's Trigger 25 times. Each execution of
that Trigger would, on inserting into the History table, cause a Full Table Scan of
the History table to satisfy the Trigger there !

The application team looked at the two triggers and decided that they did not
need the After Insert Trigger on the History table -- the delete that it ran didn't make sense. {It was probably inserted during code testing / data validation and wasn't removed soon enough !}


Problem Solved ! Nothing to do with Statistics or with rewriting the original SQL
but to do with "what happens down the line".