Making SQL always hard parsed – Using row level security

I hit a very interesting OTN Forum discussion. To make the long story short, the request was as following

How can I make the SQL always hard parsed even when bind variables are being used? (Changing the code is not an option)

Not common in OLTP, right? But on DSS environment, this could be very common and even useful!

As far as I remember, MSSQL Server has the functionality not to load the SQL onto shared memory. Very cool feature.

But Oracle? No, it’s impossbile.

The discussion got many suggestions. Let’s have a look at them.

1. Adaptive cursor sharing will save us.

Cool. It’s 11g new feature. Love it. But this is 10g database.

2. SQL Tuning advisor(SQL profile) or stored outline

Not relevant.

3. Get rid of statistics and sample dynamically

But dynamic sampling works only on optimization time. Dynamic sampling itself does not make SQL always optimized.

4. Change code to use literal value

Not an option.

5. Invalidate the cursor by updating statistics.
Not bad. By updating statistics like this,

exec dbms_stats.set_table_stats('schema_name', 'table_name', -
num_rows=>null, no_invalidate=>false);

We can invalidate all the cursors dependent on the table without the danger of changing statistics.  But this is also not an option because we can’t change the software code.

(This was my suggestion, actually)

6. Apply row-level security model to generate random predicate.

Very clever idea by this guy. Clever, Randolf! Maybe the only way to meet the request.

Do not change code, but make my SQL always hard parsed!

It works like this:

create table t1
as
select * from all_objects
where rownum <= 1000;

exec dbms_stats.gather_table_stats(user, 't1');

drop sequence s1;

create sequence s1 cache 1000;

create or replace package pkg_rls_hard_parse is
  function force_hard_parse (in_schema varchar2, in_object varchar2)
      return varchar2;
end pkg_rls_hard_parse;
/ 

create or replace package body pkg_rls_hard_parse is
  function force_hard_parse (in_schema varchar2, in_object varchar2)
      return varchar2
  is
    s_predicate varchar2(100);
    n_random pls_integer;
  begin
    select s1.nextval || ' = ' || s1.currval into s_predicate from dual;
    return s_predicate;
  end force_hard_parse;
end pkg_rls_hard_parse;
/ 

exec dbms_rls.add_policy (user, 't1', 'hard_parse_policy', user, -
        'pkg_rls_hard_parse.force_hard_parse', 'select');  <-- Clever!

alter system flush shared_pool;

exec dbms_monitor.session_trace_enable;

-- Execute the same query 10 times
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;

exec dbms_monitor.session_trace_disable;

The result of tkprof is like following:

select /* placeholder */ count(*)
from
 t1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.00       0.02          0          0          0           0
Execute     10      0.01       0.01          0          0          0           0
Fetch       20      0.07       0.06          0        150          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       40      0.09       0.10          0        150          0          10

Misses in library cache during parse: 10   <-- Look here!
Misses in library cache during execute: 10

Cool!

One of the problems pointed out was that this row level security feature has some overhead. For instance, have a look at this:

SELECT S1.NEXTVAL || ' = ' || S1.CURRVAL
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     30      0.00       0.00          0          0          0           0
Fetch       30      0.00       0.00          0          0          1          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       61      0.00       0.01          0          0          1          30

begin :con := PKG_RLS_HARD_PARSE.FORCE_HARD_PARSE(:sn, :on); end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       30      0.00       0.01          0          0          0           0
Execute     30      0.01       0.01          0          0          0          30
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       60      0.01       0.03          0          0          0          30

It looks like Oracle calls the policy function multiple times to apply row the level security policy. Not sure why, but there would be some overhead especially when the original queries are very frequently called.

Another problem to note is that it would flood the shared pool. We are generating tons of unshareable SQLs! But this is very heavy DDS system that parse overhead wouldn’t be a big deal.

This kind of clever tricks really makes me happy. :)