Saturday, January 10, 2009

Using DBMS_RULE_ADM to evaluate dynamic predicates

The best comments are the ones which inspire you to try and learn something new. I received one of these to my recent blog post about Scaling dynamic SQL. Stas, one of my good friends, asked whether I have tried DBMS_RULE_ADM package for this task. Well, now I did. Stas, you should be careful what you ask next time, you might get it :)

The first thing we need to do is create an evaluation context:
SQL> declare
2 l_vtl sys.re$variable_type_list;
3 begin
4 l_vtl:=sys.re$variable_type_list(
5 sys.re$variable_type('age', 'number', null, null),
6 sys.re$variable_type('balance', 'number', null, null),
7 sys.re$variable_type('birthdate', 'date', null, null),
8 sys.re$variable_type('today', 'date', null, null)
9 );
10
11 dbms_rule_adm.create_evaluation_context(
12 evaluation_context_name => 'adverts_ctx',
13 variable_types => l_vtl
14 );
15 end;
16 /

PL/SQL procedure successfully completed
We can create the rules itself now:
SQL> declare
2 l_rules dbms_sql.Varchar2_Table;
3 begin
4 dbms_rule_adm.create_rule_set(
5 rule_set_name => 'adverts'
6 );
7
8 l_rules(1):=':age between 16 and 18';
9 l_rules(2):=':birthdate = :today or :balance > 1000';
10 l_rules(3):=':balance between 100 and 200 and :age > 18';
11
12 for i in 1 .. 3
13 loop
14 dbms_rule_adm.create_rule(
15 rule_name => 'rule_'||to_char(i),
16 condition => l_rules(i),
17 evaluation_context => 'adverts_ctx'
18 );
19
20 dbms_rule_adm.add_rule(
21 rule_name => 'rule_'||to_char(i),
22 rule_set_name => 'adverts'
23 );
24 end loop;
25 end;
26 /

PL/SQL procedure successfully completed
Since there will be no SQL executed, I've decided to simply measure wall clock time, given that we can obtain latch wait information from the extended SQL trace:
SQL> create table results
2 (
3 sid number,
4 cs number
5 );

Table created
Here is the test procedure itself:
SQL> create or replace procedure test_dra(
2 p_i in number
3 ) is
4 l_age sys.re$variable_value;
5 l_balance sys.re$variable_value;
6 l_birthdate sys.re$variable_value;
7 l_today sys.re$variable_value;
8 l_vvl sys.re$variable_value_list;
9 l_true sys.re$rule_hit_list;
10 l_maybe sys.re$rule_hit_list;
11 l_time number;
12 begin
13 l_age:=sys.re$variable_value('age', anydata.convertnumber(16));
14 l_balance:=sys.re$variable_value('balance', anydata.convertnumber(1500));
15 l_birthdate:=sys.re$variable_value('birthdate', anydata.convertdate(to_date('20090101', 'yyyymmdd')));
16 l_today:=sys.re$variable_value('today', anydata.convertdate(trunc(sysdate)));
17
18 l_vvl:=sys.re$variable_value_list(l_age, l_balance, l_birthdate, l_today);
19
20 l_true:=sys.re$rule_hit_list();
21 l_maybe:=sys.re$rule_hit_list();
22
23 l_time:=dbms_utility.get_time;
24 dbms_monitor.session_trace_enable(waits => true, binds => false);
25
26 for i in 1 .. p_i
27 loop
28 dbms_rule.evaluate(
29 rule_set_name => 'adverts',
30 evaluation_context => 'adverts_ctx',
31 variable_values => l_vvl,
32 stop_on_first_hit => false,
33 true_rules => l_true,
34 maybe_rules => l_maybe
35 );
36 end loop;
37
38 dbms_monitor.session_trace_disable;
39 insert into results values (sys_context('userenv', 'sid'), dbms_utility.get_time-l_time);
40 end test_dra;
41 /

Procedure created
As before, I've used four parallel jobs with 100000 iterations each. Here are the results I've got:
SQL> select * from results;

SID CS
---------- ----------
134 3472
131 3412
133 3205
132 3358
And tkprof of one of the jobs:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache: mutex X 769 0.00 0.04
latch free 39 0.00 0.00
Which is, well, about 6.5 times slower given wall clock time compared to the cached results using package from my previous blog post:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 300000 4.85 4.97 0 0 0 300000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300003 4.85 4.97 0 0 0 300000

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache: mutex X 209 0.00 0.00
cursor: pin S wait on X 98 0.01 1.04
cursor: pin S 360 0.00 0.00
The interesting thing to note here is that DBMS_RULE_ADM seems to have a better scalability, however, I can't really say at what point it could be justified (if at all) given a pretty hefty wall clock time difference.

No comments:

Post a Comment