Search My Oracle Blog

Custom Search

08 January, 2007

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".

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016