Skip to content

Move SQL Plan Baseline to other Instance

Oracle Database supports the export and import of SQL plan baselines using the Oracle Data Pump Import and Export utilities. Use the DBMS_SPM package to define a staging table, which you can use to pack and unpack SQL plan baselines.

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

STEP 1) create a staging table

Use the CREATE_STGTAB_BASELINE procedure.

SQL> BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage');
END;
/

PL/SQL procedure successfully completed.

 

STEP 2) Pack the SQL plan baselines

Pack the sql plan baseline that you want to export from the SQL management base into the staging table using the PACK_STGTAB_BASELINE function.

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

DECLARE
  my_plans number;
BEGIN
  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'stage',
    enabled => 'yes',
    SQL_HANDLE => 'SQL_5bf39dfrta1a5b89');
END;
/

STEP 3) Export the staging table

Export the table (in our case stage table) into a flat file using the Oracle Data Pump Export utility.

$ sqlplus / as sysdba
SQL> Grant EXEMPT ACCESS POLICY to system;
SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/usr/users/oadba';
SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
SQL> exit;


$ expdp system/<password> tables=stage directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log compression=all

Or

Use export command exp

exp system/<password>  file=stage.dmp tables=stage

 

STEP 4) Transfer the flat file to the target system.

Using secure copy or other method copy the generated stage.dmp to target system

 

STEP 5) Import the staging table

Import the staging table from the flat file using the Oracle Data Pump Import utility.

imp system/<password> file=stage.dmp tables=stage

 

STEP 6 ) Unpack the SQL plan baselines

Unpack the SQL plan baseline from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function.

The following example unpacks all fixed plan baselines stored in the staging table stage:

sqlplus system/<password>

SET SERVEROUTPUT ON
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'STAGE',
    table_owner     => 'SYSTEM',
    creator         => 'SYSTEM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

Plans Unpacked: 1
PL/SQL procedure successfully completed.

 

STEP 7) VERIFY

Verify if SQL plan baseline got imported successfully

select * from dba_sql_plan_baselines;

 

[Post Views: 525]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

4 Comments

  1. sam sam

    Mentioned wrong table name in step -1
    It should be stage not stage1.

    • sam sam

      By d way, you explained so nicely with example.

    • Brijesh Gogia Brijesh Gogia

      Thanks. Corrected Typo.

  2. Manoj Kumar Patro Manoj Kumar Patro

    Can I migrate a baseline from 11.2.0.4 to an Oracle 12 database?

Leave a Reply to Brijesh Gogia Cancel reply

Required fields are marked *

error: Alert: Content is protected !!