OUTLINE转换为SPM

----根据CATAGORY转化
declare
 migrate_out clob;
begin
migrate_out := dbms_spm.migrate_stored_outline(attribute_name => 'CATEGORY',attribute_value => 'SPECIAL',fixed => 'YES');
end;


----根据OUTLINE_NAME转化
declare
 migrate_out clob;
begin
 migrate_out := dbms_spm.migrate_stored_outline(attribute_name => 'outline_name',attribute_value => 'AUTO_20170328_153729_OLD',fixed => 'YES');
 end;
--- 根据PLAN_NAME查找一下(PLAN_NAME就是OUTLINE NAME)
 select * from dba_sql_plan_baselines
where plan_name ='AUTO_20170328_153729_OLD';
 


---确认已经转换
select * from dba_outlines
where category='SPECIAL';




select * from dba_sql_plan_baselines
where plan_name in (select name from dba_outlines
where category='SPECIAL')




---










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.






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.