How do I migrate stored outlines to SQL Plan Management?
By Maria Colgan-Oracle on Jul 12, 2011
Stored outlines were deprecated in Oracle Database 11g and even though we have done a number of posts about SQL Plan Management (SPM) we have never discussed the reasons why you should migrate stored outlines to SQL plan baselines and the steps necessary to do the migration. It is strongly recommended that you migrate existing stored outlines to SPM because:
- SQL plan baselines allow multiple execution plans per SQL statement. Different plans may be optimal under different circumstances (different session parameters etc)
- If a plan becomes unusable (because an index is dropped) SPM will not use the existing SQL plan baseline. A new plan will be used. However, if a stored outline was being used the same plan would be used even if the index is gone and it is now a suboptimal plan
- SPM allows the optimizer to continue to find better execution plans, which can be verified and then added as accepted SQL plan baselines
You can migrate stored outlines to SQL plan baselines using either the PL/SQL function DBMS_SPM.MIGRATE_STORED_OUTLINE or through Oracle Enterprise Manager (EM). The steps for each approach are shown below.
The DBMS_SPM.MIGRATE_STORED_OUTLINE function migrates stored outlines for one or more SQL statements to
SQL plan baselines. You can specify which stored outline(s) to migrate based on outline name, SQL text, outline category, or migrate all stored outlines in the system. Let's assume we want to migrate the stored outline for the following simple query against the SH schema.
Select p.prod_name, s.amount_sold
From Sales s, Products p
Where s.prod_id = p.prod_id;
First we will need to know the name of the stored outline for this query. We can find this by querying user_outlines
Once we have the name of the stored outline, we need to call the DBMS_SPM.MIGRATE_STORED_OUTLINE function. The function takes three arguments,
- Attribute type - which specifies the type of parameter used in
attribute_valueto identify the migrated stored outlines. It is case insensitive and the possible values are, outline_name, sql_text, category, or all.
- Attribute value - which can be the name of stored outline to be migrated, the SQL text of stored
outlines to be migrated, the name of the category of stored outlines to
be migrated or NULL if attribute_name is all.
- Fixed - which specifies if the stored outline should become a fixed SQL plan baseline or not. The default is NO meaning the stored outline will not become a fixed SQL plan baseline.
The DBMS_SPM.MIGRATE_STORED_OUTLINE function returns a migration report in the form of a clob, so you must declare a SQL*Plus variable to catch the migration report. Once you have run the function you can view the report by selecting your variable name from dual.
From the report we can see one stored outline was successfully migrated. Successfully migrated stored outlines are marked as migrated and are no longer used, the SQL plan baseline will be used from now on. You can confirm this by querying the migrated column in user_outlines.
We can also confirm that the stored outline is now a SQL plan baseline by querying dba_sql_plan_baselines.
From dba_sql_plan_baselines we can see that there is a new SQL plan baseline for our query and the plan name is the same as the original stored outline name and the origin column says that this plan came from a stored outline. We can also see that this SQL plan baseline is both enabled and accepted so it is ready for use. We can confirm our query is using the SQL plan baseline by checking the note section of the execution plan.
Since the stored outline will no longer be used it can be dropped using the DROP OUTLINE command.
Using Enterprise Manager
Migrating stored outlines to SQL plan baselines is very easy using Enterprise Manager (EM). Support for migration has been built directly into the SQL plan baseline tab on the SQL Plan Control page. If any stored outline exist on the system, a message will appear at the top of the SQL plan baseline tab asking you whether you wish to migrate them to SQL plan baselines. A migrate button will also appear next to the message. You can initiate the migration process by simply clicking this migrate button.
Clicking the migrate button will trigger another screen to open that allows you to select which stored outlines should be migrated. By default all of the stored outlines found on the system will be migrated (note, disabled stored outlines will not be migrated). Alternatively, you can select the stored outline to be migrated based on their category, name or their corresponding SQL text.
In this example we will take the default and migrate all existing stored outlines, so we simple click the migrate button. This will trigger a migration summary screen to appear. The summary shows which stored outlines will be migrated and offers you the option of running the job immediately or scheduling it for a later date.
In this case we are going to run the job immediately so after confirming the two stored outlines to be migrated are correct, we click on the submit button. This will begin the migration process and you will see a spinning icon on the screen while the job completes. Once the job is done you will get a migration report just like the one you get with the DBMS_SPM.MIGRATE_STORED_OUTLINE function.
The report tells you how many stored outlines were scheduled to be migrated and how many were successfully migrated. All stored outlines should be successfully migrated unless the plan they produce in Oracle Database 11g is not legal. To see the new SQL plan baselines for the migrated stored outlines, click on the OK button. This will return you to the SQL plan baseline tab of the SQL Plan Control page.
At the bottom of the SQL plan baseline tab, there is a table that shows all of the SQL plan baselines that exist. Here you can see our two stored outlines have been migrated to SQL plan baselines. The plan name reflects the original name of the stored outline and the origins field shows that these plans were created from stored outlines, just as it did with the DBMS_SPM.MIGRATE_STORED_OUTLINE function.