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;

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
• Details of running concurrent requests in Oracle EBS - May 26, 2020
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020