Viewing VPD predicates with DBMS_XPLAN

Oracle Virtual Private Database (VPD), also known as Row Level Security (RLS), provides a very high level of flexibility in exposing data to users. It is also a very convenient tool for forcing hard parsing of a SQL statement either on every execution or depending on some criteria (e.g. see here). VPD works by appending invisible (e.g. query text in V$SQL doesn’t contain them) predicates to SQL statements. This invisibility is mostly a good thing, but in some cases it can also be a nuisance (e.g. when troubleshooting SQL).  There are a couple of blogs describing how to view these predicates (here and here), both suggesting DBMS_XPLAN as one of the ways.

However, it doesn’t always work as expected, and sometimes it doesn’t work at all. Consider an example:

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

drop table t1;

create table t1(x number);

create or replace function f1(p_schema varchar2, p_object varchar2) return varchar2 is
begin
 return 'x=1';
end;
exec dbms_rls.add_policy(object_name =>'T1', policy_name => 'norows_policy', policy_function => 'F1');

select * from t1;

select * from table(dbms_xplan.display_cursor(null, null, 'predicate last'));

SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("X"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

So far so good: we’ve created a VPD predicate X=1, and we can see it in the predicate section of the plan. Now let’s change the predicate to 1=2, and look at the dbms_xplan.display_cursor output again:

SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     1 (100)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

Now we see an internally transformed version (NULL IS NOT NULL) instead of the original one. Let’s try ‘1=1’:

SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Now the predicate section is not showing anything at all. Apparently, the optimizer has recognized the redundancy of this predicate and removed it. But this type of predicates, even though logically redundant, is very handy for forcing hard-parsing (see the link above), so not being able to see it can be a problem.

Fortunately, the predicate can be still viewed in V$VPD_POLICY

select predicate from v$vpd_policy where sql_id = '27uhu2q2xuu7';
---
1=1

Conclusion

Using the predicate section of DBMS_XPLAN.DISPLAY_CURSOR output for checking VPD predicates is not reliable, one should use V$VPD_POLICY instead.