Scaling dynamic SQL

The story

I was working for one of the Russian biggest telcos back then and marketing guys came out with their next idea. One of the most frequent things your customers do is checking their balances. The idea was to attach an advertising to each balance response. However, usually you don't really want to attach some static message that you show to everyone because it is not the way your customers care about it. Think of Google, which became so popular because it prioritizes information by relevance. Doing something useless to your customers not only annoys them but wastes your bandwidth as well.

What you have to do is targeted adverts. What that means is that you may substantially increase the value by figuring out what your customers would like to see based on a data about them which you have in your billing system. And you know literally hundreds of things about any single customer. Starting from age, martial status, birthday, active services and ending up with approximate location based on triangulation data. Think of Google's sponsored links.

The system

Upon receiving a balance request, the system had to find out various stuff about requester and then figure out what to show based on the rules from a marketing department. Rules had to offer virtually unlimited flexibility and the system itself had to be flexible to introduce and remove targeting conditions on the fly.

The implementation

What we had is a simple table with a PL/SQL predicates, like this:

SQL> create table p
  2  (
  3   p_id number primary key,
  4   p varchar2(4000)
  5  );
 
Table created

SQL> insert into p values (1, 'to_number(:age) between 16 and 18');
 
1 row inserted
SQL> insert into p values (2, 'to_date(:birthday, ''yyyymmdd'')=trunc(sysdate) or :balance > 1000');
 
1 row inserted
SQL> insert into p values (3, 'to_number(:balance) between 100 and 200 and :age > 18');
 
1 row inserted
 
SQL> commit;
 
Commit complete

Of course, in reality it was a bit more complex than that, allowing for prioritizing, schedules, etc. As you'd probably already guessed, upon receiving a balance request we were binding data about customer into the above predicates to figure out which one evaluates to true which in turn lead to relevant advert. Given that you may be getting around 5000 balance requests each second plus you have to multiply this by a number of predicates you have to check, you already talking about tens of thousands evaluations per second. In other words, you have to make the entire thing pretty darn efficient.

Soft parsing

Soft parsing is something which is deemed by many to be inevitable as soon as you have to deal with stuff like above -- storing dynamically generated PL/SQL predicates in a table and executing these during runtime. And this is something we had to avoid because, even with stuff like session_cached_cursor, parse is still a parse, a scalability inhibitor and CPU waster. Think of not using KIWI.

What to do?

I'll show you a little trick you can use to break the dreaded marriage of dynamic SQL with parsing. Take a look at the following package:

create or replace package eval is

 type t_cached_cursors is table of number index by p.p%type;
 g_cached_cursors t_cached_cursors;

 function evaluate(
  p in p.p%type,
  p_n in dbms_sql.Varchar2_Table,
  p_v in dbms_sql.Varchar2_Table
 ) return boolean;

 function evaluate_nc(
  p in p.p%type,
  p_n in dbms_sql.Varchar2_Table,
  p_v in dbms_sql.Varchar2_Table
 ) return boolean;
end eval;

create or replace package body eval is

function evaluate(
 p in p.p%type,
 p_n in dbms_sql.Varchar2_Table,
 p_v in dbms_sql.Varchar2_Table
) return boolean is
 l_cursor number;
 l_res  number;
begin
 begin
  l_cursor:=g_cached_cursors(p);
 exception when no_data_found then
  l_cursor:=dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor, p, dbms_sql.native);
  g_cached_cursors(p):=l_cursor;
 end;
 dbms_sql.bind_variable(l_cursor, 'l_res', l_res);

 for i in 1 .. p_n.count
 loop
  if (instr(p, ':'||p_n(i)) > 0)
  then
   dbms_sql.bind_variable(l_cursor, p_n(i), p_v(i));
  end if;
 end loop;

 l_res:=dbms_sql.execute(l_cursor);
 dbms_sql.variable_value(l_cursor, 'l_res', l_res);

 return (l_res=1);
end;

function evaluate_nc(
 p in p.p%type,
 p_n in dbms_sql.Varchar2_Table,
 p_v in dbms_sql.Varchar2_Table
) return boolean is
 l_cursor number;
 l_res  number;
begin
 l_cursor:=dbms_sql.open_cursor;
 dbms_sql.parse(l_cursor, p, dbms_sql.native);
 dbms_sql.bind_variable(l_cursor, 'l_res', l_res);

 for i in 1 .. p_n.count
 loop
  if (instr(p, ':'||p_n(i)) > 0)
  then
   dbms_sql.bind_variable(l_cursor, p_n(i), p_v(i));
  end if;
 end loop;

 l_res:=dbms_sql.execute(l_cursor);
 dbms_sql.variable_value(l_cursor, 'l_res', l_res);
 dbms_sql.close_cursor(l_cursor);

 return (l_res=1);
