Way back in 1995/1996, when I started working, I remember my colleagues being very enthusiastic about the upgrade from Oracle6 to Oracle7. Many new features became available, and the one that stood out the most, according to my coworkers, was the birth of database triggers. Before Oracle7, only primary, unique, check and unenforced foreign keys were implemented. The other constraints were typically validated in the client. With the arrival of database triggers, it was now possible to have all business rules implemented on the database. No matter what client application was accessing the database, they all had to satisfy the business rules. A great idea.
I too loved the idea. In 1998 I was responsible for implementing the entire business rule layer of a new system and I made sure no invalid data was able to enter the database. I remember my colleagues even getting a little frustrated when simple insert scripts to enter test data didn't work because of yet another business rule implemented by me with the use of database triggers. We were using Headstart Utilities, which made the implementation of business rules with database triggers even easier, by offering numerous utilities that generated database trigger code for:
- populating the ID column from a sequence
- populating auditing columns
- standard code for circumventing the well-known mutating table problem
- and much more
The result was a system that used database triggers heavily which I thought was very robust. And I certainly wasn't alone. This way of dealing with business rules became the de facto standard in Oracle consultancy in the Netherlands for many years to come.
Then, somewhere in 2002/2003, I discovered the Ask Tom website, and I was intrigued by the quality of the answers, by the abundance of test cases clearly showing all Tom's points, and by the use of clear and simple language that makes reading for foreigners almost a piece of cake. And above all, I agreed with virtually every opinion of Tom on several subjects. But to my big surprise, he seemed to dislike database triggers. How on earth was that possible? One of my favourite features! I concluded I had met the first topic on which we disagreed. His arguments against database triggers are roughly:
1) they make things happen "automagically"
2) they are rarely implemented correct
3) they slow down DML
At the time, I could understand the first point, but the second and third one didn't drive home with me. And, what's the alternative when you want to implement business rules, but don't want to use database triggers? About two years ago, during and just after Tom gave a three day seminar in my home city, it all suddenly made sense to me. Shortly after, the alternative became clear as well. First of all let me elaborate on the disadvantages of database triggers.
1) Database triggers make things happen "automagically"
The first point was one I could immediately agree with, and my initial reaction was: that's the price to pay. It's possible you deliver value 'A' and some trigger changed the value into 'B'. This is non intuitive and you might get surprised by this behaviour. Things will get worse when doing DML inside a trigger, because you could get a chain of reactions: table t1 gets mutated, which fires a trigger that does an insert into t2, which has a trigger that does ... and so on. This can be a nightmare to understand and debug and you can imagine some nasty unexpected side effects happening. This problem can of course be slightly alleviated by proper coding and documentation, but it will never be straightforward.
2) Database triggers are rarely implemented correct
Most developers tend to forget they are writing code that has to work in a multi user environment, especially when coding database triggers. And I used to be one of them. For example, let's take a typical overlap business rule stating that periods, defined by the columns startdate and enddate, shall not overlap with other periods in the same table. If I opened two SQL*Plus sessions and inserted a period in session1, not committing yet, then switch to session2 and inserted the same period, they would both not see each others data and be not overlapping. Now simply commit both and voila: inconsistent data has entered your database! Because most applications, including Oracle*Forms, almost always do their DML statements immediately followed by a commit, the chances of this happening are reduced a lot, but it remains a fundamental mistake.
Lucas Jellema wrote a very clear article on the AMIS blog about this exact topic: On the false sense of security with PL/SQL based implementation of business rules - and what to do about it, where he explains why you have to add serialization code by locking. Another nice example of how to code a business rule correctly can be found as a blog comment on Tom Kyte's blog, written by Toon Koppelaars.
3) Database triggers slow down DML
I have spent a lot of time tuning queries and batches lately on a system that was built on Oracle 7.3 with A LOT of triggers. Using a database trigger compared to the alternative - which I'll discuss in a minute - is a bit slower. But the real pain comes when doing set operations, typically at night in batches. If coded recently, the code is probably doing single DML statements or bulk processing in PL/SQL.
But when doing bulk processing on table X, you will find yourself very unpleasantly surprised by the fact that all row level triggers on table X are fired "for each row". So even if you coded all nice bulk inserts adding 100 rows at once to table X, all your row level triggers will fire 100 times and all SQL in those row level triggers are executed 100 times, as can be seen in this little test:
create table t1 (id number(4), description varchar2(30))
/
create table t2 (id number(4), description varchar2(30), modification_date date)
/
create trigger t1_aru
after update on t1
for each row
begin
insert into t2 values (:new.id, :new.description, sysdate);
end;
/
insert into t1
select level
, 'some description ' || to_char(level)
from dual
connect by level <= 1000
/
alter session set sql_trace true
/
update t1
set description = 'New description'
where id <= 100
/
disconnect
The tkprof now shows:
********************************************************************************
update t1
set description = 'New description'
where id <= 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 1 0 0
Execute 1 0.01 0.14 0 7 104 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.17 0 8 104 100
<...>
********************************************************************************
INSERT INTO T2
VALUES
(:B1 , :B2 , SYSDATE)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.01 0.04 0 1 119 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.01 0.04 0 1 119 100
<...>
********************************************************************************
So 100 executions of the insert statement and certainly no nice bulk processing as in the initial update statement. If your system was built using database triggers to enforce your business rules, like I described in the first paragraphs, then you are in a lot of trouble performance wise these days, because:
- you are stuck doing things row by row, for the enforcement of each and every business rule
- the data volumes are increasing every year, in a successful business at least
So you may find yourself being unable to complete a simple batch job processing 1,000,000 records in a nightly batch window, only because of the presence of numerous row level triggers.
So what's the alternative?
The alternative, as Tom has often mentioned, is an api. An Application Programming Interface. In other words, just a package containing all actions that can be done to the entity. For each action all necessary checks are performed. I'll show the difference between the two approaches with an example of implementing a business rule "It is not possible to end a customer's last contract." on a contracts table. First I'll demonstrate the trigger approach, which suffers from the mutating table problem, so some code to circumvent this is applied as well:
rwijk@ORA10GR2> create table contracts_trigger_approach
2 ( id number(9) primary key
3 , customer_id number(9)
4 , startdate date not null
5 , enddate date
6 )
7 /
Tabel is aangemaakt.
rwijk@ORA10GR2> insert into contracts_trigger_approach
2 select level
3 , trunc((level+1)/2)
4 , date '2007-01-01'
5 , null
6 from dual
7 connect by level <= 20000
8 /
20000 rijen zijn aangemaakt.
rwijk@ORA10GR2> create index ct_i1 on contracts_trigger_approach(customer_id)
2 /
Index is aangemaakt.
rwijk@ORA10GR2> exec dbms_stats.gather_table_stats(user,'contracts_trigger_approach',cascade=>true)
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> create package contracts_pkg
2 as
3 type r_contracts is record
4 ( id contracts_trigger_approach.id%type
5 , customer_id contracts_trigger_approach.customer_id%type
6 );
7 type tr_contracts is table of r_contracts index by binary_integer
8 ;
9 array_contracts tr_contracts;
10 empty tr_contracts
11 ;
12 procedure check_contract_ending_allowed
13 ( p_contract_id in contracts_trigger_approach.id%type
14 , p_customer_id in contracts_trigger_approach.customer_id%type
15 );
16 end contracts_pkg;
17 /
Package is aangemaakt.
rwijk@ORA10GR2> create package body contracts_pkg
2 as
3 procedure lock_customer (p_customer_id in contracts_trigger_approach.customer_id%type)
4 is
5 l_lock_dummy integer;
6 begin
7 l_lock_dummy := dbms_lock.request
8 ( id => dbms_utility.get_hash_value
9 ( name => 'contracts_pkg.lock_customer' || to_char(p_customer_id)
10 , base => 1
11 , hash_size => power(2,30)
12 )
13 );
14 end lock_customer
15 ;
16 function exists_other_open_contracts
17 ( p_contract_id in contracts_trigger_approach.id%type
18 , p_customer_id in contracts_trigger_approach.customer_id%type
19 ) return boolean
20 is
21 l_dummy varchar2(14);
22 begin
23 select 'open contracts'
24 into l_dummy
25 from contracts_trigger_approach
26 where customer_id = p_customer_id
27 and id != p_contract_id
28 and enddate is null
29 ;
30 return true;
31 exception
32 when no_data_found then
33 return false;
34 when too_many_rows then
35 return true;
36 end exists_other_open_contracts
37 ;
38 procedure check_contract_ending_allowed
39 ( p_contract_id in contracts_trigger_approach.id%type
40 , p_customer_id in contracts_trigger_approach.customer_id%type
41 )
42 is
43 begin
44 lock_customer(p_customer_id)
45 ;
46 if not exists_other_open_contracts (p_contract_id, p_customer_id)
47 then
48 raise_application_error
49 ( -20000
50 , 'Cannot end a customer''s last open contract.'
51 );
52 end if
53 ;
54 end check_contract_ending_allowed
55 ;
56 end contracts_pkg;
57 /
Package-body is aangemaakt.
rwijk@ORA10GR2> create or replace trigger contracts_bsu
2 before update of enddate on contracts_trigger_approach
3 begin
4 contracts_pkg.array_contracts := contracts_pkg.empty;
5 end;
6 /
Trigger is aangemaakt.
rwijk@ORA10GR2> create or replace trigger contracts_aru
2 after update of enddate on contracts_trigger_approach
3 for each row
4 begin
5 contracts_pkg.array_contracts(contracts_pkg.array_contracts.count+1).id := :new.id;
6 contracts_pkg.array_contracts(contracts_pkg.array_contracts.count).customer_id := :new.customer_id;
7 end;
8 /
Trigger is aangemaakt.
rwijk@ORA10GR2> create or replace trigger contracts_asu
2 after update of enddate on contracts_trigger_approach
3 begin
4 for i in 1 .. contracts_pkg.array_contracts.count
5 loop
6 contracts_pkg.check_contract_ending_allowed
7 ( contracts_pkg.array_contracts(i).id
8 , contracts_pkg.array_contracts(i).customer_id
9 );
10 end loop;
11 end;
12 /
Trigger is aangemaakt.
rwijk@ORA10GR2> select * from contracts_trigger_approach where customer_id in (617,618)
2 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 617 01-01-2007 00:00:00
1234 617 01-01-2007 00:00:00
1235 618 01-01-2007 00:00:00
1236 618 01-01-2007 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> update contracts_trigger_approach
2 set enddate = sysdate
3 where id = 1234
4 /
1 rij is bijgewerkt.
rwijk@ORA10GR2> update contracts_trigger_approach
2 set enddate = sysdate
3 where id = 1235
4 /
1 rij is bijgewerkt.
rwijk@ORA10GR2> update contracts_trigger_approach
2 set enddate = sysdate
3 where id = 1236
4 /
update contracts_trigger_approach
*
FOUT in regel 1:
.ORA-20000: Cannot end a customer's last open contract.
ORA-06512: in "RWIJK.CONTRACTS_PKG", regel 48
ORA-06512: in "RWIJK.CONTRACTS_ASU", regel 4
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.CONTRACTS_ASU'.
rwijk@ORA10GR2> select * from contracts_trigger_approach where customer_id in (617,618)
2 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 617 01-01-2007 00:00:00
1234 617 01-01-2007 00:00:00 13-09-2007 21:30:41
1235 618 01-01-2007 00:00:00 13-09-2007 21:30:41
1236 618 01-01-2007 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
So we can see the business rule is enforced. Note that there is some code as well to lock the customer, so in case another session simultaneously tries to end another contracts from the same customer, then this session gets blocked until the first session is either committed or rollbacked.
Now let's take a look at an api approach. Please note that I used the phrase "an api approach", because I could think of several valid ones:
rwijk@ORA10GR2> create table contracts_api_approach
2 ( id number(9) primary key
3 , customer_id number(9)
4 , startdate date not null
5 , enddate date
6 )
7 /
Tabel is aangemaakt.
rwijk@ORA10GR2> insert into contracts_api_approach
2 select level
3 , trunc((level+1)/2)
4 , date '2007-01-01'
5 , null
6 from dual
7 connect by level <= 20000
8 /
20000 rijen zijn aangemaakt.
rwijk@ORA10GR2> create index ca_i1 on contracts_api_approach(customer_id)
2 /
Index is aangemaakt.
rwijk@ORA10GR2> exec dbms_stats.gather_table_stats(user,'contracts_api_approach',cascade=>true)
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> create package contracts_api
2 as
3 procedure end_contract
4 ( p_contract_id in contracts_api_approach.id%type
5 , p_customer_id in contracts_api_approach.customer_id%type
6 , p_enddate in contracts_api_approach.enddate%type
7 );
8 end contracts_api;
9 /
Package is aangemaakt.
rwijk@ORA10GR2> create package body contracts_api
2 as
3 procedure lock_customer (p_customer_id in contracts_trigger_approach.customer_id%type)
4 is
5 l_lock_dummy integer;
6 begin
7 l_lock_dummy := dbms_lock.request
8 ( id => dbms_utility.get_hash_value
9 ( name => 'contracts_pkg.lock_customer' || to_char(p_customer_id)
10 , base => 1
11 , hash_size => power(2,30)
12 )
13 );
14 end lock_customer
15 ;
16 function contract_exists (p_contract_id in contracts_api_approach.id%type) return boolean
17 is
18 l_dummy varchar2(6);
19 begin
20 select 'exists'
21 into l_dummy
22 from contracts_api_approach
23 where id = p_contract_id
24 ;
25 return true;
26 exception
27 when no_data_found then
28 return false;
29 end contract_exists
30 ;
31 procedure end_contract
32 ( p_contract_id in contracts_api_approach.id%type
33 , p_customer_id in contracts_api_approach.customer_id%type
34 , p_enddate in contracts_api_approach.enddate%type
35 )
36 is
37 begin
38 lock_customer(p_customer_id)
39 ;
40 update contracts_api_approach c1
41 set c1.enddate = p_enddate
42 where c1.id = p_contract_id
43 and exists
44 ( select 'another open contract from same customer'
45 from contracts_api_approach c2
46 where c2.id != c1.id
47 and c2.customer_id = c1.customer_id
48 and c2.enddate is null
49 )
50 ;
51 if sql%rowcount = 0
52 then
53 if contract_exists(p_contract_id)
54 then
55 raise_application_error
56 ( -20000
57 , 'Cannot end a customer''s last open contract.'
58 );
59 end if;
60 end if
61 ;
62 end end_contract
63 ;
64 end contracts_api;
65 /
Package-body is aangemaakt.
rwijk@ORA10GR2> select * from contracts_api_approach where customer_id in (617,618)
2 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 617 01-01-2007 00:00:00
1234 617 01-01-2007 00:00:00
1235 618 01-01-2007 00:00:00
1236 618 01-01-2007 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> exec contracts_api.end_contract(1234,617,sysdate)
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> exec contracts_api.end_contract(1235,618,sysdate)
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> exec contracts_api.end_contract(1236,618,sysdate)
BEGIN contracts_api.end_contract(1236,618,sysdate); END;
*
FOUT in regel 1:
.ORA-20000: Cannot end a customer's last open contract.
ORA-06512: in "RWIJK.CONTRACTS_API", regel 55
ORA-06512: in regel 1
rwijk@ORA10GR2> select * from contracts_api_approach where customer_id in (617,618)
2 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 617 01-01-2007 00:00:00
1234 617 01-01-2007 00:00:00 20-12-2009 11:53:44
1235 618 01-01-2007 00:00:00 20-12-2009 11:53:44
1236 618 01-01-2007 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
Here the api calls have exactly the same results as the trigger based approach. But let's compare the performance of both, by performing 2000 updates, 1000 successful and 1000 unsuccessful ones caught in an exception handler:
rwijk@ORA10GR2> begin
2 runstats_pkg.rs_start;
3 for i in 1..2000
4 loop
5 declare
6 e exception;
7 pragma exception_init(e,-20000);
8 begin
9 update contracts_trigger_approach
10 set enddate = sysdate
11 where id = i
12 ;
13 exception
14 when e then null;
15 end;
16 end loop;
17 runstats_pkg.rs_middle;
18 for i in 1..2000
19 loop
20 declare
21 e exception;
22 pragma exception_init(e,-20000);
23 begin
24 contracts_api.end_contract(i,trunc((i+1)/2),sysdate);
25 exception
26 when e then null;
27 end;
28 end loop;
29 runstats_pkg.rs_stop(100);
30 end;
31 /
Run1 draaide in 130 hsecs
Run2 draaide in 35 hsecs
Run1 draaide in 371,43% van de tijd
Naam Run1 Run2 Verschil
STAT.buffer is pinned count 3,650 3,825 175
LATCH.simulator lru latch 657 837 180
LATCH.simulator hash latch 660 841 181
STAT.calls to kcmgcs 367 182 -185
STAT.active txn count during cleanout 363 177 -186
LATCH.undo global data 363 177 -186
STAT.cleanout - number of ktugct calls 363 177 -186
STAT.table fetch continued row 531 175 -356
STAT.buffer is not pinned count 8,881 8,350 -531
STAT.no work - consistent read gets 2,889 2,356 -533
STAT.heap block compress 1,040 284 -756
STAT.free buffer requested 46 1,027 981
STAT.calls to kcmgas 27 1,013 986
LATCH.checkpoint queue latch 4 1,000 996
STAT.switch current to new buffer 0 1,000 1,000
STAT.index fetch by key 2,000 3,000 1,000
LATCH.In memory undo latch 1,000 0 -1,000
STAT.execute count 4,001 3,001 -1,000
STAT.enqueue requests 1,007 2 -1,005
LATCH.enqueue hash chains 1,011 3 -1,008
LATCH.enqueues 1,011 2 -1,009
STAT.consistent gets from cache 13,613 14,714 1,101
STAT.consistent gets 13,613 14,714 1,101
STAT.calls to get snapshot scn: kcmgss 4,358 3,176 -1,182
STAT.rollback changes - undo records applied 1,362 0 -1,362
STAT.consistent gets - examination 8,363 10,177 1,814
LATCH.shared pool 2,953 1,005 -1,948
LATCH.cache buffers lru chain 49 2,027 1,978
LATCH.object queue header operation 137 3,069 2,932
STAT.recursive calls 6,004 3,002 -3,002
STAT.redo entries 4,771 1,364 -3,407
LATCH.row cache objects 4,146 12 -4,134
STAT.db block changes 9,599 2,759 -6,840
STAT.session logical reads 24,933 17,005 -7,928
STAT.db block gets 11,320 2,291 -9,029
STAT.db block gets from cache 11,320 2,291 -9,029
LATCH.cache buffers chains 56,966 28,173 -28,793
LATCH.library cache pin 36,019 6,014 -30,005
STAT.physical read bytes 114,688 73,728 -40,960
STAT.physical read total bytes 114,688 73,728 -40,960
LATCH.library cache 50,048 6,019 -44,029
STAT.undo change vector size 215,156 108,052 -107,104
STAT.session uga memory max 128,872 0 -128,872
STAT.session pga memory max 262,144 0 -262,144
STAT.redo size 957,892 328,976 -628,916
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
155,080 49,205 -105,875 315.17%
PL/SQL-procedure is geslaagd.
So a single ending of a contract is almost four times as fast and using only 32% of the latches. I admit that I have chosen a business rule that is one of the worst kinds. It has to take care of the mutating table problem and it has to have code to handle multi user situations. The differences will not be this big when for example comparing a business rule to automatically fill an ID by a sequence. But even then there are differences as can be seen in this OTN thread.
But how do you make sure people cannot write direct DML statements using the api approach?
This is done by getting your privileges right. Typically you will have an owner schema that has only granted the execute privilege on all api's to all user schemas. There shouldn't be a single table privilege granted to anyone. You could even revoke the "create session" privilege from the owner schema after each install, to be really safe. This way no direct DML on the underlying tables is allowed.
But what if my screen painting application should be based on a table or view?
If there really is no other way in your developing environment than selecting a table or view to create screens/forms for entering data, then you can always create views with instead of triggers. These triggers will then redirect all DML on the views to the existing api. Additionally, you'd have to grant select, update, insert and delete on those views to the users.
But how is an api advantageous in batches when doing set operations?
Because now you have the flexibility to code a separate api call to update in bulk. Or, to code one routine that processes an array of input values where you'd call the routine with only 1 record during daily OLTP activities, and with lots of records during batches at night. Here is an example with the contracts table, doing a 5000 row update. The contracts_trigger_approach is the same as listed earlier. For the api approach, a new api is created with a general routine that can handle either 1 or many ending of contracts at the same time:
rwijk@ORA10GR2> create table contracts_api_approach
2 ( id number(9) primary key
3 , customer_id number(9)
4 , startdate date not null
5 , enddate date
6 )
7 /
Tabel is aangemaakt.
rwijk@ORA10GR2> insert into contracts_api_approach
2 select level
3 , trunc((level+1)/2)
4 , date '2007-01-01'
5 , null
6 from dual
7 connect by level <= 20000
8 /
20000 rijen zijn aangemaakt.
rwijk@ORA10GR2> create index i2 on contracts_api_approach (customer_id)
2 /
Index is aangemaakt.
rwijk@ORA10GR2> create type t_contracts_object as object
2 ( id number(9)
3 , customer_id number(9)
4 , startdate date
5 , enddate date
6 );
7 /
Type is aangemaakt.
rwijk@ORA10GR2> create type t_contracts is table of t_contracts_object;
2 /
Type is aangemaakt.
rwijk@ORA10GR2> exec dbms_stats.gather_table_stats(user,'contracts_api_approach',cascade=>true)
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> create package contracts_api
2 as
3 procedure end_contract (p_contracts t_contracts);
4 end contracts_api;
5 /
Package is aangemaakt.
rwijk@ORA10GR2> create package body contracts_api
2 as
3 procedure lock_customer (p_customer_id in contracts_trigger_approach.customer_id%type)
4 is
5 l_lock_dummy integer;
6 begin
7 l_lock_dummy := dbms_lock.request
8 ( id => dbms_utility.get_hash_value
9 ( name => 'contracts_pkg.lock_customer' || to_char(p_customer_id)
10 , base => 1
11 , hash_size => power(2,30)
12 )
13 );
14 end lock_customer
15 ;
16 procedure end_contract (p_contracts t_contracts)
17 is
18 begin
19 for i in 1 .. p_contracts.count
20 loop
21 lock_customer(p_contracts(i).customer_id);
22 end loop
23 ;
24 forall i in 1 .. p_contracts.count
25 update contracts_api_approach c1
26 set c1.enddate = treat(p_contracts(i) as t_contracts_object).enddate
27 where c1.id = treat(p_contracts(i) as t_contracts_object).id
28 and exists
29 ( select 'another open contract from same customer'
30 from contracts_api_approach c2
31 where c2.id != c1.id
32 and c2.customer_id = c1.customer_id
33 and c2.enddate is null
34 )
35 ;
36 if sql%rowcount != p_contracts.count
37 then
38 raise_application_error
39 ( -20000
40 , 'Cannot end a customer''s last open contract.'
41 );
42 end if
43 ;
44 end end_contract
45 ;
46 end contracts_api;
47 /
Package-body is aangemaakt.
rwijk@ORA10GR2> select * from contracts_api_approach where customer_id in (617,618)
2 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 617 01-01-2007 00:00:00
1234 617 01-01-2007 00:00:00
1235 618 01-01-2007 00:00:00
1236 618 01-01-2007 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> exec contracts_api.end_contract(t_contracts(t_contracts_object(1234,617,null,sysdate)))
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> exec contracts_api.end_contract(t_contracts(t_contracts_object(1235,618,null,sysdate)))
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> exec contracts_api.end_contract(t_contracts(t_contracts_object(1236,618,null,sysdate)))
BEGIN contracts_api.end_contract(t_contracts(t_contracts_object(1236,618,null,sysdate))); END;
*
FOUT in regel 1:
.ORA-20000: Cannot end a customer's last open contract.
ORA-06512: in "RWIJK.CONTRACTS_API", regel 38
ORA-06512: in regel 1
rwijk@ORA10GR2> pause
rwijk@ORA10GR2> select * from contracts_api_approach where customer_id in (617,618)
2 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 617 01-01-2007 00:00:00
1234 617 01-01-2007 00:00:00 20-12-2009 12:05:57
1235 618 01-01-2007 00:00:00 20-12-2009 12:05:57
1236 618 01-01-2007 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
Here an implementation is chosen that is based on the assumption that most contracts that are ended will not fail due to this business rule. That's why the update is done first, and after the update a check is performed to see if all records got updated. If not all records were updated, an error is raised. Here too, different implementations are possible.
A performance comparison between a 5000 row update using the trigger approach and the api approach:
rwijk@ORA10GR2> alter session set sql_trace true
2 /
Sessie is gewijzigd.
Verstreken: 00:00:00.09
rwijk@ORA10GR2> declare
2 l_contracts t_contracts := t_contracts();
3 begin
4 runstats_pkg.rs_start;
5 update contracts_trigger_approach
6 set enddate = sysdate
7 where id between 1 and 10000
8 and mod(id,2) = 0
9 ;
10 runstats_pkg.rs_middle;
11 for i in 1 .. 5000
12 loop
13 l_contracts.extend;
14 l_contracts(i) := t_contracts_object(2*i,i,null,sysdate);
15 end loop
16 ;
17 contracts_api.end_contract(l_contracts)
18 ;
19 runstats_pkg.rs_stop(100);
20 end;
21 /
Run1 draaide in 170 hsecs
Run2 draaide in 79 hsecs
Run1 draaide in 215,19% van de tijd
Naam Run1 Run2 Verschil
STAT.dirty buffers inspected 83 190 107
STAT.free buffer inspected 152 360 208
STAT.redo size 1,660,892 1,660,684 -208
STAT.data blocks consistent reads - undo records a 719 1 -718
STAT.consistent changes 755 32 -723
LATCH.undo global data 1,666 936 -730
STAT.table fetch continued row 921 0 -921
LATCH.cache buffers chains 77,528 78,510 982
STAT.no work - consistent read gets 6,013 5,007 -1,006
STAT.session logical reads 30,349 33,588 3,239
STAT.consistent gets 18,621 21,878 3,257
STAT.consistent gets from cache 18,621 21,878 3,257
LATCH.simulator hash latch 1,859 5,540 3,681
LATCH.simulator lru latch 1,841 5,527 3,686
STAT.consistent gets - examination 6,660 10,935 4,275
STAT.free buffer requested 173 5,090 4,917
STAT.calls to kcmgas 107 5,074 4,967
STAT.switch current to new buffer 29 4,999 4,970
STAT.buffer is pinned count 14,978 9,996 -4,982
STAT.index fetch by key 8 5,002 4,994
STAT.rows fetched via callback 3 5,000 4,997
STAT.execute count 5,007 5 -5,002
STAT.enqueue requests 5,018 10 -5,008
STAT.recursive calls 5,064 46 -5,018
LATCH.shared pool 5,072 2 -5,070
LATCH.enqueue hash chains 5,110 21 -5,089
LATCH.enqueues 5,107 16 -5,091
LATCH.checkpoint queue latch 260 5,378 5,118
STAT.buffer is not pinned count 10,938 20,006 9,068
LATCH.library cache pin 10,084 40 -10,044
LATCH.library cache 10,157 48 -10,109
LATCH.cache buffers lru chain 469 10,656 10,187
LATCH.object queue header operation 621 15,644 15,023
STAT.table scan rows gotten 20,750 16 -20,734
STAT.session pga memory 851,968 917,504 65,536
STAT.session pga memory max 851,968 917,504 65,536
STAT.physical read total bytes 450,560 40,960 -409,600
STAT.physical read bytes 450,560 40,960 -409,600
STAT.session uga memory max 851,032 123,452 -727,580
STAT.session uga memory 851,032 0 -851,032
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
120,374 122,810 2,436 98.02%
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:02.79
rwijk@ORA10GR2> disconnect
And tkprof shows:
********************************************************************************
UPDATE CONTRACTS C1 SET C1.ENDDATE = TREAT(:B1 AS T_CONTRACTS_OBJECT).ENDDATE
WHERE
C1.ID = TREAT(:B2 AS T_CONTRACTS_OBJECT).ID AND EXISTS ( SELECT 'another
open contract from same customer' FROM CONTRACTS C2 WHERE C2.ID != C1.ID
AND C2.CUSTOMER_ID = C1.CUSTOMER_ID AND C2.ENDDATE IS NULL )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.32 0.33 0 21859 11680 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.32 0.33 0 21859 11680 5000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
UPDATE CONTRACTS_TRIGGER_APPROACH SET ENDDATE = SYSDATE
WHERE
ID BETWEEN 1 AND 10000 AND MOD(ID,2) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.68 0.84 0 2641 11676 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.68 0.85 0 2641 11676 5000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE CONTRACTS_TRIGGER_APPROACH (cr=2653 pr=0 pw=0 time=225941 us)
5000 TABLE ACCESS FULL CONTRACTS_TRIGGER_APPROACH (cr=786 pr=0 pw=0 time=35066 us)
********************************************************************************
SELECT 'open contracts'
FROM
CONTRACTS_TRIGGER_APPROACH WHERE CUSTOMER_ID = :B2 AND ID != :B1 AND ENDDATE
IS NULL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.20 0.14 0 0 0 0
Fetch 5000 0.29 0.19 0 15941 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.50 0.34 0 15941 0 5000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
5000 TABLE ACCESS BY INDEX ROWID CONTRACTS_TRIGGER_APPROACH (cr=15941 pr=0 pw=0 time=217268 us)
10000 INDEX RANGE SCAN I1 (cr=10020 pr=0 pw=0 time=92443 us)(object id 59124)
********************************************************************************
So even when you are doing a single update statement updating 5000 rows, the trigger approach is more than two times slower. The total number of latches do not differ very much, but the difference in latches on the library cache is still huge. The more business rules you have, the bigger the difference will be between the two alternatives.
The api approach is in my opinion very clear, less code, faster and more scalable. Needless to say, I now totally agree with Tom on database triggers: I hate them and I wish they didn't exist :-)
All new home grown systems will never use database triggers anymore, but I guess professionally I will have to live with them for a long time, unfortunately ...
Hi Rob,
ReplyDeleteImagine two contracts of the same customer with end_date = null.
what would happen if two sessions run the api each one for each contract at the same time? Don't you have to lock at contract level (or contract level + end_date=null)?
Hi Joaquin,
ReplyDeleteIf two contracts of the same customer would be entered, then they are executed serially because of the lock_customer procedure. So the second one will have to wait for the first one to finish (rollback/commit).
Regards,
Rob.
Hi Rob,
ReplyDeleteI can't see why your update in the API-Version
update contracts_api_approach c1
set c1.enddate = treat(p_contracts(i) as t_contracts_object).enddate
where c1.id = treat(p_contracts(i) as t_contracts_object).id
and exists
( select 'another open contract from same customer'
from contracts_api_approach c2
where c2.id != c1.id
and c2.customer_id = c1.customer_id
and c2.enddate is null
)
could prevent two sessions from closing two differnt contracts for the same customer.
Each session will lock a DIFFERENT contract-row because of the update-statement, and therefore will not block (serialize).
If both sessions commit, two contracts could be closed even it there are no other contracts left for this specific customer.
Can you help me out?
best regards
Oliver
Hi Oliver,
ReplyDeleteYou are absolutely right of course. I coded the lock_customer routine for the trigger approach, but the api approach needs it as well. The post is updated to reflect the new situation. The performance numbers have also been adjusted.
Funny how I wrote "And I used to be one of them." and still forgot about it...
Thanks for your thorough reading and thinking and for mentioning it to me!
Regards,
Rob.
Hi Rob,
ReplyDeleteThanks for the article on triggers.
I am 23 and am working as an Oracle PL/SQL developer since 6 months.
Can you suggest me sites or blogs that can help me to strengthen my database concepts.
regards,
Anand Iyer (India)
SQL server vs. Oracle server.
ReplyDeletepros and cons??