Friday, January 15, 2010

enq: JI - contention

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 ...

Sunday, January 10, 2010

CAST-COLLECT versus CAST-MULTISET

At work I am building an interface using SQL object types and SQL collection types. I noticed a difference between using the COLLECT aggregate function in combination with a CAST function, versus the CAST-MULTISET method. I started out using CAST-COLLECT, but switched to CAST-MULTISET. Here is a simulation:

rwijk@ORA11GR1> create table customers
2 ( id number(6) not null primary key
3 , name varchar2(30) not null
4 , birthdate date not null
5 )
6 /

Table created.

rwijk@ORA11GR1> create table bankaccounts
2 ( nr number(10) not null primary key
3 , customer_id number(6) not null
4 , current_balance number(14,2) not null
5 )
6 /

Table created.

rwijk@ORA11GR1> insert into customers
2 select 1, 'Jeffrey Lebowski', date '1964-01-01' from dual union all
3 select 2, 'Walter Sobchak', date '1961-01-01' from dual
4 /

2 rows created.

rwijk@ORA11GR1> create sequence customers_seq start with 3
2 /

Sequence created.

rwijk@ORA11GR1> insert into bankaccounts
2 select 123456789, 1, 10 from dual union all
3 select 987654321, 1, 100 from dual union all
4 select 234567890, 2, 2000 from dual
5 /

3 rows created.

rwijk@ORA11GR1> select * from customers
2 /

ID NAME BIRTHDATE
---------- ------------------------------ -------------------
1 Jeffrey Lebowski 01-01-1964 00:00:00
2 Walter Sobchak 01-01-1961 00:00:00

2 rows selected.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR CUSTOMER_ID CURRENT_BALANCE
---------- ----------- ---------------
123456789 1 10
987654321 1 100
234567890 2 2000

3 rows selected.

Two simple tables with a master-detail relationship. To be able to communicate (not to store, except for simple auditing) a customer, SQL types come in handy. Three types, two object types and a collection type:

rwijk@ORA11GR1> create type to_bankaccount is object
2 ( nr number(10)
3 , current_balance number(14,2)
4 );
5 /

Type created.

rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
2 /

Type created.

rwijk@ORA11GR1> create type to_customer is object
2 ( name varchar2(30)
3 , birthdate date
4 , bankaccounts ta_bankaccounts
5 );
6 /

Type created.

rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /

TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3

3 rows selected.

The external system can call a function to get the customer information. The function below simulates that function. I used the CAST-COLLECT method:

rwijk@ORA11GR1> create function customer_with_collect
2 ( p_customer_id in customers.id%type
3 ) return to_customer
4 is
5 o_customer to_customer;
6 begin
7 select to_customer
8 ( c.name
9 , c.birthdate
10 , ( select cast
11 ( collect(to_bankaccount(ba.nr,ba.current_balance))
12 as ta_bankaccounts
13 )
14 from bankaccounts ba
15 where ba.customer_id = c.id
16 )
17 )
18 into o_customer
19 from customers c
20 where c.id = p_customer_id
21 ;
22 return o_customer;
23 end customer_with_collect;
24 /

Function created.

rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /

TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3

3 rows selected.

rwijk@ORA11GR1> select customer_with_collect(1)
2 from dual
3 /

CUSTOMER_WITH_COLLECT(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))


1 row selected.

rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /

TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3
SYSTPuS41SrC7Q9G6/OfQT4VHPA== COLLECTION 0

4 rows selected.

But as you can see, when you use the function, an internal type has been persistently created. This is the internal type that is used to store the result of the COLLECT-function in:

rwijk@ORA11GR1> select collect(to_bankaccount(nr,current_balance))
2 from bankaccounts
3 /

COLLECT(TO_BANKACCOUNT(NR,CURRENT_BALANCE))(NR, CURRENT_BALANCE)
------------------------------------------------------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==(TO_BANKACCOUNT(123456789, 10), TO_BANKACCOUNT(98
7654321, 100), TO_BANKACCOUNT(234567890, 2000))


1 row selected.

Note that the name of the type is the same as listed earlier.

If you don't query the user_types view as I did, then you might be surprised that the following sequence doesn't work:

rwijk@ORA11GR1> drop type to_customer
2 /

Type dropped.

