To implement entity rules and inter-entity rules, one of the options is to use an on commit-time fast refreshable materialized view with a check constraint on top. You can read a few examples in these posts. It's an elegant way to check for conditions at commit-time, and quite remarkable when you see it for the first time. But there is a serious caveat to this way of implementing business rules, which may or may not be relevant for your situation. In any case, it doesn't hurt to be aware of this caveat. To show what I mean I create a table called percentages_per_year:
rwijk@ORA11GR1> create table percentages_per_year (ym,percentage)
2 as
3 select date '2005-12-01' + numtoyminterval(level,'month')
4 , case mod(level,3) when 0 then 5 else 10 end
5 from dual
6 connect by level <= 48
7 /
Table created.
rwijk@ORA11GR1> select * from percentages_per_year
2 /
YM PERCENTAGE
------------------- ----------
01-01-2006 00:00:00 10
01-02-2006 00:00:00 10
01-03-2006 00:00:00 5
01-04-2006 00:00:00 10
01-05-2006 00:00:00 10
01-06-2006 00:00:00 5
01-07-2006 00:00:00 10
01-08-2006 00:00:00 10
01-09-2006 00:00:00 5
01-10-2006 00:00:00 10
01-11-2006 00:00:00 10
01-12-2006 00:00:00 5
01-01-2007 00:00:00 10
01-02-2007 00:00:00 10
01-03-2007 00:00:00 5
01-04-2007 00:00:00 10
01-05-2007 00:00:00 10
01-06-2007 00:00:00 5
01-07-2007 00:00:00 10
01-08-2007 00:00:00 10
01-09-2007 00:00:00 5
01-10-2007 00:00:00 10
01-11-2007 00:00:00 10
01-12-2007 00:00:00 5
01-01-2008 00:00:00 10
01-02-2008 00:00:00 10
01-03-2008 00:00:00 5
01-04-2008 00:00:00 10
01-05-2008 00:00:00 10
01-06-2008 00:00:00 5
01-07-2008 00:00:00 10
01-08-2008 00:00:00 10
01-09-2008 00:00:00 5
01-10-2008 00:00:00 10
01-11-2008 00:00:00 10
01-12-2008 00:00:00 5
01-01-2009 00:00:00 10
01-02-2009 00:00:00 10
01-03-2009 00:00:00 5
01-04-2009 00:00:00 10
01-05-2009 00:00:00 10
01-06-2009 00:00:00 5
01-07-2009 00:00:00 10
01-08-2009 00:00:00 10
01-09-2009 00:00:00 5
01-10-2009 00:00:00 10
01-11-2009 00:00:00 10
01-12-2009 00:00:00 5
48 rows selected.
The data is such that all percentages in a year add up to exactly 100. To ensure this is always the case in the future, I implement it as a business rule using an on commit-time fast refreshable materialized view:
rwijk@ORA11GR1> create materialized view log on percentages_per_year
2 with sequence, rowid (ym,percentage) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view mv
2 refresh fast on commit
3 as
4 select to_char(ym,'yyyy') year
5 , sum(percentage) year_percentage
6 , count(percentage) count_percentage
7 , count(*) count_all
8 from percentages_per_year
9 group by to_char(ym,'yyyy')
10 /
Materialized view created.
rwijk@ORA11GR1> alter materialized view mv
2 add constraint year_percentage_100_ck
3 check (year_percentage=100)
4 /
Materialized view altered.
rwijk@ORA11GR1> select * from mv
2 /
YEAR YEAR_PERCENTAGE COUNT_PERCENTAGE COUNT_ALL
---- --------------- ---------------- ----------
2009 100 12 12
2008 100 12 12
2007 100 12 12
2006 100 12 12
4 rows selected.
rwijk@ORA11GR1> exec dbms_stats.gather_table_stats(user,'percentages_per_year')
PL/SQL procedure successfully completed.
Now the idea is to run 4 jobs, each updating 12 records of one year. To see what's going on inside each job, I create a logging table with logging procedures that run autonomous transactions:
rwijk@ORA11GR1> create table t_log
2 ( year number(4)
3 , starttime timestamp(6)
4 , endtime timestamp(6)
5 )
6 /
Table created.
rwijk@ORA11GR1> create procedure log_start (p_year in t_log.year%type)
2 is
3 pragma autonomous_transaction;
4 begin
5 insert into t_log (year,starttime)
6 values (p_year,systimestamp)
7 ;
8 commit;
9 end log_start;
10 /
Procedure created.
rwijk@ORA11GR1> create procedure log_end (p_year in t_log.year%type)
2 is
3 pragma autonomous_transaction;
4 begin
5 update t_log
6 set endtime = systimestamp
7 where year = p_year
8 ;
9 commit;
10 end log_end;
11 /
Procedure created.
The jobs will run a procedure called update_percentages. The procedure sets its module and action column, to be able to identify the trace files that are created. It logs itself before the update and after the commit:
rwijk@ORA11GR1> create procedure update_percentages (p_year in number)
2 is
3 begin
4 dbms_application_info.set_module('p',to_char(p_year))
5 ;
6 dbms_session.session_trace_enable
7 ;
8 log_start(p_year)
9 ;
10 update percentages_per_year
11 set percentage = case when extract(month from ym) <= 8 then 9 else 7 end
12 where extract(year from ym) = p_year
13 ;
14 commit
15 ;
16 log_end(p_year)
17 ;
18 end update_percentages;
19 /
Procedure created.
And now let 4 jobs run:
rwijk@ORA11GR1> declare
2 l_job_id binary_integer;
3 begin
4 for i in 2006 .. 2009
5 loop
6 dbms_job.submit(l_job_id,'rwijk.update_percentages('||to_char(i)||');');
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> pause
After all jobs have run concurrently and have finished, the logging table looks like this:
rwijk@ORA11GR1> select year
2 , starttime
3 , endtime
4 , endtime - starttime duration
5 from t_log
6 order by endtime
7 /
YEAR STARTTIME ENDTIME
----- ------------------------------- -------------------------------
DURATION
-------------------------------
2007 14-JAN-10 09.59.45.625000 PM 14-JAN-10 09.59.45.843000 PM
+000000000 00:00:00.218000
2006 14-JAN-10 09.59.45.609000 PM 14-JAN-10 09.59.45.875000 PM
+000000000 00:00:00.266000
2008 14-JAN-10 09.59.45.656000 PM 14-JAN-10 09.59.45.906000 PM
+000000000 00:00:00.250000
2009 14-JAN-10 09.59.45.625000 PM 14-JAN-10 09.59.45.937000 PM
+000000000 00:00:00.312000
4 rows selected.
You can see the overlap of the processes. The process for year 2007 has finished first, and doesn't experience any noticeable wait. The other three processes do. A look at the trace file shows one line like this:
WAIT #8: nam='enq: JI - contention' ela= 85497 name|mode=1246298118 view object #=106258 0=0 obj#=-1 tim=7377031494
This line is followed after the COMMIT (XCTEND rlbk=0) in the trace file. The wait event is called "enq: JI - contention", where JI stands for materialized view refresh :-). In the documention it says that JI stands for "Enqueue used during AJV snapshot refresh". So it's still unclear where those letters JI really stand for, although the J may be from the J in AJV. And google suggests that AJV stands for "Aggregate join view". Maybe there are so many two-letter enqueue names already that they just picked one?
In the WAIT line from the trace file, we see three parameters after the "ela= 85497". We can see these parameters in the v$event_name view too:
rwijk@ORA11GR1> select event#
2 , name
3 , parameter1
4 , parameter2
5 , parameter3
6 , wait_class
7 from v$event_name
8 where name = 'enq: JI - contention'
9 /
EVENT# NAME
---------- ----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
WAIT_CLASS
----------------------------------------------------------------
818 enq: JI - contention
name|mode
view object #
0
Other
1 row selected.
The first parameter is "name|mode", which equals a rather strange number 1246298118. This pretty-old-but-still-very-useful article of Anjo Kolk helped me interpreting the number:
rwijk@ORA11GR1> select chr(bitand(1246298118,-16777216)/16777215) ||
2 chr(bitand(1246298118,16711680)/65535) “Lock”
3 , bitand(1246298118, 65535) “Mode”
4 from dual
5 /
Lo Mode
-- ----------
JI 6
1 row selected.
Where lock mode 6 means an exclusive lock.
The second parameter is the object_id as you can find it in the all_objects view. The third parameter isn't useful here.
So a materialized view cannot be fast refreshed more than once in a given period. It's serialized during the commit. Normally when I update record 1 and commit and you update record 2 and commit, we don't have to wait for each other. But when having an on commit-time fast refreshable materialized view on top of the table, we do have to wait when two sessions do some totally unrelated transactions concurrently against the same table. Is this a problem? Not when the table is modified infrequently or only by a single session. But it can be a big problem when applied to a table that modifies a lot concurrently.
So be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed. Or be prepared to be called by the users of your system ...
Rob,
ReplyDeletejust for clarification: Are the UDDATE DMLs themselves serialized?
I wonder, since this is a on commit MV - the contention should be caused by the COMMIT, not the by the update itself?
So the question is: Do multiple DMLs within one transaction serialize, or does only the COMMIT serialize on the refresh?
Of course this doesn't make the situation any better if the table is hit by frequent and very small transactions, but it might make a difference if there are transactions that are larger in size and cover more or heavier DMLs.
Randolf
Hi Randolf,
ReplyDeleteIt's only the commit that is serialized. But in OLTP databases, the update is typically immediately followed by the commit, so the end user will report that the update is slow.
I have edited the text, because the original text was indeed not clear enough.
Thanks for your comment!
Regards,
Rob.
Rob,
ReplyDeleteThanks. Learned something new today. But your last few statements confuse me:
But it can be a big problem when applied to a table that modifies a lot concurrently.
So be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed. Or be prepared to be called by the users of your system ...
If you have to implement such business rules, I thought there is no option but to serialize table access. I thought MV-with-constraint is the most elegant way to implement business rule in database since we are allowing database to handle serialization for us (automagically). What are the alternastives? I hope you are not suggesting to handle serialization of table access to be moved out of the database. :)
Hi Narendra,
ReplyDeleteYes you always need to serialize somehow. But in this case every table access needs to be serialized, whereas a trigger or api approach needs to be serialized only along some parent row, which is MUCH more scalable. In the case above serializing per year is already sufficient. So four transactions, each for a different year, do not need to be serialized.
Because Oracle lacks an ON-COMMIT trigger, the only alternative I see -for aggregation rules- is to switch to the api approach. Or (yuck!) outside the database ...
Most other entity and inter-entity rules can still be implemented by database triggers as well as by using the api approach.
Regards,
Rob.
Hi Rob,
ReplyDeleteInteresting experiment.
Of course if two TX's affect the same year, serialization needs to be done.
But, as you probably are aware of, implementation of this business rule need not cause any serialization between your transactions, as these transactions all affect *different* years, and the rule is such that different transactiond affecting different years, can never interfer with each other, as far as this rule is concerned.
It is a pitty that Oracle is not smart enough to deduce this fact from the view-text of your materialized view definition.
My question now is. What happpens if you yourself try to circumvent any serialization between your transactions, by instead of one MV, defining an MV + check-constraint *per year*. Is Oracle then smart enough to see that each TX only affects one MV, and that the MV's are mutually distinct? And that therefor no serialization happens?
Or is it still dumb: and fast refreshes each MV in each TX?
Cheers,
Toon
Hi Toon,
ReplyDeleteI just checked it to be sure. In the case of four MV's, one for each year, the four processes are each updating (MERGE INTO "RWIJK"."MV200n" ...) all MV's. With even more contention.
Having an MV per parent row, wouldn't be a practical solution anyway.
Regards,
Rob.
Hello Rob,
ReplyDeleteWhat about partitioning the percentages_per_year table and the materialized view in order to have PCT. Would that relieve contention?
i faced same issue as you. Congratulations for this blog !
ReplyDeleteYou really need the MV log to only record records for its MV's year...
ReplyDeleteso the given period is how long?
ReplyDelete