Queries With VPD Enabled Regress When Executed By Other Users Than Object Owners

(Doc ID 834663.1)

Last updated on NOVEMBER 04, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.
Checked for relevance on 24-Apr-2013


Symptoms

After enabling the VPD policy (or policies), performance for queries including the tables with VPD is regressing visibly when the queries are executed by users that are not the object owners.

To check the situation, sql trace files can be taken and, after formatting them with tkprof, it is remarked that the inline views which appear as a result of the VPD policies are not merged in the main query.

This problem tends to appear after migration from lower releases to 10.2 releases.

In most of these situations, there are also PLSQL structures involved in the queries. As a result, the same problem can appear for products using PLSQL in their objects, like Oracle Spatial.

Example:
Initial query:

select <column list>
from table_1, table_2
where <condition_list>;


The VPD policy is adding an extra predicate on table_1.
As such, logically, the query becomes:

select <column list>
from (select * from table_1 where <vpd_condition>) table_1, table_2
where <condition_list>;


In most situations, the Oracle optimizer would merge the inline view in the main query and the query would become something similar to the initial query, just that it would have an extra predicate (the VPD predicate) added:

select <column list>
from table_1, table_2
where <initial_condition_list>
and <vpd_condition>;


However, in this particular situation, it is noticed that this merge is not performed and the query is executed as:

select <column list>
from (select * from table_1 where <vpd_condition>) table_1, table_2
where <condition_list>;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms