Thanks to SQL Plan Management (SPM), originally discussed in our January post, you can handle the upgrade and the migration with ease. By loading the original RBO plans into SPM you can ensure the Optimizer won’t change the execution plans during an upgrade and the implicit migration from RBO to CBO.
In fact, there are two possible approaches you can take.
Option 1 – Bulk loading SPM
Typically when we discuss bulk loading plans into SPM we use SQL Tuning Sets (introduced in Oracle Database 10g) to capture the existing execution plans. However, in Oracle Database 9i we didn’t have SQL Tuning Sets so the only way to capture the existing execution plan is to use Stored Outlines. There are two ways to capture Stored Outlines, you can either manually create one for each SQL statement using the CREATE OUTLINE command (can be tricky) or let Oracle automatically create a Stored Outline for each SQL statement that is executed. Below are the steps needed to let Oracle automatically create the Stored Outlines for you and then migrate them to SPM.
1. Start a new session and issue the following command to switch on the automatic capture of a Stored Outline for each SQL statement that gets parsed from now on until you explicitly turn it off.
SQL > ALTER SYSTEM set CREATE_STORED_OUTLINES=OLDPLAN;
NOTE: Ensure that the user for which the Stored Outlines are to be created has the CREATE ANY OUTLINE privilege. If they don’t the Stored Outlines will not be captured.
2. Now execute your workload either by running your application or manually issuing SQL statements.
NOTE: if you manually issue the SQL statements ensure you use the exact SQL text used by the application, if it uses bind variables you will have to use them also.
3. Once you have executed your critical SQL statements you should turn off the automatic capture by issuing the following command:
SQL > ALTER SYSTEM set CREATE_STORED_OUTLINES=false;
4. To confirm you have captured the necessary Stored Outlines issue the following SQL statement.
SQL> SELECT name, sql_text, category FROM user_outlines;
NOTE: Each Stored Outline should have the OLDPLAN category.
5. The actual Stored Outlines are stored in the OUTLN schema. Before you upgrade you should export this schema as a backup.
exp outln/outln file=soutline.dmp owner=outln rows=y
6. Once upgraded to Oracle Database 11g Release 2 you can migrate stored outlines for one or more SQL statements to SQL plan baselines using DBMS_SPM.MIGRATE_STORED_OUTLINE or through Oracle Enterprise Manager (EM). You can specify which stored outline(s) to be migrated based on the outline name, SQL text, or outline category, or migrate all stored outlines in the system to SQL plan baselines. Below is an example of migrating all of the plan for the OLDPLAN category and an example of migrating all Stored Outlines in the OH schema.
SQL> variable report clob;
-- Migrate the Stored Outlines in the OLDPLAN category
SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name=>'CATEGORY',
attribute_value => 'OLDPLAN');
-- Migrate all Stored Outlines
SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name=>'ALL');
Option 2 – Automatically loading SPM
If you are unable to capture Stored Outlines for all of your SQL statements prior to the upgrade, you can set the parameter OPTIMIZER_MODE to RULE and automatically capture the RBO plan into SPM after the upgrade. Below are the necessary steps to automatically capture the RBO plans into SPM.
NOTE: OPTIMIZER_MODE=RULE is not supported in Oracle Database 11g; the code has only been left in Oracle to enable easy migrations from RBO to CBO. We do not recommended you use OPTIMIZER_MODE=RULE as a long term strategy in Oracle Database 11g as the code can be removed at any time now that it is de-supported.
1. Enable RBO by setting the init.ora parameter OPTIMIZER_MODE to RULE.
SQL > ALTER SYSTEM set OPTIMIZER_MODE=RULE;
2. Enable automatic plan capture by setting the init.ora parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (default FALSE). With automatic plan capture enabled, the SPM repository will be automatically populated for any repeatable SQL statement.
SQL > ALTER SYSTEM set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
3. Wait for SPM to capture SQL statements executed by the application.
4. Once all of the SQL plan baselines have been created using the RBO plan, switch automatic plan capture back to the default value by setting the init.ora parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.
SQL > ALTER SYSTEM set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
5. Switch from RBO to CBO by setting init.ora parameter OPTIMIZER_MODE to ALL_ROWS (default). Although CBO might produce alternative execution plans those plans will not be used until they have been verified to perform as well as or better than the existing RBO plans.
SQL > ALTER SYSTEM set OPTIMIZER_MODE=ALL_ROWS;
It is possible to verify each of the new CBO plans manually using, either the PL/SQL package DBMS_SPM or Enterprise Manager DB Control or you can set up a DBMS Scheduler job to check for the presence of new execution plans and automatically run the verification job for these plans.
More information on what to expect from the Optimizer when upgrading to Oracle 11g can be found in this whitepaper and more information on SQL Plan Management can be found in this whitepaper.
