Agenda
- Introduction to SQL Plan Management
- Overview
- Plan Capture
- Plan Selection
- Plan Evolution
- Using SQL Plan Management to upgrade
- Using SPM and OFE
- Using SPM and SQL Tuning Set
- Using SPM and development environments
- Using SPM and SQL Performance Analyzer
- Using SQL Plan Management to correct regressions
Important Baseline SQL Plan Attributes
When a plan enters the plan history, it is associated with a number of important attributes:
SIGNATURE, SQL_HANDLE, SQL_TEXT, and PLAN_NAME are important identifier for search operations.
ORIGIN allows you to determine if the plan was automatically captured (AUTO-CAPTURE), or manually inserted into the plan history (MANUAL).
ENABLED and ACCEPTED: ENABLED means that the plan is enabled for use by the optimizer. If ENABLED is not set, the plan will not be considered. ACCEPTED means that the plan was validated as a good plan, either automatically by the system or by the user manually changing it to ACCEPTED. Once a plan changes to ACCEPTED, it will only become not ACCEPTED if someone use DBMS_SPM.ALTER_SQL_PLAN_BASELINE() to change its status. An ACCEPTED plan can be temporarily disabled by removing the ENABLED setting. A plan has to be ENABLED and ACCEPTED for the optimizer to consider using it.
FIXED means that the optimizer will only consider those plans, not other plans. For example, if you have ten baseline plans and three of them are marked FIXED, the optimizer will only use the best plan from these three, ignoring all the others.
You can look at each plan’s attributes using the DBA_SQL_PLAN_BASELINES view as shown on the slide. You can then change some of them using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function. You also have the possibility to remove plans or complete plan history using the DBMS_SPM.PURGE_SQL_PLAN_BASELINE function. The example shown on the slide change the ACCEPTED attribute of the SYS_SQL_PLAN_8DFC352F359901EA to YES, making it ACCEPTED and thus part of the baseline.
Note: The DBA_SQL_PLAN_BASELINES view contains additional attributes that allows you to determine when each plan was last used, and whether a plan should be automatically purged.
No tuning pack required SQL Plan Baseline Scenario
Set the init.ora parameter optimizer_features_enable to 10, this is make the optimizer behave as if this was a 10g oracle database and set the init.ora parameter OPTIMIZER_CAPYURE_SQL_PLAN_BASELINES to true.
Next run all the SQL statements in the application. This will capture all of the 10g plans for the application and store them in a plan history.
Then set the init.ora parameter optimizer_features_enable back to 11 and rerun SQL statements. Any new plans generated by the 11g optimizer will be queued for verification and will only be used when they have been tested to perform as good as or better than the 10g plans.
SQL Plan Managements allows you to upgrade from 10g to 11g without any plan changes!
SQL Replay and SQL Plan Baseline Scenario
A variation of the first method described in the previous slide is through the use of SQL Replay. You can capture pre-Oracle Database 11g plans in a SQL tuning set (STS) and import them into the Oracle database 11g. Then set the init.ora parameter optimizer_features_enable to 10, this is make the optimizer behave as if this was a 10g oracle database. Next run SQL Replay for the STS. Once that’s complete set the init.ora parameter optimizer_features_enable back to 11 and rerun SQL Replay for the STS. SQL Replay will produce a report that will list an SQL statement who’s plan have regressed from 10g to 11. For those SQL statements that are shown by SQL Replay to incur performance regression due to the new optimizer version, you can capture their plans using an STS and then load them into the SMB.
This method represents best form of plan seeding process because it helps in preventing performance regressions while preserving performance improvements upon database upgrade.
Database Upgrade – using tuning pack, guarantees plans are same
New Application Deployment – once you are running on 11g, no tuning pack
Possible SQL Plan Manageability Scenarios
Database upgrade:
- Bulk SQL plan loading is especially useful when the system is being upgraded from a pre-Oracle Database 11g version to Oracle Database 11g. For this, you can capture plans for a SQL workload into a SQL Tuning Set (STS) before the upgrade, and then load these plans from the STS into the SQL plan baseline immediately after the upgrade. This strategy can minimize plan regressions resulting from the use of the new optimizer version.
New Application Deployment:
- The deployment of a new application module means then introduction of brand new SQL statements into the system. The software vendor can ship the application software along with appropriate SQL plan baselines for new SQL being introduced. Because of the plan baselines the new SQL statements will initially run with the plans that are known to give good performance under a standard test configuration. However, if the customer system configuration is very different from the test configuration, the plan baselines can be evolved over time to produce better performance.
For both cases, after manual loading, you can use the automatic SQL plan capture to make sure that going forward, only better plans will be used for your applications.
© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- SQL PLAN MANAGEMENT
- SQL PLAN MANAGEMENT TRACING
- 11g新特性SQL执行计划管理(SQL Plan Management) (1)
- Internal_Function with Encryption in SQL PLAN
- HOW TO LOAD SQL PLANS INTO SPM FROM AWR
- HOW TO DROP PLANS FROM SPM REPOSITRY
- RAC System Test Plan Outline
- Automatic PGA Memory Management
- Using DBMS_SQL Package to Execute DDL Statements and Dynamic SQL from PL/SQL
- Automatic Memory Management(AMM) on 11g
最新评论