Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Uday.

Asked: February 03, 2021 - 4:59 pm UTC

Last updated: February 10, 2021 - 4:43 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hi Tom,
we have implemented RLS based on client identifier of a session
when RLS is applied for a particular client identifier the query is keep on running
if i apply same where condition with admin user the query is running fast
is this due to RLS or any other reason?
is where condition is applied for each row of the table or entire table at once?

and Connor said...

RLS changes the text of the query, so you are potentially running a brand new query, which is then subject to the same standard SQL tuning requirements as normal SQL, ie, good statistics, right indexes, etc etc...

I would look at running:

ALTER SESSION SET events '10730 trace name context forever, level 1';

and then running your SQL with the RLS policies. For example, if you had a RLS function (say)

create or replace function rls_func ... as
begin
  return 'hiredate is not null';
end;
/


then the trace file created when you ran "select * from emp" would show

Policy function: RLS_FUNC
RLS view  :
SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "CHA"."EMP"   "EMP" WHERE (hiredate is not null)


Then you can focus your tuning efforts on that *true* SQL

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about using "dbms_sql2.expand_sql_text" to get the final sql text

Rajeshwaran, Jeyabal, February 04, 2021 - 10:15 am UTC

for 11g database, we can even use dbms_sql2.expand_sql_text to get the final sql text, something like this.

create or replace function results_subset(
  p_schema  in varchar2,
  p_object_name in varchar2)
return varchar2 as
begin
  return 'deptno =20';
end;
/

begin
dbms_rls.add_policy(object_schema=>user,
     object_name=>'EMP',
     policy_name=>'EMP_DEPT_CHK',
     function_schema=>user,
     policy_function=>'RESULTS_SUBSET',
     statement_types=>'SELECT,INSERT,UPDATE',
     update_check=>true);
end;
/
demo@ORA11G> variable x clob
demo@ORA11G> begin
  2  dbms_sql2.expand_sql_text(' select deptno,count(*) from emp group by deptno',:x );
  3  end;
  4  /

PL/SQL procedure successfully completed.

demo@ORA11G> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."DEPTNO" "DEPTNO",COUNT(*) "COUNT(*)" FROM  (SELECT "A2"."EMPNO" "EM
PNO","A2"."ENAME" "ENAME","A2"."JOB" "JOB","A2"."MGR" "MGR","A2"."HIREDATE" "HIR
EDATE","A2"."SAL" "SAL","A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO" FROM "DEMO"."EMP" 
"A2" WHERE "A2"."DEPTNO"=20) "A1" GROUP BY "A1"."DEPTNO"


Jonathan, have mentioned about this in his blog (@ https://jonathanlewis.wordpress.com/2012/07/10/expanding-sql/ )

Still Performance issue not solved

Uday Salla, February 04, 2021 - 3:45 pm UTC

HI Tom,
first of all thanks for the reply
i got the query and i executed it with different user compleated in 1.8 seconds
but for that perticular user its same
all the indexes intact and stats gathered no full table scans
even explain plan for both is same

Connor McDonald
February 10, 2021 - 4:43 am UTC

Run your query with gather plan statistics so we can see, ie

SQL> select /*+ gather_plan_statistics */ ...
  2  from ...


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.