How do I migrate stored outlines to SQL Plan Management?

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.



Using  DBMS_SPM.MIGRATE_STORED_OUTLINES


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_value to 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.

Comments:

it seems to be not working in any conditions. Migrated outline is always rejected. Hash values don't match. An outline was created in 10.2.0.4, currently in 11.2.03

Posted by guest on May 22, 2012 at 07:02 AM PDT #

Hi Nickolay,

I don't fully understand the problem you are encountering. What hash value don't match? Do you mean Plan hash values? Plan hash values are not stored in stored outlines so this error message doesn't make sense to me. Can you please send the command you are using to do the migration and the exact error message you are receiving?

Thanks,
Maria

Posted by Maria Colgan on May 23, 2012 at 02:52 PM PDT #

Hi Maria

I just tried to use dbms_spm.migrate_stored_outline to migrate some outlines that I imported from 10.2.0.5 in 11.2.0.2. Unfortunately it didn't work and, honestly, with the information I received I do not know how to further investigate the problem. Here is what I did:

* 10.2.0.5

expdp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) content=data_only dumpfile=outlines.dmp

* 11.2.0.2

impdp full=y dumpfile=outlines.dmp

variable report clob
set long 1000000
exec :report := dbms_spm.migrate_stored_outline(attribute_name => 'all')
select :report from dual;

-------------------------------------------------------------------------------
Migrate Stored Outline to SQL Plan Baseline Report
-------------------------------------------------------------------------------

Summary:
--------
Number of stored outlines to be migrated: 3

Stored outlines migrated successfully: 0
Stored outlines failed to be migrated: 3

Summary of Reasons for failure:
-------------------------------
Number of invalid stored outlines: 3

Details on stored outlines not migrated or name changes durin
g migration:
-------------------------------------------------------------------------
* Notes on name change:
* New SQL plan baselines are assigned the same names as their original
* stored outlines. If a stored outline has the same name as an existing
* SQL plan baseline, a system generated name is used for the new
* SQL plan baseline.

Can you, please, advise how to investigate such a problem?

Best,
Chris

Posted by Chris Antognini on May 29, 2012 at 08:23 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
13
14
15
16
17
18
19
20
21
22
23
24
26
27
28
29
30
31
     
Today