Creating SQL Plan Baselines Using APIs

Creating SQL plan baselines for regressed SQL statements with plan changes is another option to running the SQL Tuning Advisor. Doing so instructs the optimizer to use the original execution plans for these SQL statements in the future.

To create SQL plan baselines for the original plans:

1. Create a subset of a SQL tuning set of only the regressed SQL statements.

2. Create SQL plan baselines for this subset of SQL statements by loading their plans using the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package, as shown in the following example:

   DECLARE
     my_plans PLS_INTEGER;
   BEGIN
     my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
       sqlset_name => 'regressed_sql');
   END;
   /