end;

end eval;

The package has two functions -- evaluate and evaluate_nc, the first one is using a simple caching trick. The idea behind evaluate function is really that simple -- upon opening and parsing a cursor, place it into in-memory table indexed by cursor text for further reuse instead of closing it. Each execution peeks at that in-memory table to see if there is a cursor we can reuse instead of going through the whole parsing exercise.

The usage is simple as well:

SQL> declare
  2   l_n  dbms_sql.varchar2_table;
  3   l_v  dbms_sql.varchar2_table;
  4   l_res boolean;
  5  begin
  6   l_n(1):='age';
  7   l_n(2):='birthday';
  8   l_n(3):='balance';
  9   l_v(1):='16';
 10   l_v(2):='20090101';
 11   l_v(3):='1500';
 12  
 13   for cur in (select p_id, 'declare l_res number; begin :l_res:=case when ('||p||') then 1 else 
0 end; end;' p from p)
 14   loop
 15    l_res:=eval.evaluate(cur.p, l_n, l_v);
 16    dbms_output.put_line('eval p'||to_char(cur.p_id)||': '||case when l_res then 'true' else 'fal
se' end);
 17   end loop;
 18  end;
 19  /
eval p1: true
eval p2: true
eval p3: false

Let's do some tests now. Here are two test procedures:

create or replace procedure test_cached(
 p_i in number
) is
 l_p   dbms_sql.Varchar2_Table;
 l_n   dbms_sql.varchar2_table;
 l_v   dbms_sql.varchar2_table;
 l_res  boolean;
begin
 l_n(1):='age';
 l_n(2):='birthday';
 l_n(3):='balance';

 l_v(1):='100';
 l_v(2):='20090101';
 l_v(3):='1000';

 select 'declare /* cached */ l_res number; begin :l_res:=case when ('||p||') then 1 else 0 end; end;'
  bulk collect into l_p
  from p;

 dbms_monitor.session_trace_enable(waits => true, binds => false);
 for i in 1 .. p_i
 loop
  for j in 1 .. l_p.count
  loop
   l_res:=eval.evaluate(l_p(j), l_n, l_v);
  end loop;
 end loop;
 dbms_monitor.session_trace_disable;
end;

create or replace procedure test_not_cached(
 p_i in number
) is
 l_p   dbms_sql.Varchar2_Table;
 l_n   dbms_sql.varchar2_table;
 l_v   dbms_sql.varchar2_table;
 l_res  boolean;
begin
 l_n(1):='age';
 l_n(2):='birthday';
 l_n(3):='balance';

 l_v(1):='100';
 l_v(2):='20090101';
 l_v(3):='1000';

 select 'declare /* not_cached */ l_res number; begin :l_res:=case when ('||p||') then 1 else 0 end; end;'
  bulk collect into l_p
  from p;

 dbms_monitor.session_trace_enable(waits => true, binds => false);
 for i in 1 .. p_i
 loop
  for j in 1 .. l_p.count
  loop
   l_res:=eval.evaluate_nc(l_p(j), l_n, l_v);
  end loop;
 end loop;
 dbms_monitor.session_trace_disable;
end;

I tested each of these using four parallel jobs with 100000 iterations (p_i parameter) each. Here is what I've got (I'm using one of the predicates as an example):

declare /* cached */ l_res number; begin :l_res:=case when (to_number(:age) 
  between 16 and 18) then 1 else 0 end; end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      1.40       1.29          0          0          0      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001      1.40       1.29          0          0          0      100000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 40     (recursive depth: 2)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  cursor: pin S wait on X                        35        0.01          0.37
  cursor: pin S                                  91        0.00          0.00

declare /* not_cached */ l_res number; begin :l_res:=case when 
  (to_number(:age) between 16 and 18) then 1 else 0 end; end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   100000      1.00       1.21          0          0          0           0
Execute 100000      2.73       2.74          0          0          0      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200000      3.73       3.95          0          0          0      100000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40     (recursive depth: 2)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache: mutex X                        116        0.00          0.00
  cursor: mutex S                                24        0.00          0.00
  cursor: pin S                                  83        0.00          0.00
  cursor: pin S wait on X                        30        0.01          0.28

That's a three times improvement. Note that in first tkprof report we got only one parse, while in second one amount of parses equals executions, as we expected.