A SQL Profile Is Not Consistently Fixing a Performance Problem When VPD Is In Use
(Doc ID 1440205.1)
Last updated on MARCH 03, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and laterOracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A 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.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |