| Trigger presence KILLS performance [message #50761] |
Thu, 04 April 2002 21:15  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hi all,
I just ran into a performance problem when using triggers.
The situation is this:
I have a table containing a few million records.
deleting from this table takes x ms. depending on the number of rows.
After adding a simple before delete statement level trigger my deletes now roughly take 2x ms. to complete.
I've run a few tests and it's definitely the presence of an enabled trigger that causes the performance drop.
even a trigger that doesn't do anything (BEGIN NULL; END;) causes the performance drop.
Does anyone have an explanation why this is so and more importantly, is there a way around this?
Thierry
|
|
|
|
| Re: Trigger presence KILLS performance [message #50763 is a reply to message #50761] |
Thu, 04 April 2002 23:09  |
shri
Messages: 15
Registered: November 2001
|
Junior Member |
|
|
i think that is the problem of the parameters that you have passed to the table. Check the PcT free parameter of the table if it is 40 make it 10 make the maximum utilization of the parameters.
try to write an after delete trigger on a particular column where the delete is based i.e some key columns
|
|
|
|
| Re: Trigger presence KILLS performance [message #50769 is a reply to message #50763] |
Fri, 05 April 2002 01:33 |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo Shri,
First I want to thank you for your reply.
But your suggestion does not solve my problem.
I think that Oracle does something complete different when you place a trigger on a table.
When I delete 10000 records from a 4million records table it takes about 8 seconds, but when I set a Before Delete STATEMENT trigger on this table who is doing nothing (just BEGIN NULL;END;)´, then the delete takes about 16 seconds????
It must be a Before Delete trigger because I must check if the user is allowed to delete these specific records?
And my PcT Free is 10.
Greetings,
Thierry
P.S. Oracle version 8.1.7.0.0
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
