| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger performance
As a quick answer, one might say that an insert trigger of the type that
you are describing will not have any meaningful performance impact,
because you would not need to query another table to determine whether
"destination" will be null or not. The only impact is additional plsql
logic, which should be negligable.
But you should not take anyone's word for this.
Ben Ryan recommended using tkprof to determine whether the trigger made any impact on performance. To see how far one could go with this, I tried to take his advice and create a test of trigger performance.
I created the following table:
create table actions (
action_type varchar2(1), source varchar2(50), destination varchar2(50)
I used the following trigger assuming half of the ten values would lead to making the destination field null. Note that there is no table lookup involved.
create or replace trigger actions_tg
before insert on actions
for each row
begin
if :new.action_type in ('a','b','c','d','e') then
:new.destination := null;
end if;
end;
On an isolated machine, an NT test workstation running Oracle 8i, I ran the following test multiple times. Between each run the table was truncated. Also a new sqlplus client was started for each test -- note the "exit" at the end of the test. This gave me a unique trc file for each test.
alter session set sql_trace = true;
declare
i number;
v varchar2(5);
begin
for i in 1..100 loop
v := to_char(i);
insert into actions values ('a','a'||v,'a');
commit;
insert into actions values ('b','b'||v,'b');
commit;
insert into actions values ('c','c'||v,'c');
commit;
insert into actions values ('d','d'||v,'d');
commit;
insert into actions values ('e','e'||v,'e');
commit;
The summary timings were:
With Trigger
Non-recursive 1.39 1.82
recusive .53 .71
Non-recursive 1.45 1.80
recursive .59 .78
Without Trigger
Non-recursive 1.36 1.79
recursive .35 .35
Non-recursive 1.27 1.68
recursive .27 .68
Non-recursive 1.47 1.84
recursive .58 .76
If you note, the timings vary. This is to be expected. The number of blocks read should not vary, however. I ran more of the without trigger tests because I was unable to get the blocks to always agree. See the results at the bottom for more details, if you are interested.
The results are very close considering that 500 insert statements are involved in each of the tests. Indeed neither the with nor the without trigger appears to be a consistent winner in the race. I would expect that if the tests were conducted enough times one might be able to show statistically that the without trigger was significantly faster. But it is already clear that that statistical significance is irrelevant. Hence I will not bother to do the statistics, nor continue generating the tests.
The major reason, as I see it, for putting business rules close to the data, such as in a trigger, is to insure that no one, whether they go in through the application or with an ad hoc sqlplus session, can violate those business rules. Were they my business rules, I would certainly consider that goal more important than the loss of performance that might be involved.
Frank Hubeny
"Alex A." wrote:
> What is the performance impact of an insert trigger? > I have a table > ACTIONS(ACTION_TYPE, SOURCE, DESTINATION) > and depending on ACTION_TYPE, DESTINATION may or may not > be null. Is the cost of adding a trigger to enforce this > significant (there are only about 10 action types)? If > the overhead introduced by the trigger is significant, > I'd rather rely on the front-end checking (although this > is not the right thing to do...). > > Any advice? > > Thanks, > Alex > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.
Sumary text of the tkprof outut:
WITHOUT TRIGGER TEST 1 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 2 0.04 0.04 0 0 0 0 Execute 3 1.32 1.75 0 0 500 1 Fetch 0 0.00 0.00 0 0 0 0
total 5 1.36 1.79 0 0 500 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 6 0.04 0.04 0 0 0 0 Execute 501 0.31 0.31 0 5 1514 500 Fetch 1 0.00 0.00 0 2 0 1
total 508 0.35 0.35 0 7 1514 501
Misses in library cache during parse: 0
TEST 2 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 2 0.24 0.25 0 0 4 0 Execute 3 1.44 1.89 0 0 500 1 Fetch 0 0.00 0.00 0 0 0 0
total 5 1.68 2.14 0 0 504 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 17 0.20 0.20 0 0 0 0 Execute 512 0.35 0.72 0 6 1510 500 Fetch 20 0.00 0.00 0 43 0 12
total 549 0.55 0.92 0 49 1510 512
TEST 3 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 2 0.26 0.26 0 0 0 0 Execute 3 1.33 1.95 0 0 500 1 Fetch 0 0.00 0.00 0 0 0 0
total 5 1.59 2.21 0 0 500 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 12 0.16 0.17 0 0 0 0 Execute 507 0.35 0.52 0 6 1510 500 Fetch 12 0.01 0.01 0 32 0 8
total 531 0.52 0.70 0 38 1510 508
TEST 4 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 2 0.03 0.04 0 0 0 0 Execute 3 1.24 1.64 0 0 500 1 Fetch 0 0.00 0.00 0 0 0 0
total 5 1.27 1.68 0 0 500 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 6 0.03 0.03 0 0 0 0 Execute 501 0.24 0.65 0 5 1514 500 Fetch 1 0.00 0.00 0 2 0 1
total 508 0.27 0.68 0 7 1514 501
TEST 5 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 2 0.23 0.24 0 0 0 0 Execute 3 1.24 1.60 0 0 500 1 Fetch 0 0.00 0.00 0 0 0 0
total 5 1.47 1.84 0 0 500 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 6 0.13 0.13 0 0 0 0 Execute 501 0.45 0.63 0 6 1510 500 Fetch 1 0.00 0.00 0 2 0 1
total 508 0.58 0.76 0 8 1510 501
WITH TRIGGER TEST 1 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 2 0.16 0.17 0 0 0 0 Execute 3 1.23 1.65 0 0 500 1 Fetch 0 0.00 0.00 0 0 0 0
total 5 1.39 1.82 0 0 500 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 7 0.12 0.13 0 0 0 0 Execute 502 0.41 0.58 0 6 1510 500 Fetch 3 0.00 0.00 0 9 0 2
total 512 0.53 0.71 0 15 1510 502
TEST 2 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 2 0.21 0.21 0 0 0 0 Execute 3 1.24 1.59 0 0 500 1 Fetch 0 0.00 0.00 0 0 0 0
total 5 1.45 1.80 0 0 500 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 7 0.16 0.17 0 0 0 0 Execute 502 0.43 0.61 0 6 1510 500 Fetch 3 0.00 0.00 0 9 0 2
total 512 0.59 0.78 0 15 1510 502Received on Mon Sep 20 1999 - 00:23:34 CDT
 |
 |