FAQ: SQL Plan Management (SPM) Frequently Asked Questions
(Doc ID 1524658.1)
Last updated on NOVEMBER 18, 2023
Applies to:
Oracle Database Exadata Express Cloud Service - Version N/A and laterOracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A 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:
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.
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? |
| If I have a SPM baseline, how do I find the SQL_ID for the statement that originally created the baseline ? |
| 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 |