0

If we use Oracle Row Level Security(RLS) to hide some records - Are there any Performance Implications - will it slow down my SQL Queries? The Oracle Package for this is: DBMS_RLS.

I plan to add: IS_HISTORICAL=T/F to some tables. And then using RLS, hide the records which have value of IS_HISTORICAL=T.

The SQL Queries we use in application are quite complex, with inner/outer joins, subqueries, correlated subqueries etc.

Of the 200 odd tables, about 50 of them will have this RLS Policy (to hide records by IS_HISTORICAL=T) applied on them. Rest of the 150 tables are child tables of these 50 Tables, so RLS is implicit on them.

Any License implications?

Thanks.

ddevienne
  • 1,802
  • 2
  • 17
  • 28
Jasper
  • 8,440
  • 31
  • 92
  • 133
  • depends on your context level, but a static context (for example) will not be a cause of performance issues (VPD itself won't be any slower than the app doing a select * ... where is_historical = 'N' ... ). – tbone Feb 19 '13 at 13:48

1 Answers1

2

"Are there any Performance Implications - will it slow down my SQL Queries? "

As with all questions relating to performance the answer is, "it depends". RLS works by wrapping the controlled query in an outer query which applies the policy function as a WHERE clause...

select /*+ rls query */ * from ( 
    select /*+ your query */ ... from t23 
    where whatever = 42 )
where rls_policy.function_t23 = 'true'

So the performance implications rest entirely on what goes in the function.

The normal way of doing these things is to use context namespaces. These are predefined areas of session memory accessed through the SYS_CONTEXT() function. As such the cost of retrieving a stored value from a context is negligible. And as we would normally populate the namespaces once per session - say by an after logon trigger or a similar connection hook - the overall cost per query is trivial. There are different ways of refreshing the namespace which might have performance implications but again these are trivial in the overall schem of things (see this other answer).

So the performance impact depends on what your function actually does. Which brings us to a consideration of your actual policy:

"this RLS Policy (to hide records by IS_HISTORICAL=T)"

The good news is the execution of such a function is unlikely to be costly in itself. The bad news is the performance may still be Teh Suck! anyway, if the ratio of live records to historical records is unfavourable. You will probably end up retrieving all the records and then filtering out the historical ones. The optimizer might push the RLS predicate into the main query but I think it's unlikely because of the way RLS works: it avoids revealing the criteria of the policy to the general gaze (which makes debugging RLS operations a real PITN).

Your users will pay the price of your poor design decision. It is much better to have journalling or history tables to store old records and keep only live data in the real tables. Retaining historical records alongside live ones is rarely a solution which scales.

"Any License implications?"

DBMS_RLS requires an Enterprise Edition license.

Community
  • 1
  • 1
APC
  • 144,005
  • 19
  • 170
  • 281
  • APC> Thanks for the very informative reply. We will be partitioning the tables based on IS_HISTORICAL column, so that would separate out the historical records and oracle can optimize on it even further. The separate history tables approach was not considered for this reason, and also that there would be hundreds of history tables to create and maintain. – Jasper Feb 19 '13 at 09:57
  • Have you benchmarked that approach? Because I don't know whether partition pruning will kick in when you would hope it will. – APC Feb 19 '13 at 10:59