Exporting And Importing SQL Plan Baselines In Oracle 11g

Oracle Database supports the export and import of SQL plan baselines using its import and export utilities or Oracle Data Pump. Use the DBMS_SPM package to define a staging table, which is then used to pack and unpack SQL plan baselines.

To import a set of SQL plan baselines from one system to another:

On the original system, create a staging table using the CREATE_STGTAB_BASELINE procedure:

BEGIN

DBMS_SPM.CREATE_STGTAB_BASELINE(

table_name => ‘stage1’);

END;

/

This example creates a staging table named stage1.

Pack the SQL plan baselines you want to export from the SQL management base into the staging table using the PACK_STGTAB_BASELINE function:

DECLARE

my_plans number;

BEGIN

my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(

table_name => ‘stage1’,

enabled => ‘yes’,

creator => ‘dba1’);

END;

/

This example packs all enabled plan baselines created by user dba1 into the staging table stage1. You can select SQL plan baselines using the plan name (plan_name), SQL handle (sql_handle), or by any other plan criteria. The table_name parameter is mandatory.

1. Export the staging table stage1 into a flat file using the export command or Oracle Data Pump. –>Exp system/orapw4db  tables=STAGE1 file==stage1_baseline.dmp log=exp_basline.log
2. Transfer the flat file to the target system.
3. Import the staging table stage1 from the flat file using the import command or Oracle Data Pump.  –>    imp system/orapw4db fromuser=SYSTEM touser=SYSTEM tables=STAGE1 file=stage1_baseline.dmp log=imp_basline
4. Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function:

DECLARE

my_plans number;

BEGIN

my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(

table_name => ‘stage1’,

fixed => ‘yes’);

END;

/

Once afer unpack just issue the below query to see the baseline in the database.

SQL> select SQL_HANDLE,SQL_TEXT,ENABLED,ACCEPTED,FIXED FROM dba_sql_plan_baselines;

SQL_HANDLE

——————————

SQL_TEXT

——————————————————————————–

ENA ACC FIX

— — —

SQL_e1ce5b34a129b51e

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO “RMS”.”ORG14_CUSTPRODUCTPRICES” select

YES YES YES