Document
| Purpose |
| Scope |
| Details |
| Plan Stability |
| SQL Plan Management (SPM) Overview |
| Licensing |
| Usage |
| Limits and Restrictions |
| Plan Transportation & Loading/Dropping Plans and Baselines |
| Troubleshooting SQL Plan Management Issues |
| Known Issues |
| Tracing and Debugging |
| SQL Profiles do not offer true plan stability |
| Pre 11g: Stored Outlines |
| Stored Outline documents: |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.0.1.0 and laterOracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
PURPOSE
SCOPE
DETAILS
Plan Stability
Each New Oracle release contains a number of features designed to improve the performance of queries in a myriad of ways. However, some applications (or portions of applications) are such that a static and predictable performance is more desirable than improvements in certain areas. Plan Stability addresses these needs.
SQL Plan Management (SPM) Overview
SQL plan management is a preventative mechanism that records and evaluates 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.
SPM is controlled by the use of the following parameters:
Document 567107.1 Init.ora Parameter "OPTIMIZER_USE_SQL_PLAN_BASELINES" Reference Note
Licensing
SQL Plan Management (SPM) is available as part of Oracle Database Enterprise Edition and does not require any additional licenses. For more clarification refer to:
Database Licensing Information User Manual
You can find the most current version of this product documentation here: http://docs.oracle.com/en/applications/?tab=3
| "Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?: | https://blogs.oracle.com/optimizer/entry/does_the_use_of_sql |
Usage
Refer to the following document for SPM usage information.
Document 1400903.1 Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT)
Limits and Restrictions
Refer to the following document for SPM usage restrictions:
Useful Blog Posts:
| SQL Plan Management (Part 1 of 4) Creating SQL plan baselines: | http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines |
| SQL Plan Management (Part 2 of 4) SPM Aware Optimizer: | http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_2_of_4_spm_aware_optimizer |
| SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines: | http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_3_of_4_evolving_sql_plan_baselines_1 |
| SQL Plan Management (Part 4 of 4): User Interfaces and Other Features: | http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_4_of_4_user_interfaces_and_other_features |
| SPM and Auto Capture: | https://blogs.oracle.com/optimizer/entry/what_you_need_to_know |
Plan Transportation & Loading/Dropping Plans and Baselines
Refer to the following document for details:
Troubleshooting SQL Plan Management Issues
Refer to the following document for details:
Known Issues
Document 1204524.1 Dbms_spm.Load_plans_from_sqlset Getting Xml Parsing Error
Document 12732879.8 Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
Document 11719151.8 Bug 11719151 - SQL Plan Management capture causes slowness
Document 9910484.8 Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
Document 14009271.8 Bug 14009271 - Cannot reproduce execution plan imported from SQL Tuning Sets / Outlines
NOTE: The latest Patchset release contains fixes for known issues across the spectrum of server products. For example:
Tracing and Debugging
SQL Profiles do not offer true plan stability
It is a common misconception that SQL Profiles can provide plan stability; but they do not enforce a specific plan. While SQL Profiles provide a static plan initially, the plan can change with volume changes. This means that they do not necessarily provide the static plan that may be desired. Refer to the following document differences:
For more information on SQL Profiles, see:
Pre 11g: Stored Outlines
Plan stability in earlier versions was ensured by Stored Outlines. This feature have a number of deficiencies when compared to SPM but can provide stable systems. Stored outlines are in-flexible so when data volume, profile or shape does change, adjustments cannot be made.
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.
Stored Outline documents:
Document 445126.1 HOW TO: Create a Stored Outline Based Upon an Existing Cursor
Document 728647.1 How to Transfer Stored Outlines from One Database to another (9i and above)
Document 102311.1 How to Move Stored Outlines for One Application from One Database to Another (8i)
Document 730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline
Document 726802.1 Editing Stored Outlines in Oracle10g and Oracle11g
Document 463288.1 How to generate an outline with a good plan loaded into shared_pool
REFERENCES
NOTE:787692.1 - Loading Hinted Execution Plans into SQL Plan Baseline.NOTE:789520.1 - How to Enable SQL Plan Management Tracing
NOTE:567107.1 - Init.ora Parameter "OPTIMIZER_USE_SQL_PLAN_BASELINES" Reference Note
NOTE:1348303.1 - 11.2.0.3 Patch Set - List of Bug Fixes by Problem Type
NOTE:1204524.1 - DBMS_SPM.LOAD_PLANS_FROM_SQLSET Getting Xml Parsing Error
NOTE:601807.1 - Oracle 11gR1 Upgrade Companion
NOTE:567104.1 - Init.ora Parameter "OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES" Reference Note
NOTE:1438701.1 - How To Configure Automatic Purge(Drop) Of SQL Plan Baseline(SPM).
BUG:16625010 - SPM BASELINE NOT WORKING FOR SQL CALLED FROM PL/SQL
NOTE:789888.1 - How to Load SQL Plans into SQL Plan Management (SPM) from the Automatic Workload Repository (AWR)
NOTE:1353272.1 - Uploaded STS Execution plan to SPM Differs from the STS Original
NOTE:11719151.8 - Bug 11719151 - SQL Plan Management capture causes slowness
NOTE:880485.1 - Transporting SQL PLAN Baselines from One Database to Another.
NOTE:102311.1 - How to Transfer Stored Outlines from One Database to Another (8i)
NOTE:1187767.1 - Baseline Not Used Under Plsql When OFE Is Changed
NOTE:790039.1 - How to Drop Plans from the SQL Plan Management (SPM) Repository
NOTE:445126.1 - HOW TO: Create a Stored Outline Based Upon an Existing Cursor
NOTE:456518.1 - How to Use SQL Plan Management (SPM) - Plan Stability Worked Example
NOTE:67536.1 - Stored Outline Quick Reference
BUG:11719151 - MERGE STATEMENT USED WHEN SPM CAPTURE CAUSING PERFORMANCE ISSUE
NOTE:456019.1 - How to Transport a SQL Tuning Set (STS)
NOTE:726802.1 - Editing Stored Outlines in Oracle10g and Oracle11g
NOTE:12732879.8 - Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
NOTE:1323444.1 - Database Hangs after loading plan baselines from sqlset from 10gR2 to 11gR2
BUG:12732879 - SPM: COULDN'T REPRODUCE ANY ENABLED+ACCEPTED PLAN SO USING THE COST-BASED PLAN
NOTE:728647.1 - How to Transfer Stored Outlines from One Database to Another (9i and above)
NOTE:9910484.8 - Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
NOTE:730062.1 - How to Edit a Stored Outline to Use the Plan from Another Stored Outline
BUG:9910484 - UNNECESSARY UPDATES ON SQLOBJ$DATA CAUSING OBJECT AND TABLESPACE GROWTH
NOTE:785351.1 - Oracle 11gR2 Upgrade Companion
NOTE:801033.1 - How to Load SQL Execution Plans in to a STS so that they can be Transferred (to 11g) and Loaded into a SQL Plan Baseline.
No comments:
Post a Comment