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.