A SQL Profile Is Not Consistently Fixing a Performance Problem When VPD Is In Use

(Doc ID 1440205.1)

Last updated on SEPTEMBER 10, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 and later   [Release: 10.1 and later ]
Information in this document applies to any platform.

Symptoms

A SQL Profile created to fix performance problems works inconsistently on a query---sometimes producing a desirable plan and sometimes producing various undesirable plans.  Some of the objects involved in the query have Virtual Private Database (VPD) policies in place.

The SQL Profile can be created either by accepting a recommendation from the SQL Tuning Advisor or by using a script provided in SQLT.  The former are "true" SQL Profiles while the latter are specialty SQL Profiles.  True SQL Profiles do not force a specific plan to be used, so getting multiple plans after applying a SQL Profile is expected behavior.  SQLT-created SQL Profiles are intended to force a specific plan.

This note applies to both kinds of SQL Profiles.

Changes

A SQL Profile may have been created on a problematic query, or VPD policies may have been created on one or more objects referenced in a query with a formerly working SQL Profile.

EBS Users:  Upgrading to EBS R12 can enable VPD policies on many tables as part of Multi-Org Access Control (MOAC) support.

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