rwijk@ORA11GR1> drop type ta_bankaccounts
2 /

Type dropped.

rwijk@ORA11GR1> drop type to_bankaccount
2 /
drop type to_bankaccount
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


So I have to drop the internal type first, before I can drop type TO_BANKACCOUNT:

rwijk@ORA11GR1> column type_name new_value systype
rwijk@ORA11GR1> select type_name
2 from user_types
3 where type_name like 'SYS%'
4 /

TYPE_NAME
------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==

1 row selected.

rwijk@ORA11GR1> drop type "&systype"
2 /
old 1: drop type "&systype"
new 1: drop type "SYSTPuS41SrC7Q9G6/OfQT4VHPA=="

Type dropped.

rwijk@ORA11GR1> drop type to_bankaccount
2 /

Type dropped.

This behaviour annoyed me slightly, so I checked out another way using CAST-MULTISET:

rwijk@ORA11GR1> create type to_bankaccount is object
2 ( nr number(10)
3 , current_balance number(14,2)
4 );
5 /

Type created.

rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
2 /

Type created.

rwijk@ORA11GR1> create type to_customer is object
2 ( name varchar2(30)
3 , birthdate date
4 , bankaccounts ta_bankaccounts
5 );
6 /

Type created.

rwijk@ORA11GR1> create function customer_with_multiset
2 ( p_customer_id in customers.id%type
3 ) return to_customer
4 is
5 o_customer to_customer;
6 begin
7 select to_customer
8 ( c.name
9 , c.birthdate
10 , cast
11 ( multiset
12 ( select to_bankaccount(ba.nr,ba.current_balance)
13 from bankaccounts ba
14 where ba.customer_id = c.id
15 )
16 as ta_bankaccounts
17 )
18 )
19 into o_customer
20 from customers c
21 where c.id = p_customer_id
22 ;
23 return o_customer;
24 end customer_with_multiset;
25 /

Function created.

And with CAST-MULTISET, there are no additional internal types created:

rwijk@ORA11GR1> select customer_with_multiset(1)
2 from dual
3 /

CUSTOMER_WITH_MULTISET(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))


1 row selected.

rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /

TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TA_BANKACCOUNTS COLLECTION 0
TO_BANKACCOUNT OBJECT 2
TO_CUSTOMER OBJECT 3

3 rows selected.

So that's 0-1 in favour of CAST-MULTISET. Let's check performance:

rwijk@ORA11GR1> declare
2 o_customer to_customer;
3 begin
4 runstats_pkg.rs_start;
5 for i in 1..10000
6 loop
7 o_customer := customer_with_collect(1);
8 end loop;
9 runstats_pkg.rs_middle;
10 for i in 1..10000
11 loop
12 o_customer := customer_with_multiset(1);
13 end loop;
14 runstats_pkg.rs_stop(1000);
15 end;
16 /
Run1 draaide in 171 hsecs
Run2 draaide in 130 hsecs
Run1 draaide in 131.54% van de tijd

Naam Run1 Run2 Verschil
STAT.undo change vector size 10,264 2,844 -7,420
LATCH.shared pool 10,236 8 -10,228
STAT.buffer is pinned count 0 20,000 20,000
STAT.redo size 30,784 3,780 -27,004

Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
183,334 170,551 -12,783 107.50%

PL/SQL procedure successfully completed.

That's 0-2.

Sunday, January 3, 2010

Holiday Challenge Time

This was the title of a thread on OTN's SQL and PL/SQL Forum. Centinul posted an interesting puzzle to be solved by either SQL or PL/SQL here. If you like to puzzle yourself, then stop reading here, and go visit the link (and don't look at the answers of course :-) ).

The puzzle was not only a great way to spend time, it's also a nice show case for hierarchical queries and the answer of Frank Kulash taught me something new. The setup:

