My Oracle Support Banner

FAQ: SQL Plan Management (SPM) Frequently Asked Questions (Doc ID 1524658.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

Purpose

This document addresses a number of Frequently Asked Questions for SQL Plan Management (SPM).

These Questions and Answers were generated from the Q&A Session for "How to achieve Plan Stability on 11g with SQL Plan Management (SPM)" Webcast. The recording for this webcast can be accessed in the following document:

Document 740966.1 Advisor Webcasts: Current Schedule and Archived recordings

SQL plan management is a preventative mechanism that can records and evaluate the execution plans of SQL statements over time. This mechanism can build a SQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well.

Document 1359841.1 Plan Stability Features (Including SPM) Start Point

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06

Chapter 15 Using SQL Plan Management
http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#PFGRF007

Chapter 20 Using Plan Stability
http://docs.oracle.com/cd/E11882_01/server.112/e16638/outlines.htm#PFGRF707

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Questions and Answers

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
Purpose
 Ask Questions, Get Help, And Share Your Experiences With This Article
Questions and Answers
 General
 What is the difference between a SPM Baseline, a SQL Profile and a SQL_PATCH?
 Can there be Multiple baselines?
 Is there a feature which allows partitioning a baseline for multiple user profiles, for example one for application, one default and another for selected end users?
 I ran my SQL with optimizer_capture_sql_plan_baselines = true but I do not see a baseline in dba_sql_plan_baselines?
 If I have a SPM baseline, how do I find out what SQL_ID it relates to?
 How can I display the plans for a SQL statement recorded in a SPM baseline?
 Do I need to change my statistics gathering routine having implemented SPM Baselines?
 How do I move SPM Baselines between databases?
 How do SPM Baselines integrate with Adaptive Features?
 How do SPM Baselines interact with the Oracle Database In-Memory Option?
 Plan Choice
 What is the order of precedence for Baselines, SQL profiles, Outlines, Hints etc?
 How does SPM decide between the same SQL statement called by two different database users, referencing two different tables with the same name?
 Plan Evolution
 How can I Monitor Plan Evolution?
 How are Adaptive Plans evolved?
 How does a DBA decide which plans to accept and enable?
 Plan Capture
 What is the recommended method for plan capture?
 Is it good to set auto capture to true right after an upgrade of the Database?
 What if the first captured plan is not the most optimal?
 How can plans be migrated from Oracle 9i?
 Is it possible to load plans from the AWR into SPM?
 Is it possible to upload an existing baseline or manually create a baseline?
 If a baseline is imported from a "test" database into a "production" database do environment variables that must be set to the same values (e.g. optimizer_mode)?
 Why doesn't the view, DBA_SQL_PLAN_BASELINES, display execution statistics? Columns, such as ELAPSED_TIME and CPU_TIME, are zero.
 Performance and Footprint
 What is the performance impact of setting optimizer_capture_sql_plan_baselines on a production system?
 How can you calculate additional disk space in the sysaux tablespace for spm?
 How can you verify the performance of different accepted plans stored in a baseline verses non accepted plans?
 Does capturing and use of SPM baselines degrade the database performance?
 Troubleshooting SPM Baseline Issues
 What are the known issues with SPM ?
 What should I collect to diagnose issues where an SPM baseline is not used?
 Discuss SQL Plan Management (SPM)
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.