Performance Impact of Using RLS

Question

Generic_Schema has one common table, GENERIC_TABLE. The primary key of GENERIC_TABLE is COL1 and COMPANY_ID.

Company_id     Col1 Col2 Col3
-------------- ---- ---- ----
ALL_COMPANIES  A    B    C
Comp1          A    B1   C1
Comp3          A    B3   C

Specific Company Schema

1. Schema1 =>Comp1
2. Schema2 =>Comp2
3. SchemaN =>CompN

We have implemented RLS on GENERIC_TABLE. The RLS function returns a clob value, but the logic to retrieve the filter values inside the function is:

1. Compare the key with other values, and if the Comp1 Schema request data, then A|B1|C1 data is retrieved and the ALL_COMPANIES data is not retrieved because a company-specific value exists in the table.
2. If the Comp4 retrieve data, it will get only the A|B|C record.
3. The filter condition need to be retrieved from another function which returns the rowid.

Now, here is the issue. After implementing this, the elapsed time of the RLS is much higher, though we didn't see much I/O. Can anyone think that this design is an issue, because this is not a static predicate and it is context sensitive. Please let me know if you need more inputs.

Functions Definition is

create or replace function
  book_access_policy
  (obj_schema varchar2, obj_name varchar2)
  return clob

 is

CURSOR get_d_predicate
IS
 SELECT /*+ RESULT_CACHE */ rd
  FROM
  (
  SELECT rowid rd
        ,DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY CASE WHEN COMPANY_ID = sys_context('publishing_application','company_id') THEN 1 ELSE 2 END) rnk
FROM GENERIC_TABLE
  )
   WHERE rnk=1;

get_d_predicate_rec get_d_predicate%ROWTYPE;
d_predicate clob;

begin

 if sys_context('publishing_application','company_id') IS NULL THEN
  d_predicate:= ' 1=1';

 else
   d_predicate =' rowid in ('
    OPEN get_d_predicate;
    LOOP
     FETCH get_d_predicate INTO get_d_predicate_rec;
     EXIT WHEN get_d_predicate%NOTFOUND;
       d_predicate = d_predicate|| get_d_predicate_rec.rd;
    END LOOP;
   CLOSE get_d_predicate;
    d_predicate:=d_predicate||')';


 end if;

return d_predicate;

 end;
 /