rwijk@ORA11GR1> create table matrix (id,a,b,c,d,e,f,g,h,i,j)
2 as
3 select 'A',1,1,1,0,0,0,1,0,1,0 from dual union all
4 select 'B',0,0,0,0,1,1,1,0,1,0 from dual union all
5 select 'C',0,1,1,1,1,0,0,0,0,1 from dual union all
6 select 'D',1,0,0,0,1,0,1,0,0,0 from dual union all
7 select 'E',1,1,0,0,0,0,0,0,0,1 from dual union all
8 select 'F',0,1,1,0,1,1,0,0,0,0 from dual union all
9 select 'G',0,1,0,1,1,1,0,1,0,1 from dual union all
10 select 'H',1,1,0,1,0,0,0,1,0,0 from dual union all
11 select 'I',0,1,1,1,0,1,1,1,0,1 from dual union all
12 select 'J',1,0,0,0,0,0,0,1,0,1 from dual union all
13 select 'K',1,0,0,1,1,1,0,1,1,0 from dual union all
14 select 'L',0,0,1,0,1,1,0,0,1,0 from dual union all
15 select 'M',0,0,1,0,0,0,0,1,1,1 from dual union all
16 select 'N',1,0,1,0,0,0,1,1,1,0 from dual union all
17 select 'O',1,0,0,1,1,0,1,0,0,0 from dual
18 /

Table created.


The table looks like this:




Quoting the challenge:
Our goal is from a start position identified as (A,E), First Row, Fifth column, traverse DOWN the matrix to reach a valid point on row "O."

Restrictions

1. You can only move UP, DOWN, LEFT, or RIGHT (not diagonally) by one unit.
2. The path must be a repeating pattern of 0 1 0 1 0 1 ... etc For example a move from (A,E) to (B,E) is valid while a move from (A,E) to (A,F) is not.

The correct solution has the following requirements:

1. Identifies the path from start to finish using an identifiable way to determine the ROW,COLUMN for each entry point in the path while abiding by the restrictions above.


My solution is to first transform the table with a matrix layout to a transactional layout using three columns:
  • rw, an integer between 1 and 15, representing the row
  • col, an integer between 1 and 10, representing the column
  • value, an integer between 0 and 1, representing the matrix cell's value
In this layout a hierarchical query can do the job relatively easy:

rwijk@ORA11GR1> with matrix_transformed as
2 ( select ascii(id) - 64 rw
3 , col
4 , case col
5 when 1 then a
6 when 2 then b
7 when 3 then c
8 when 4 then d
9 when 5 then e
10 when 6 then f
11 when 7 then g
12 when 8 then h
13 when 9 then i
14 when 10 then j
15 end as value
16 from matrix
17 , ( select level col from dual connect by level <= 10 )
18 )
19 select ltrim
20 ( sys_connect_by_path('['||chr(64+rw)||','||chr(64+col)||']',';')
21 , ';'
22 ) path
23 from matrix_transformed
24 where rw = 15
25 start with rw = 1
26 and col = 5
27 connect by nocycle
28 prior value != value
29 and prior rw != 15
30 and ( ( rw = prior rw and col = prior col - 1 )
31 or ( rw = prior rw and col = prior col + 1 )
32 or ( rw = prior rw - 1 and col = prior col )
33 or ( rw = prior rw + 1 and col = prior col )
34 )
35 /

PATH
------------------------------------------------------------------------------
[A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[C,I];
[C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
[K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]

[A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[B,J];
[C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
[K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]


2 rows selected.

These two rows represent these paths:



Lines 1-18 transform the table to 150 rows in transactional layout. A hierarchical query then solves the puzzle by starting with the row [A,E] (lines 25-26 of the query). Line 27 specifies NOCYCLE to ignore loops. When joining the table with itself - like you do when using hierarchical queries - you want the next level of rows to be only the rows that are immediately UP, DOWN, LEFT or RIGHT of the current cell. This is what lines 30-34 implement. You want to discard the row when the cell value of the matrix is the same as in the previous level to ensure that 0's are followed by 1's and vice versa (line 28). And when you've reached row O - when rw equals 15 - the query needs to stop (line 29). Of this entire hierarchy, you only want one output row: the last one, which equals the one with rw = 15. And this was new to me: you can specify a where clause in your hierarchical query, which is evaluated AFTER the connect by. Of course this behaviour is documented:
Oracle processes hierarchical queries as follows:

* A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
* The CONNECT BY condition is evaluated.
* Any remaining WHERE clause predicates are evaluated.

If you look closely at my answer in the thread and here, you'll notice the difference. Finally a SYS_CONNECT_BY_PATH is used to print the entire route from [A,E] to [O,I].