However the trigger code itself seems to be relatively lightweight - I'll have to test this. And more important, multirow DML is capable of checking the rule only once instead of "for each row". Undoubtedly RuleGen will outperform CDM RuleFrame. In the near future, I hope to be able to investigate how well RuleGen performs compared to the api approach.In the past months I have done some testing in between other activities. This post contains the results of that testing.
To be continued...
I used approximately the same table setup as from my post database triggers are evil. Before comparing RuleGen to the api approach, I decided to first test it against the database trigger approach. Two tables are used in the test, each with the same structure and same contents:
- contracts_rulegen_approach and
- contracts_trigger_approach,
and three different test scenarios:
- testing 2000 single row updates, of which 1000 succeed and the other 1000 fail,
- testing a single update statement that updates 5000 rows at once for 5000 different customers, and
- testing a single update statement that updates 5000 rows at once, for 2500 different customers so the business rule would only have to be validated 2500 times instead of 5000.
After showing Toon the first test results and trace files, with one business rule implemented on the tables, he supplied me a little patch to remove a savepoint in the code he needed in an earlier version for a bug. Another suggestion was to implement three business rules instead of one, to even out any starting costs.
The test I am presenting here, is an implementation of the following three entity rules:
- A customer must have at least one open contract
- The periods of all contracts from one customer are not allowed to overlap
- The gap between two successive contracts of a customer cannot be more than one year
Using the database trigger approach, the three rules are implemented like this:
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> create table my_rule_violation_log
2 ( id number primary key
3 , creation_date date
4 , username varchar2(30)
5 , rule varchar2(30)
6 , error_message varchar2(2000)
7 )
8 /
Tabel is aangemaakt.
rwijk@ORA10GR2> create sequence rvl_seq start with 1 increment by 1 cache 100
2 /
Reeks is aangemaakt.
rwijk@ORA10GR2> insert into contracts_trigger_approach
2 select level
3 , ceil(level/4)
4 , case mod(level-1,4)
5 when 0 then date '2005-01-01'
6 when 1 then date '2006-01-01'
7 when 2 then date '2007-01-01'
8 when 3 then date '2008-01-01'
9 end
10 , case mod(level-1,4)
11 when 0 then date '2005-12-31'
12 when 1 then date '2006-12-31'
13 when 2 then date '2007-12-31'
14 when 3 then null
15 end
16 from dual
17 connect by level <= 20000
18 /
20000 rijen zijn aangemaakt.
rwijk@ORA10GR2> alter table contracts_trigger_approach
2 add constraint ctt_t_uk1
3 unique (customer_id,startdate)
4 /
Tabel is gewijzigd.
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 , startdate contracts_trigger_approach.startdate%type
7 , enddate contracts_trigger_approach.enddate%type
8 , old_customer_id contracts_trigger_approach.customer_id%type
9 , old_startdate contracts_trigger_approach.startdate%type
10 , old_enddate contracts_trigger_approach.enddate%type
11 );
12 type tr_contracts is table of r_contracts index by pls_integer
13 ;
14 array_contracts tr_contracts;
15 empty tr_contracts
16 ;
17 procedure log
18 ( p_rule in my_rule_violation_log.rule%type
19 , p_error_message in my_rule_violation_log.error_message%type
20 );
21 procedure lock_customer (p_customer_id in contracts_trigger_approach.customer_id%type)
22 ;
23 procedure check_contract_ending_allowed
24 ( p_contract_id in contracts_trigger_approach.id%type
25 , p_customer_id in contracts_trigger_approach.customer_id%type
26 , p_old_customer_id in contracts_trigger_approach.customer_id%type
27 , p_enddate in contracts_trigger_approach.enddate%type
28 , p_old_enddate in contracts_trigger_approach.enddate%type
29 );
30 procedure check_period_overlap
31 ( p_contract_id in contracts_trigger_approach.id%type
32 , p_customer_id in contracts_trigger_approach.customer_id%type
33 , p_startdate in contracts_trigger_approach.startdate%type
34 , p_enddate in contracts_trigger_approach.enddate%type
35 , p_old_customer_id in contracts_trigger_approach.customer_id%type
36 , p_old_startdate in contracts_trigger_approach.startdate%type
37 , p_old_enddate in contracts_trigger_approach.enddate%type
38 );
39 procedure check_max_gap_1_year
40 ( p_customer_id in contracts_trigger_approach.customer_id%type
41 , p_startdate in contracts_trigger_approach.startdate%type
42 , p_enddate in contracts_trigger_approach.enddate%type
43 , p_old_customer_id in contracts_trigger_approach.customer_id%type
44 , p_old_startdate in contracts_trigger_approach.startdate%type
45 , p_old_enddate in contracts_trigger_approach.enddate%type
46 );
47 end contracts_pkg;
48 /
Package is aangemaakt.
rwijk@ORA10GR2> create package body contracts_pkg
2 as
3 procedure log
4 ( p_rule in my_rule_violation_log.rule%type
5 , p_error_message in my_rule_violation_log.error_message%type
6 )
7 is
8 pragma autonomous_transaction;
9 begin
10 insert into my_rule_violation_log
11 ( id
12 , creation_date
13 , username
14 , rule
15 , error_message
16 )
17 values
18 ( rvl_seq.nextval
19 , sysdate
20 , user
21 , p_rule
22 , p_error_message
23 );
24 commit
25 ;
26 end log
27 ;
28 procedure lock_customer (p_customer_id in contracts_trigger_approach.customer_id%type)
29 is
30 l_lock_dummy integer;
31 begin
32 l_lock_dummy := dbms_lock.request
33 ( release_on_commit => true
34 , id => dbms_utility.get_hash_value
35 ( name => 'contracts_pkg.lock_customer' || to_char(p_customer_id)
36 , base => 1
37 , hash_size => power(2,30)
38 )
39 );
40 end lock_customer
41 ;
42 function exists_gap_larger_than_1_year
43 ( p_customer_id in contracts_trigger_approach.customer_id%type
44 ) return boolean
45 is
46 l_dummy varchar2(15);
47 begin
48 select 'a gap too large'
49 into l_dummy
50 from ( select customer_id
51 , months_between
52 ( startdate
53 , lag(enddate,1,startdate) over (partition by customer_id order by startdate)
54 ) gap_in_months
55 from contracts_trigger_approach
56 where customer_id = p_customer_id
57 )
58 group by customer_id
59 having max(gap_in_months) > 12
60 ;
61 return true
62 ;
63 exception
64 when no_data_found then
65 return false;
66 end exists_gap_larger_than_1_year
67 ;
68 procedure check_max_gap_1_year
69 ( p_customer_id in contracts_trigger_approach.customer_id%type
70 , p_startdate in contracts_trigger_approach.startdate%type
71 , p_enddate in contracts_trigger_approach.enddate%type
72 , p_old_customer_id in contracts_trigger_approach.customer_id%type
73 , p_old_startdate in contracts_trigger_approach.startdate%type
74 , p_old_enddate in contracts_trigger_approach.enddate%type
75 )
76 is
77 begin
78 if p_customer_id != p_old_customer_id
79 or p_startdate != p_old_startdate
80 or nvl(p_enddate,date '-4712-01-01') != nvl(p_old_enddate,date '-4712-01-01')
81 then
82 if exists_gap_larger_than_1_year(p_customer_id)
83 then
84 raise_application_error
85 ( -20000
86 , 'A customer''s contracts cannot have a gap larger than one year.'
87 );
88 end if;
89 end if
90 ;
91 end check_max_gap_1_year
92 ;
93 function exists_overlapping_contracts
94 ( p_contract_id in contracts_trigger_approach.id%type
95 , p_customer_id in contracts_trigger_approach.customer_id%type
96 , p_startdate in contracts_trigger_approach.startdate%type
97 , p_enddate in contracts_trigger_approach.enddate%type
98 ) return boolean
99 is
100 l_dummy varchar2(21);
101 begin
102 select 'overlapping contracts'
103 into l_dummy
104 from contracts_trigger_approach
105 where customer_id = p_customer_id
106 and id != p_contract_id
107 and startdate <= nvl(p_enddate,startdate+1)
108 and nvl(enddate,p_startdate+1) >= p_startdate
109 ;
110 return true;
111 exception
112 when no_data_found then
113 return false;
114 when too_many_rows then
115 return true;
116 end exists_overlapping_contracts
117 ;
118 procedure check_period_overlap
119 ( p_contract_id in contracts_trigger_approach.id%type
120 , p_customer_id in contracts_trigger_approach.customer_id%type
121 , p_startdate in contracts_trigger_approach.startdate%type
122 , p_enddate in contracts_trigger_approach.enddate%type
123 , p_old_customer_id in contracts_trigger_approach.customer_id%type
124 , p_old_startdate in contracts_trigger_approach.startdate%type
125 , p_old_enddate in contracts_trigger_approach.enddate%type
126 )
127 is
128 begin
129 if p_customer_id != p_old_customer_id
130 or p_startdate != p_old_startdate
131 or nvl(p_enddate,date '-4712-01-01') != nvl(p_old_enddate,date '-4712-01-01')
132 then
133 if exists_overlapping_contracts
134 ( p_contract_id
135 , p_customer_id
136 , p_startdate
137 , p_enddate
138 )
139 then
140 raise_application_error
141 ( -20001
142 , 'A customer''s contracts are not allowed to overlap with each other.'
143 );
144 end if;
145 end if
146 ;
147 end check_period_overlap
148 ;
149 function exists_other_open_contracts
150 ( p_contract_id in contracts_trigger_approach.id%type
151 , p_customer_id in contracts_trigger_approach.customer_id%type
152 ) return boolean
153 is
154 l_dummy varchar2(14);
155 begin
156 select 'open contracts'
157 into l_dummy
158 from contracts_trigger_approach
159 where customer_id = p_customer_id
160 and id != p_contract_id
161 and enddate is null
162 ;
163 return true;
164 exception
165 when no_data_found then
166 return false;
167 when too_many_rows then
168 return true;
169 end exists_other_open_contracts
170 ;
171 procedure check_contract_ending_allowed
172 ( p_contract_id in contracts_trigger_approach.id%type
173 , p_customer_id in contracts_trigger_approach.customer_id%type
174 , p_old_customer_id in contracts_trigger_approach.customer_id%type
175 , p_enddate in contracts_trigger_approach.enddate%type
176 , p_old_enddate in contracts_trigger_approach.enddate%type
177 )
178 is
179 begin
180 if p_customer_id != p_old_customer_id
181 or nvl(p_enddate,date '-4712-01-01') != nvl(p_old_enddate,date '-4712-01-01')
182 then
183 if not exists_other_open_contracts (p_contract_id, p_customer_id)
184 then
185 raise_application_error
186 ( -20002
187 , 'Cannot end a customer''s last open contract.'
188 );
189 end if;
190 end if
191 ;
192 end check_contract_ending_allowed
193 ;
194 end contracts_pkg;
195 /
Package-body is aangemaakt.
rwijk@ORA10GR2> create or replace trigger contracts_bsu
2 before update 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 on contracts_trigger_approach
3 for each row
4 declare
5 cn_index constant pls_integer := contracts_pkg.array_contracts.count + 1;
6 begin
7 contracts_pkg.array_contracts(cn_index).id := :new.id;
8 contracts_pkg.array_contracts(cn_index).customer_id := :new.customer_id;
9 contracts_pkg.array_contracts(cn_index).startdate := :new.startdate;
10 contracts_pkg.array_contracts(cn_index).enddate := :new.enddate;
11 contracts_pkg.array_contracts(cn_index).old_customer_id := :old.customer_id;
12 contracts_pkg.array_contracts(cn_index).old_startdate := :old.startdate;
13 contracts_pkg.array_contracts(cn_index).old_enddate := :old.enddate;
14 end;
15 /
Trigger is aangemaakt.
rwijk@ORA10GR2> create or replace trigger contracts_asu
2 after update on contracts_trigger_approach
3 begin
4 for i in 1 .. contracts_pkg.array_contracts.count
5 loop
6 contracts_pkg.lock_customer(contracts_pkg.array_contracts(i).customer_id)
7 ;
8 contracts_pkg.check_max_gap_1_year
9 ( contracts_pkg.array_contracts(i).customer_id
10 , contracts_pkg.array_contracts(i).startdate
11 , contracts_pkg.array_contracts(i).enddate
12 , contracts_pkg.array_contracts(i).old_customer_id
13 , contracts_pkg.array_contracts(i).old_startdate
14 , contracts_pkg.array_contracts(i).old_enddate
15 );
16 contracts_pkg.check_period_overlap
17 ( contracts_pkg.array_contracts(i).id
18 , contracts_pkg.array_contracts(i).customer_id
19 , contracts_pkg.array_contracts(i).startdate
20 , contracts_pkg.array_contracts(i).enddate
21 , contracts_pkg.array_contracts(i).old_customer_id
22 , contracts_pkg.array_contracts(i).old_startdate
23 , contracts_pkg.array_contracts(i).old_enddate
24 );
25 contracts_pkg.check_contract_ending_allowed
26 ( contracts_pkg.array_contracts(i).id
27 , contracts_pkg.array_contracts(i).customer_id
28 , contracts_pkg.array_contracts(i).old_customer_id
29 , contracts_pkg.array_contracts(i).enddate
30 , contracts_pkg.array_contracts(i).old_enddate
31 );
32 end loop;
33 end;
34 /
Trigger is aangemaakt.
rwijk@ORA10GR2> select *
2 from contracts_trigger_approach
3 where id between 1233 and 1236
4 order by id
5 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 309 01-01-2005 00:00:00 31-12-2005 00:00:00
1234 309 01-01-2006 00:00:00 31-12-2006 00:00:00
1235 309 01-01-2007 00:00:00 31-12-2007 00:00:00
1236 309 01-01-2008 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> update contracts_trigger_approach
2 set startdate = date '2002-01-01'
3 , enddate = date '2002-12-31'
4 where id = 1236
5 /
update contracts_trigger_approach
*
FOUT in regel 1:
.ORA-20000: A customer's contracts cannot have a gap larger than one year.
ORA-06512: in "RWIJK.CONTRACTS_PKG", regel 84
ORA-06512: in "RWIJK.CONTRACTS_ASU", regel 6
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.CONTRACTS_ASU'.
rwijk@ORA10GR2> update contracts_trigger_approach
2 set enddate = date '2009-01-01'
3 where id = 1235
4 /
update contracts_trigger_approach
*
FOUT in regel 1:
.ORA-20001: A customer's contracts are not allowed to overlap with each other.
ORA-06512: in "RWIJK.CONTRACTS_PKG", regel 140
ORA-06512: in "RWIJK.CONTRACTS_ASU", regel 14
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.CONTRACTS_ASU'.
rwijk@ORA10GR2> update contracts_trigger_approach
2 set enddate = date '2009-01-01'
3 where id = 1236
4 /
update contracts_trigger_approach
*
FOUT in regel 1:
.ORA-20002: Cannot end a customer's last open contract.
ORA-06512: in "RWIJK.CONTRACTS_PKG", regel 185
ORA-06512: in "RWIJK.CONTRACTS_ASU", regel 23
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.CONTRACTS_ASU'.
rwijk@ORA10GR2> select *
2 from contracts_trigger_approach
3 where id between 1233 and 1236
4 order by id
5 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 309 01-01-2005 00:00:00 31-12-2005 00:00:00
1234 309 01-01-2006 00:00:00 31-12-2006 00:00:00
1235 309 01-01-2007 00:00:00 31-12-2007 00:00:00
1236 309 01-01-2008 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
Note that I also implemented a small logging procedure to be able to have an even more fair comparison, since RuleGen also inserts violations in a log table. When doing the test with 2000 updates out of which 1000 fail, the failed rows will be logged.
Now to RuleGen. In RuleGen those three business rules are implemented by specifying a "When" and a "How". Here also only the update part of the When was implemented.
The implementation in RuleGen is like this:
rwijk@ORA10GR2> create table contracts_rulegen_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_rulegen_approach
2 select level
3 , ceil(level/4)
4 , case mod(level-1,4)
5 when 0 then date '2005-01-01'
6 when 1 then date '2006-01-01'
7 when 2 then date '2007-01-01'
8 when 3 then date '2008-01-01'
9 end
10 , case mod(level-1,4)
11 when 0 then date '2005-12-31'
12 when 1 then date '2006-12-31'
13 when 2 then date '2007-12-31'
14 when 3 then null
15 end
16 from dual
17 connect by level <= 20000
18 /
20000 rijen zijn aangemaakt.
rwijk@ORA10GR2> alter table contracts_rulegen_approach
2 add constraint ctt_r_uk1
3 unique (customer_id,startdate)
4 /
Tabel is gewijzigd.
rwijk@ORA10GR2> exec dbms_stats.gather_table_stats(user,'contracts_rulegen_approach',cascade=>true)
PL/SQL-procedure is geslaagd.
And then in the APEX frontend of RuleGen:
Rule1: The gap between two successive contracts of a customer cannot be more than one year
When:
select distinct new_customer_id as customer_id
from updated_rows
where ( updated_enddate = 'TRUE'
or updated_customer_id = 'TRUE'
or updated_startdate = 'TRUE'
)
How:
select 'The contracts of customer ' || to_char(customer_id) || ' cannot have a gap larger than 1 year.' as msg
from ( select customer_id
, months_between
( startdate
, lag(enddate,1,startdate) over (partition by customer_id order by startdate)
) gap_in_months
from contracts_rulegen_approach
where customer_id = p_customer_id
)
where customer_id = p_customer_id
group by customer_id
having max(gap_in_months) > 12
Rule2: The periods of all contracts from one customer are not allowed to overlap
When:
select distinct new_customer_id as customer_id
from updated_rows
where ( updated_enddate = 'TRUE'
or updated_customer_id = 'TRUE'
or updated_startdate = 'TRUE'
)
How:
select distinct 'Customer ' || to_char(p_customer_id) || ' has overlapping contracts.' as msg
from contracts_rulegen_approach c1
where c1.customer_id = p_customer_id
and exists
( select 'overlapping contract'
from contracts_rulegen_approach c2
where c2.id != c1.id
and c2.customer_id = c1.customer_id
and c2.startdate <= nvl(c1.enddate,c2.startdate+1)
and nvl(c2.enddate,c1.startdate+1) >= c1.startdate
)
Rule3: A customer must have at least one open contract
When:
select distinct old_customer_id as customer_id
from updated_rows
where ( updated_enddate = 'TRUE'
or updated_customer_id = 'TRUE'
)
How:
select 'Customer ' || to_char(p_customer_id) || ' must have at least one open contract.' as msg
from contracts_rulegen_approach
where customer_id = p_customer_id
group by customer_id
having count(case when enddate is null then 1 end) = 0
Note that I relaxed the "When" query of rule 3, in RuleGen, but also in the guard of the IF-statement in the trigger approach. This is done to have the validation code run in all test cases. And since this is a performance test, and not a test how to define business rules the best way, it doesn't matter.
After having specified the "When" and "How" for all rules, it's a matter of just pressing a button in RuleGen, and the rules are generated on the table contracts_rulegen_approach in this case. And this is what I consider one of the strongest points of RuleGen: the ease of implementation. It forces you to think through all the events that would require a validation, the "When", and a single SQL that expresses "How" to validate. Thus you focus only on the essentials of the business rule, and all the tedious standard coding is done for you.
Two database triggers are created in the process:
- An "after each row insert or update or delete" database trigger with this call:
sp_rg_rw_ctt_maintain_te
This maintains the transition effect, or in other words, the rows that should be validated because of the transaction.
(:old.rowid
,:new.rowid
,:old.CUSTOMER_ID
,:new.CUSTOMER_ID
,:old.STARTDATE
,:new.STARTDATE
,:old.ENDDATE
,:new.ENDDATE
,INSERTING
,UPDATING
,DELETING
); - An "after statement insert or update or delete" database trigger with this call:
sp_rg_eval_rules(INSERTING,UPDATING,DELETING,'RWIJK','CONTRACTS_RULEGEN_APPROACH');
These procedures are wrapped, understandably. But a consequence of that is that debugging is a bit harder: when you make a typo in a query, you don't get to see where the error is. You are left with doing an extra visual check and if that won't help you'd have to take out the SQL statements and run them separately. So debugging is a little harder, although most of the time the "When" and the "How" statements are quite easy. And, Toon has announced that version 2.1 (expected in July/August 2008) will contain syntax validation integrated in the front-end for the "How" and "When" queries.
Let's test if the rules work:
rwijk@ORA10GR2> select *
2 from contracts_rulegen_approach
3 where id between 1233 and 1236
4 order by id
5 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 309 01-01-2005 00:00:00 31-12-2005 00:00:00
1234 309 01-01-2006 00:00:00 31-12-2006 00:00:00
1235 309 01-01-2007 00:00:00 31-12-2007 00:00:00
1236 309 01-01-2008 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> update contracts_rulegen_approach
2 set startdate = date '2002-01-01'
3 , enddate = date '2002-12-31'
4 where id = 1236
5 /
update contracts_rulegen_approach
*
FOUT in regel 1:
.ORA-20999: (CTT_RULE1) The contracts of customer 309 cannot have a gap larger than 1 year.
ORA-06512: in "SYSRG.X_RG_SYS_RTE", regel 24
ORA-06512: in "SYSRG.X_RG_SYS_RTE", regel 74
ORA-06512: in "SYSRG.SP_RG_EVAL_RULES", regel 22
ORA-06512: in "RWIJK.RG$_CTT_AIUDS", regel 5
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.RG$_CTT_AIUDS'.
rwijk@ORA10GR2> update contracts_rulegen_approach
2 set enddate = date '2009-01-01'
3 where id = 1235
4 /
update contracts_rulegen_approach
*
FOUT in regel 1:
.ORA-20999: (CTT_RULE2) Customer 309 has overlapping contracts.
ORA-06512: in "SYSRG.X_RG_SYS_RTE", regel 24
ORA-06512: in "SYSRG.X_RG_SYS_RTE", regel 74
ORA-06512: in "SYSRG.SP_RG_EVAL_RULES", regel 22
ORA-06512: in "RWIJK.RG$_CTT_AIUDS", regel 5
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.RG$_CTT_AIUDS'.
rwijk@ORA10GR2> update contracts_rulegen_approach
2 set enddate = date '2009-01-01'
3 where id = 1236
4 /
update contracts_rulegen_approach
*
FOUT in regel 1:
.ORA-20999: (CTT_RULE3) Customer 309 must have at least one open contract.
ORA-06512: in "SYSRG.X_RG_SYS_RTE", regel 24
ORA-06512: in "SYSRG.X_RG_SYS_RTE", regel 74
ORA-06512: in "SYSRG.SP_RG_EVAL_RULES", regel 22
ORA-06512: in "RWIJK.RG$_CTT_AIUDS", regel 5
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.RG$_CTT_AIUDS'.
rwijk@ORA10GR2> select *
2 from contracts_rulegen_approach
3 where id between 1233 and 1236
4 order by id
5 /
ID CUSTOMER_ID STARTDATE ENDDATE
---------- ----------- ------------------- -------------------
1233 309 01-01-2005 00:00:00 31-12-2005 00:00:00
1234 309 01-01-2006 00:00:00 31-12-2006 00:00:00
1235 309 01-01-2007 00:00:00 31-12-2007 00:00:00
1236 309 01-01-2008 00:00:00
4 rijen zijn geselecteerd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
Ok, so now the tables are setup and the business rules are implemented in both scenarios. Let's run the first test, 2000 times updating a single row, 1000 updates succeeding and the other 1000 failing. The errors are logged and then ignored. The test is setup in such a way that all three business rules are validated each time, and the failures are because it fails on the third business rule. This is the output of that test:
rwijk@ORA10GR2> alter session set events '10046 trace name context forever, level 12'
2 /
Sessie is gewijzigd.
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,-20002);
8 begin
9 update contracts_trigger_approach
10 set enddate = decode(enddate,null,date '2008-12-31',date '2006-07-01')
11 where id = 6 * i
12 ;
13 exception
14 when e then
15 contracts_pkg.log
16 ( 'myrule'
17 , 'Customer ' || to_char(i) || ' must always have at least one open contract.'
18 );
19 end;
20 end loop;
21 runstats_pkg.rs_middle;
22 for i in 1..2000
23 loop
24 declare
25 e exception;
26 pragma exception_init(e,-20999);
27 begin
28 update contracts_rulegen_approach
29 set enddate = decode(enddate,null,date '2008-12-31',date '2006-07-01')
30 where id = 6 * i
31 ;
32 exception
33 when e then null;
34 end;
35 end loop;
36 runstats_pkg.rs_stop(100);
37 end;
38 /
Run1 draaide in 1653 hsecs
Run2 draaide in 4058 hsecs
Run1 draaide in 40,73% van de tijd
Naam Run1 Run2 Verschil
STAT.redo ordering marks 29 133 104
STAT.rows fetched via callback 2,006 2,118 112
LATCH.JS queue state obj latch 108 288 180
STAT.deferred (CURRENT) block cleanout application 1,515 1,699 184
STAT.commit cleanouts successfully completed 2,018 2,204 186
LATCH.mostly latch-free SCN 1,005 1,198 193
LATCH.Consistent RBA 1,005 1,198 193
LATCH.lgwr LWN SCN 1,005 1,198 193
STAT.messages sent 1,001 1,198 197
STAT.commit cleanouts 2,018 2,220 202
STAT.IMU Redo allocation size 18,844 18,636 -208
STAT.opened cursors cumulative 116 339 223
STAT.index fetch by key 2,042 2,354 312
LATCH.dml lock allocation 2,039 2,402 363
LATCH.sequence cache 3,020 3,400 380
STAT.enqueue releases 2,078 2,653 575
LATCH.redo allocation 5,029 5,610 581
LATCH.undo global data 5,064 5,655 591
LATCH.redo writing 3,033 3,639 606
LATCH.simulator lru latch 2,090 2,736 646
LATCH.simulator hash latch 2,132 2,792 660
STAT.consistent gets - examination 12,144 12,807 663
LATCH.messages 3,149 4,044 895
LATCH.In memory undo latch 5,999 6,996 997
STAT.switch current to new buffer 0 1,000 1,000
STAT.free buffer requested 259 1,400 1,141
LATCH.checkpoint queue latch 116 1,294 1,178
STAT.calls to kcmgas 1,048 2,344 1,296
STAT.rollback changes - undo records applied 1,000 3,000 2,000
LATCH.cache buffers lru chain 466 2,642 2,176
LATCH.object queue header operation 450 2,731 2,281
STAT.CPU used by this session 1,613 4,011 2,398
STAT.recursive cpu usage 1,257 3,682 2,425
STAT.sorts (memory) 2,026 6,038 4,012
STAT.enqueue requests 4,080 8,655 4,575
STAT.IMU undo allocation size 691,224 696,004 4,780
STAT.index scans kdiixs1 6,068 11,111 5,043
LATCH.enqueue hash chains 6,457 11,958 5,501
STAT.sorts (rows) 10,471 16,442 5,971
LATCH.PL/SQL warning settings 3 6,000 5,997
STAT.session cursor cache hits 92 6,276 6,184
STAT.parse count (total) 34 6,314 6,280
STAT.shared hash latch upgrades - no wait 6,125 13,099 6,974
STAT.table scan blocks gotten 7 7,003 6,996
STAT.table scans (short tables) 3 7,002 6,999
STAT.session uga memory 0 7,584 7,584
STAT.consistent changes 38 8,129 8,091
STAT.redo entries 5,511 13,905 8,394
LATCH.enqueues 4,071 12,685 8,614
LATCH.shared pool 3,369 12,713 9,344
STAT.db block gets 13,950 29,765 15,815
STAT.db block gets from cache 13,950 29,765 15,815
LATCH.library cache lock 504 16,758 16,254
LATCH.SQL memory manager workarea list latch 4,416 20,950 16,534
STAT.db block changes 13,543 30,485 16,942
STAT.no work - consistent read gets 6,315 26,369 20,054
STAT.buffer is not pinned count 16,347 36,710 20,363
STAT.table fetch by rowid 24,103 46,289 22,186
STAT.buffer is pinned count 31,951 55,990 24,039
STAT.execute count 9,112 34,359 25,247
STAT.recursive calls 18,548 48,083 29,535
STAT.calls to get snapshot scn: kcmgss 10,149 41,657 31,508
STAT.consistent gets from cache 24,559 64,308 39,749
STAT.consistent gets 24,559 64,308 39,749
LATCH.session allocation 2,783 58,150 55,367
STAT.session logical reads 38,509 94,073 55,564
STAT.session uga memory max 63,600 7,584 -56,016
STAT.physical read bytes 1,105,920 1,187,840 81,920
STAT.physical read total bytes 1,105,920 1,187,840 81,920
LATCH.cache buffers chains 99,292 238,405 139,113
STAT.session pga memory max 196,608 0 -196,608
LATCH.row cache objects 16,071 245,858 229,787
LATCH.library cache pin 40,704 278,257 237,553
LATCH.library cache 52,348 367,256 314,908
STAT.undo change vector size 401,376 1,208,564 807,188
STAT.redo size 1,636,112 3,133,848 1,497,736
STAT.table scan rows gotten 965 3,506,515 3,505,550
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
266,179 1,317,418 1,051,239 20.20%
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
rwijk@ORA10GR2> disconnect
So for single row updates, RuleGen is approximately two-and-a-half times slower than a standard trigger based approach. This can be accounted to the overhead of the framework, as can be seen in the next tkprof snippet:
********************************************************************************
UPDATE CONTRACTS_TRIGGER_APPROACH SET ENDDATE = DECODE(ENDDATE,NULL,DATE
'2008-12-31',DATE '2006-07-01')
WHERE
ID = 6 * :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 6.73 7.13 65 6000 2678 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 6.73 7.13 65 6000 2678 1000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2000 UPDATE CONTRACTS_TRIGGER_APPROACH (cr=6000 pr=65 pw=0 time=361732 us)
2000 TABLE ACCESS BY INDEX ROWID CONTRACTS_TRIGGER_APPROACH (cr=6000 pr=64 pw=0 time=160047 us)
2000 INDEX UNIQUE SCAN SYS_C0010694 (cr=4000 pr=24 pw=0 time=71647 us)(object id 64953)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 10 0.02 0.03
db file sequential read 16 0.02 0.04
********************************************************************************
SELECT 'a gap too large'
FROM
( SELECT CUSTOMER_ID , MONTHS_BETWEEN ( STARTDATE , LAG(ENDDATE,1,STARTDATE)
OVER (PARTITION BY CUSTOMER_ID ORDER BY STARTDATE) ) GAP_IN_MONTHS FROM
CONTRACTS_TRIGGER_APPROACH WHERE CUSTOMER_ID = :B1 ) GROUP BY CUSTOMER_ID
HAVING MAX(GAP_IN_MONTHS) > 12
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2000 0.32 0.31 0 0 0 0
Fetch 2000 0.51 0.47 48 6047 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 0.84 0.78 48 6047 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 7 0.01 0.02
db file sequential read 8 0.03 0.08
********************************************************************************
SELECT 'overlapping contracts'
FROM
CONTRACTS_TRIGGER_APPROACH WHERE CUSTOMER_ID = :B4 AND ID != :B3 AND
STARTDATE <= NVL(:B2 ,STARTDATE+1) AND NVL(ENDDATE,:B1 +1) >= :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2000 0.64 0.65 0 0 0 0
Fetch 2000 0.03 0.06 0 6032 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 0.67 0.71 0 6032 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 2)
********************************************************************************
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 2000 0.40 0.32 0 0 0 0
Fetch 2000 0.03 0.08 0 6047 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4001 0.43 0.40 0 6047 0 1000
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
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID CONTRACTS_TRIGGER_APPROACH (cr=6047 pr=0 pw=0 time=91468 us)
8000 INDEX RANGE SCAN CTT_T_UK1 (cr=4024 pr=0 pw=0 time=58573 us)(object id 64956)
********************************************************************************
INSERT INTO MY_RULE_VIOLATION_LOG ( ID , CREATION_DATE , USERNAME , RULE ,
ERROR_MESSAGE )
VALUES
( RVL_SEQ.NEXTVAL , SYSDATE , USER , :B2 , :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.32 0.36 13 21 5200 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.32 0.36 13 21 5200 1000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000 SEQUENCE RVL_SEQ (cr=62 pr=1 pw=0 time=46672 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 9 0.02 0.04
db file scattered read 1 0.00 0.00
********************************************************************************
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.09 0.08 0 0 1000 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.09 0.08 0 0 1000 0
Misses in library cache during parse: 0
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
INSERT INTO RUN_STATS SELECT 'after 1', STATS.* FROM STATS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.01 0 7 27 729
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.02 0 7 27 729
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
729 VIEW STATS (cr=0 pr=0 pw=0 time=18315 us)
729 UNION-ALL (cr=0 pr=0 pw=0 time=13935 us)
347 HASH JOIN (cr=0 pr=0 pw=0 time=7104 us)
347 FIXED TABLE FULL X$KSUMYSTA (cr=0 pr=0 pw=0 time=1777 us)
1 FIXED TABLE FULL X$KSUSGIF (cr=0 pr=0 pw=0 time=16 us)
347 FIXED TABLE FULL X$KSUSD (cr=0 pr=0 pw=0 time=1412 us)
382 HASH JOIN (cr=0 pr=0 pw=0 time=18766 us)
382 FIXED TABLE FULL X$KSLLD (cr=0 pr=0 pw=0 time=1542 us)
382 VIEW (cr=0 pr=0 pw=0 time=14378 us)
382 SORT GROUP BY (cr=0 pr=0 pw=0 time=12060 us)
2334 FIXED TABLE FULL X$KSLLT (cr=0 pr=0 pw=0 time=11683 us)
********************************************************************************
UPDATE CONTRACTS_RULEGEN_APPROACH SET ENDDATE = DECODE(ENDDATE,NULL,DATE
'2008-12-31',DATE '2006-07-01')
WHERE
ID = 6 * :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 12.89 12.51 65 6000 4626 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 12.89 12.51 65 6000 4626 1000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2000 UPDATE CONTRACTS_RULEGEN_APPROACH (cr=6041 pr=65 pw=0 time=1435548 us)
2000 TABLE ACCESS BY INDEX ROWID CONTRACTS_RULEGEN_APPROACH (cr=6000 pr=64 pw=0 time=123280 us)
2000 INDEX UNIQUE SCAN SYS_C0010698 (cr=4000 pr=24 pw=0 time=51603 us)(object id 64962)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 10 0.00 0.00
db file sequential read 16 0.01 0.01
********************************************************************************
INSERT INTO RG$_TE
VALUES
(:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2000 0.76 0.77 0 41 6144 2000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2000 0.76 0.77 0 41 6144 2000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
********************************************************************************
SELECT OWNER_ALIAS ,RULE ,DECODE(SESSION_CHECK_MOMENT,'DEFERRED','Y','N') AS
DEFERRED
FROM
V_RG_TABLE_RULE WHERE TABLE_NAME = :B2 AND OWNER = :B1 AND
SESSION_CHECK_STATUS = 'ENABLED' ORDER BY DLEVEL ,TOTAL_INVOLVED_COLUMNS ,
RULE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2000 0.46 0.55 0 0 0 0
Fetch 2000 0.48 0.45 0 4000 0 6000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 0.95 1.01 0 4000 0 6000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
********************************************************************************
begin x_RW_CTT_RULE1.check_rule(:b1,:b2); end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2000 0.07 0.07 0 0 0 0
Execute 2000 2.92 3.05 0 0 0 2000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 3.00 3.13 0 0 0 2000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
********************************************************************************
SELECT VALUE(T) AS TE
FROM
RG$_TE T
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 6000 0.85 0.86 0 0 0 0
Fetch 6000 1.03 0.84 0 18000 0 6000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12000 1.89 1.71 0 18000 0 6000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 4)
********************************************************************************
SELECT DISTINCT NEW_CUSTOMER_ID AS CUSTOMER_ID
FROM
V_RW_CTT_UTE WHERE ( UPDATED_ENDDATE = 'TRUE' OR UPDATED_CUSTOMER_ID =
'TRUE' OR UPDATED_STARTDATE = 'TRUE' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 4000 1.53 1.59 0 0 0 0
Fetch 4000 0.53 0.63 0 4000 0 4000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8000 2.06 2.22 0 4000 0 4000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 3)
********************************************************************************
SELECT 'The contracts of customer ' || TO_CHAR(CUSTOMER_ID) || ' cannot have
a gap larger than 1 year.' AS MSG
FROM
( SELECT CUSTOMER_ID , MONTHS_BETWEEN ( STARTDATE , LAG(ENDDATE,1,STARTDATE)
OVER (PARTITION BY CUSTOMER_ID ORDER BY STARTDATE) ) GAP_IN_MONTHS FROM
CONTRACTS_RULEGEN_APPROACH WHERE CUSTOMER_ID = :B1 ) WHERE CUSTOMER_ID =
:B1 GROUP BY CUSTOMER_ID HAVING MAX(GAP_IN_MONTHS) > 12
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2000 0.54 0.50 0 0 0 0
Fetch 2000 0.20 0.34 48 6047 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 0.75 0.84 48 6047 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 7 0.01 0.03
db file sequential read 8 0.01 0.08
********************************************************************************
begin x_RW_CTT_RULE2.check_rule(:b1,:b2); end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2000 0.07 0.08 0 0 0 0
Execute 2000 2.67 3.02 0 0 0 2000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 2.75 3.10 0 0 0 2000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
********************************************************************************
SELECT DISTINCT 'Customer ' || TO_CHAR(:B1 ) || ' has overlapping contracts.'
AS MSG
FROM
CONTRACTS_RULEGEN_APPROACH C1 WHERE C1.CUSTOMER_ID = :B1 AND EXISTS ( SELECT
'overlapping contract' FROM CONTRACTS_RULEGEN_APPROACH C2 WHERE C2.ID !=
C1.ID AND C2.CUSTOMER_ID = C1.CUSTOMER_ID AND C2.STARTDATE <=
NVL(C1.ENDDATE,C2.STARTDATE+1) AND NVL(C2.ENDDATE,C1.STARTDATE+1) >=
C1.STARTDATE )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2000 0.71 0.53 0 0 0 0
Fetch 2000 0.59 0.52 0 12094 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 1.31 1.06 0 12094 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 3)
********************************************************************************
begin x_RW_CTT_RULE3.check_rule(:b1,:b2); end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2000 0.04 0.08 0 0 0 0
Execute 2000 2.96 3.11 0 18 0 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 3.01 3.19 0 18 0 1000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
********************************************************************************
SELECT DISTINCT OLD_CUSTOMER_ID AS CUSTOMER_ID
FROM
V_RW_CTT_UTE WHERE ( UPDATED_ENDDATE = 'TRUE' OR UPDATED_CUSTOMER_ID =
'TRUE' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 222 0 0
Execute 2000 1.00 0.93 0 0 0 0
Fetch 2000 0.46 0.42 0 2000 0 2000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4001 1.46 1.36 0 2222 0 2000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
2000 HASH UNIQUE (cr=8000 pr=0 pw=0 time=1606315 us)
2000 NESTED LOOPS (cr=8000 pr=0 pw=0 time=1273181 us)
2000 COLLECTION ITERATOR PICKLER FETCH F_RG$_TE (cr=6000 pr=0 pw=0 time=1174305 us)
2000 TABLE ACCESS BY USER ROWID CONTRACTS_RULEGEN_APPROACH (cr=2000 pr=0 pw=0 time=47244 us)
********************************************************************************
SELECT LOCK_TIMEOUT
FROM
RG_RULE R WHERE RULE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 3)
********************************************************************************
SELECT 'Customer ' || TO_CHAR(:B1 ) || ' must have at least one open
contract.' AS MSG
FROM
CONTRACTS_RULEGEN_APPROACH WHERE CUSTOMER_ID = :B1 GROUP BY CUSTOMER_ID
HAVING COUNT(CASE WHEN ENDDATE IS NULL THEN 1 END) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.42 0.52 0 0 0 0
Fetch 2000 0.17 0.19 0 6047 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4001 0.59 0.72 0 6047 0 1000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
1000 FILTER (cr=6047 pr=0 pw=0 time=249137 us)
2000 SORT GROUP BY NOSORT (cr=6047 pr=0 pw=0 time=213191 us)
8000 TABLE ACCESS BY INDEX ROWID CONTRACTS_RULEGEN_APPROACH (cr=6047 pr=0 pw=0 time=168659 us)
8000 INDEX RANGE SCAN CTT_R_UK1 (cr=4024 pr=0 pw=0 time=87563 us)(object id 64963)
********************************************************************************
SELECT OWNER_ALIAS ,RULE ,DECODE(SESSION_CHECK_MOMENT,'DEFERRED','Y','N') AS
DEFERRED
FROM
V_RG_DATABASE_RULE WHERE TABLE_NAME = :B2 AND OWNER = :B1 AND
SESSION_CHECK_STATUS = 'ENABLED' ORDER BY DLEVEL ,TOTAL_INVOLVED_TABLES ,
TOTAL_INVOLVED_COLUMNS ,RULE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.28 0.31 0 0 0 0
Fetch 1000 0.00 0.05 1 1000 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.28 0.37 1 1000 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=1000 pr=1 pw=0 time=80658 us)
0 MAT_VIEW ACCESS BY INDEX ROWID MV_RG_DATABASE_RULE (cr=1000 pr=1 pw=0 time=51555 us)
0 INDEX RANGE SCAN MV_CDR_I1 (cr=1000 pr=1 pw=0 time=37605 us)(object id 62155)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.01 0.01
********************************************************************************
SELECT OWNER_ALIAS ,RULE ,DECODE(SESSION_CHECK_MOMENT,'DEFERRED','Y','N') AS
DEFERRED
FROM
V_RG_TRANSACTION_RULE WHERE TABLE_NAME = :B2 AND OWNER = :B1 AND
SESSION_CHECK_STATUS = 'ENABLED' ORDER BY DLEVEL ,TOTAL_INVOLVED_TABLES ,
TOTAL_INVOLVED_COLUMNS ,RULE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.23 0.25 0 0 0 0
Fetch 1000 0.03 0.03 1 1000 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.26 0.29 1 1000 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=1000 pr=1 pw=0 time=55211 us)
0 MAT_VIEW ACCESS BY INDEX ROWID MV_RG_TRANSACTION_RULE (cr=1000 pr=1 pw=0 time=35473 us)
0 INDEX SKIP SCAN MV_CXR_PK (cr=1000 pr=1 pw=0 time=22301 us)(object id 62159)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
********************************************************************************
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ NO_PARALLEL("RG$_TE") FULL("RG$_TE") NO_PARALLEL_INDEX("RG$_TE")
*/ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "RG$_TE" "RG$_TE") SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=87 us)
1 TABLE ACCESS FULL RG$_TE (cr=3 pr=0 pw=0 time=55 us)
********************************************************************************
DELETE FROM RG$_TE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1000 0.23 0.25 0 3001 3234 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.23 0.25 0 3002 3234 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE RG$_TE (cr=3005 pr=0 pw=0 time=197754 us)
1000 TABLE ACCESS FULL RG$_TE (cr=3000 pr=0 pw=0 time=55642 us)
********************************************************************************
INSERT INTO RG_RULE_VIOLATION_LOG(SEQ,LOG_DTD,USERNAME,RULE,ERRMSG)
VALUES
(RG_RULE_VIOLATION_LOG_SEQ.NEXTVAL,SYSDATE,USER,:B2 ,:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1000 0.51 0.42 21 4 5323 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1000 0.51 0.42 21 4 5323 1000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 6 0.01 0.03
db file scattered read 4 0.00 0.00
********************************************************************************
Now let's see if the RuleGen framework is more efficient when doing a larger multi row update. The second test will do a single update statement updating 5000 rows successfully. This is the output from this test:
rwijk@ORA10GR2> alter session set events '10046 trace name context forever, level 12'
2 /
Sessie is gewijzigd.
rwijk@ORA10GR2> begin
2 runstats_pkg.rs_start
3 ;
4 update contracts_trigger_approach
5 set enddate = date '2007-07-01'
6 where enddate = date '2007-12-31'
7 ;
8 runstats_pkg.rs_middle
9 ;
10 update contracts_rulegen_approach
11 set enddate = date '2007-07-01'
12 where enddate = date '2007-12-31'
13 ;
14 runstats_pkg.rs_stop(1000)
15 ;
16 end;
17 /
Run1 draaide in 1502 hsecs
Run2 draaide in 4366 hsecs
Run1 draaide in 34,4% van de tijd
Naam Run1 Run2 Verschil
LATCH.object queue header operation 339 1,417 1,078
LATCH.simulator lru latch 2,318 3,713 1,395
LATCH.simulator hash latch 2,342 3,833 1,491
STAT.recursive cpu usage 1,452 4,325 2,873
STAT.CPU used by this session 1,452 4,326 2,874
STAT.workarea executions - optimal 10,006 5,017 -4,989
STAT.index scans kdiixs1 15,000 20,008 5,008
STAT.shared hash latch upgrades - no wait 15,000 20,008 5,008
STAT.execute count 15,002 20,066 5,064
STAT.consistent gets - examination 15,002 20,716 5,714
LATCH.shared pool 10,155 16,719 6,564
STAT.enqueue requests 5,015 15,129 10,114
LATCH.SQL memory manager workarea list latch 10,341 20,950 10,609
STAT.recursive calls 25,010 35,643 10,633
LATCH.enqueue hash chains 5,266 15,985 10,719
LATCH.library cache lock 89 10,905 10,816
STAT.redo entries 5,008 25,412 20,404
STAT.no work - consistent read gets 15,414 36,181 20,767
LATCH.library cache pin 30,103 51,060 20,957
STAT.consistent changes 36 21,216 21,180
LATCH.library cache 30,216 51,771 21,555
LATCH.enqueues 5,288 27,756 22,468
STAT.consistent gets from cache 45,425 76,982 31,557
STAT.consistent gets 45,425 76,982 31,557
STAT.db block gets 5,150 36,932 31,782
STAT.db block gets from cache 5,150 36,932 31,782
STAT.table scan rows gotten 20,000 52,506 32,506
STAT.table fetch by rowid 55,000 95,220 40,220
STAT.buffer is not pinned count 30,151 70,731 40,580
STAT.db block changes 10,101 52,171 42,070
STAT.buffer is pinned count 84,829 129,671 44,842
LATCH.session allocation 5 61,210 61,205
STAT.session logical reads 50,575 113,914 63,339
LATCH.row cache objects 30,216 154,108 123,892
LATCH.cache buffers chains 101,368 295,973 194,605
STAT.physical read total bytes 442,368 671,744 229,376
STAT.session pga memory 1,638,400 2,097,152 458,752
STAT.session uga memory max 914,428 2,014,540 1,100,112
STAT.session uga memory 1,571,232 147,936 -1,423,296
STAT.undo change vector size 441,396 3,580,024 3,138,628
STAT.session pga memory max 393,216 4,194,304 3,801,088
STAT.redo size 1,247,368 5,844,764 4,597,396
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
228,983 718,705 489,722 31.86%
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
rwijk@ORA10GR2> disconnect
Apparently the statement from my earlier blog post:
And more important, multirow DML is capable of checking the rule only once instead of "for each row"
is simply not true. All validations are still being done "for each row", as can be seen by the 5000 executes in this tkprof snippet:
********************************************************************************
SELECT 'The contracts of customer ' || TO_CHAR(CUSTOMER_ID) || ' cannot have
a gap larger than 1 year.' AS MSG
FROM
( SELECT CUSTOMER_ID , MONTHS_BETWEEN ( STARTDATE , LAG(ENDDATE,1,STARTDATE)
OVER (PARTITION BY CUSTOMER_ID ORDER BY STARTDATE) ) GAP_IN_MONTHS FROM
CONTRACTS_RULEGEN_APPROACH WHERE CUSTOMER_ID = :B1 ) WHERE CUSTOMER_ID =
:B1 GROUP BY CUSTOMER_ID HAVING MAX(GAP_IN_MONTHS) > 12
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 1.59 1.54 0 0 0 0
Fetch 5000 0.90 0.91 22 15118 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 2.50 2.45 22 15118 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER (cr=15118 pr=22 pw=0 time=1213488 us)
5000 SORT GROUP BY NOSORT (cr=15118 pr=22 pw=0 time=1138466 us)
20000 VIEW (cr=15118 pr=22 pw=0 time=1075722 us)
20000 WINDOW BUFFER (cr=15118 pr=22 pw=0 time=809447 us)
20000 FILTER (cr=15118 pr=22 pw=0 time=603751 us)
20000 TABLE ACCESS BY INDEX ROWID CONTRACTS_RULEGEN_APPROACH (cr=15118 pr=22 pw=0 time=445594 us)
20000 INDEX RANGE SCAN CTT_R_UK1 (cr=10061 pr=22 pw=0 time=232940 us)(object id 64963)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 3 0.01 0.01
db file scattered read 4 0.00 0.00
********************************************************************************
The other two business rule validation queries were executed 5000 times as well. I think the framework can benefit a lot performance wise, when the implementation would change to a single query picking up all rows in the transition effect (TE) views. This can be tricky, because you'll have a query that has to perform well with a TE-view containing 1 row as well as a TE-view potentially containing all rows of the base table. However, when optimized for 1 row - for example by using the "cardinality (alias 1)" hint - it will do index accesses even when validating the entire table. This will likely still be faster than doing this row by row. A very nice challenge for Toon indeed ;-).
Now the third and final test: a single statement updating 5000 rows from 2500 customers:
rwijk@ORA10GR2> alter session set events '10046 trace name context forever, level 12'
2 /
Sessie is gewijzigd.
rwijk@ORA10GR2> begin
2 runstats_pkg.rs_start
3 ;
4 update contracts_trigger_approach
5 set enddate = decode(enddate,date '2005-12-31',date '2005-07-01',date '2007-07-01'
6 where id between 1 and 10000
7 and enddate in (date '2005-12-31',date '2007-12-31')
8 ;
9 runstats_pkg.rs_middle
10 ;
11 update contracts_rulegen_approach
12 set enddate = decode(enddate,date '2005-12-31',date '2005-07-01',date '2007-07-01'
13 where id between 1 and 10000
14 and enddate in (date '2005-12-31',date '2007-12-31')
15 ;
16 runstats_pkg.rs_stop(1000)
17 ;
18 end;
19 /
Run1 draaide in 1387 hsecs
Run2 draaide in 1775 hsecs
Run1 draaide in 78,14% van de tijd
Naam Run1 Run2 Verschil
LATCH.object queue header operation 305 1,363 1,058
LATCH.simulator lru latch 2,075 3,343 1,268
LATCH.simulator hash latch 2,084 3,444 1,360
STAT.execute count 15,002 12,536 -2,466
STAT.sorts (memory) 5,001 2,508 -2,493
STAT.recursive calls 25,008 20,318 -4,690
STAT.consistent gets - examination 15,001 10,246 -4,755
STAT.index scans kdiixs1 15,000 10,004 -4,996
STAT.shared hash latch upgrades - no wait 15,000 10,004 -4,996
STAT.table fetch by rowid 50,000 55,015 5,015
LATCH.enqueues 2,787 7,833 5,046
STAT.buffer is pinned count 74,851 69,788 -5,063
STAT.enqueue requests 2,510 7,605 5,095
LATCH.enqueue hash chains 2,813 7,959 5,146
LATCH.library cache pin 30,177 35,626 5,449
LATCH.library cache 30,364 36,048 5,684
STAT.calls to get snapshot scn: kcmgss 15,007 7,626 -7,381
STAT.workarea executions - optimal 10,006 2,517 -7,489
STAT.sorts (rows) 22,334 12,341 -9,993
LATCH.library cache lock 206 10,514 10,308
STAT.no work - consistent read gets 15,374 25,824 10,450
STAT.buffer is not pinned count 30,130 50,138 20,008
STAT.redo entries 5,009 25,414 20,405
STAT.consistent changes 36 21,223 21,187
STAT.table scan rows gotten 20,000 48,557 28,557
LATCH.session allocation 14 30,554 30,540
STAT.db block gets from cache 5,150 36,931 31,781
STAT.db block gets 5,150 36,931 31,781
STAT.session logical reads 50,534 83,075 32,541
STAT.db block changes 10,101 52,182 42,081
LATCH.row cache objects 30,106 99,882 69,776
LATCH.cache buffers chains 101,518 244,665 143,147
STAT.physical read total bytes 0 229,376 229,376
STAT.session uga memory max 914,428 1,949,076 1,034,648
STAT.session pga memory 1,572,864 131,072 -1,441,792
STAT.session uga memory 1,636,696 147,936 -1,488,760
STAT.session pga memory max 1,114,112 3,866,624 2,752,512
STAT.undo change vector size 441,700 3,580,568 3,138,868
STAT.redo size 1,246,420 5,844,080 4,597,660
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
213,877 494,024 280,147 43.29%
PL/SQL-procedure is geslaagd.
rwijk@ORA10GR2> rollback
2 /
Rollback is voltooid.
rwijk@ORA10GR2> disconnect
And here is a tkprof snippet:
********************************************************************************
begin x_RW_CTT_RULE2.check_rule(:b1,:b2); end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.43 3.65 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.43 3.65 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75 (recursive depth: 2)
********************************************************************************
SELECT DISTINCT 'Customer ' || TO_CHAR(:B1 ) || ' has overlapping contracts.'
AS MSG
FROM
CONTRACTS_RULEGEN_APPROACH C1 WHERE C1.CUSTOMER_ID = :B1 AND EXISTS ( SELECT
'overlapping contract' FROM CONTRACTS_RULEGEN_APPROACH C2 WHERE C2.ID !=
C1.ID AND C2.CUSTOMER_ID = C1.CUSTOMER_ID AND C2.STARTDATE <=
NVL(C1.ENDDATE,C2.STARTDATE+1) AND NVL(C2.ENDDATE,C1.STARTDATE+1) >=
C1.STARTDATE )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2500 0.90 0.94 0 0 0 0
Fetch 2500 1.56 1.31 0 15116 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5001 2.46 2.25 0 15116 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
0 HASH UNIQUE (cr=15116 pr=0 pw=0 time=1499558 us)
0 HASH JOIN (cr=15116 pr=0 pw=0 time=1297756 us)
10000 TABLE ACCESS BY INDEX ROWID CONTRACTS_RULEGEN_APPROACH (cr=7558 pr=0 pw=0 time=228059 us)
10000 INDEX RANGE SCAN CTT_R_UK1 (cr=5030 pr=0 pw=0 time=113918 us)(object id 64963)
10000 TABLE ACCESS BY INDEX ROWID CONTRACTS_RULEGEN_APPROACH (cr=7558 pr=0 pw=0 time=156392 us)
10000 INDEX RANGE SCAN CTT_R_UK1 (cr=5030 pr=0 pw=0 time=69210 us)(object id 64963)
********************************************************************************
RuleGen is not necessarily validating once for each row. Here the validation will take place for each distinct customer. You can see that the business rules get validated 2500 times, and not 5000 times, because there are 2500 distinct customers. And hence the performance is catching up. Half the number of validations is almost twice the speed here. Often though, the validations will occur at primary key level, or at some aggregated level where nothing is aggregated (test 2), so effectively for each row.
My conclusion:
It seems RuleGen has two issues regarding performance::
- RuleGen uses a global temporary table to hold the rows for the transition effect. This gives extra overhead. Unfortunately, the alternative of using PL/SQL collection variables doesn't work. Toon found out that it has problems when a "rollback to savepoint" is executed; in that case the contents of the collection won't match the outstanding DML.
- In the After Statement trigger the following pseudocode is executed:
for r in (when_query(constraint))
where I would have liked to see one how_query executing with a "column_name in (select column_name from transition effect view)"
loop
how_query(constraint,r);
end loop;
This way real performance enhancements can be achieved for multi row DML. Toon has put this on his list, but it will likely not be a part of the upcoming version 2.1.
Of course RuleGen is much slower than the rarely used api approach. But being slower than the "normal" trigger approach is something to consider if you plan to use RuleGen on your performance critical system. On the other hand, you get a lot in return:
- speed up of the development of business rules, not by percentages, but by factors
- possibility of enabling and disabling business rules at runtime
- the ability to easily defer constraint checking to transaction level instead of statement level
- continue-on-error-execution-model, meaning that you can collect all errors instead of bailing out on the first error encountered.
- possible less validations of your business rule, as could be seen in the different test results between test 3 and test 2.
- possibility of dynamically changing execution order of the rules
An excellent article!
ReplyDeleteI like the way you proof things!
Thanks,
Dimitri
Wat een mooi duidelijk verhaal.
ReplyDelete