Oracle Baseline/ SQL plan migration to its DEV/QA/Clone

In this article I have explained step by step how to copy/migrate SQL baseline from source database to target database.

On Source database

============ 

Check the list of SQL plan baselines on Source

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;


SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

Create the staging table to hold the SQL baseline

SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name > 'spm_staging_tab',
table_owner > 'BIASDBA',
tablespace_name > 'TOOLS');
END;


PL/SQL procedure successfully completed.

Check the baseline staging table

SQL> desc biasdba.spm_staging_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
VERSION NUMBER
SIGNATURE NUMBER
SQL_HANDLE VARCHAR2(30)
OBJ_NAME VARCHAR2(30)
OBJ_TYPE VARCHAR2(30)
PLAN_ID NUMBER
SQL_TEXT CLOB
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(30)
DESCRIPTION VARCHAR2(500)
DB_VERSION VARCHAR2(64)
CREATED TIMESTAMP(6)
LAST_MODIFIED TIMESTAMP(6)
LAST_EXECUTED TIMESTAMP(6)
LAST_VERIFIED TIMESTAMP(6)
STATUS NUMBER
OPTIMIZER_COST NUMBER
MODULE VARCHAR2(64)
ACTION VARCHAR2(64)
EXECUTIONS NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
END_OF_FETCH_COUNT NUMBER
CATEGORY VARCHAR2(30)
SQLFLAGS NUMBER
TASK_ID NUMBER
TASK_EXEC_NAME VARCHAR2(30)
TASK_OBJ_ID NUMBER
TASK_FND_ID NUMBER
TASK_REC_ID NUMBER
INUSE_FEATURES NUMBER
PARSE_CPU_TIME NUMBER
PRIORITY NUMBER
OPTIMIZER_ENV RAW(2000)
BIND_DATA RAW(2000)
PARSING_SCHEMA_NAME VARCHAR2(30)
COMP_DATA CLOB

Now pack the baselines / load the baselines to the staging table created above

SQL> SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
SQL> 2 3 BEGIN
4 l_plans_packed : DBMS_SPM.pack_stgtab_baseline(
table_name > 'spm_staging_tab',
5 6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
9 10 /

Plans Packed: 3
PL/SQL procedure successfully completed.

Check the baseline table after loading

SQL> select sql_handle from BIASDBA.spm_staging_tab;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db


On target database
============

Copy required table to target database.

Create the DB link to the source database ( If the DBlink not an option then use export / import to copy the baseline staging table data to target).

SQL> create public database link efipfqa1 connect to biasdba identified by biasdba using 'efipfqa1';

Check the DB link is working or not

SQL> select SQL_HANDLE from biasdba.spm_staging_tab@efipfqa1;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db

Create the baseline staging table using the source database baseline staging table data

SQL> create table biasdba.spm_staging_tab as select * from biasdba.spm_staging_tab@efipfqa1;Table created.

Now Unpack / unload the baseline to the database dictionary
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_unpacked PLS_INTEGER;
3 BEGIN
l_plans_unpacked : DBMS_SPM.unpack_stgtab_baseline(
4 5 table_name > 'spm_staging_tab',
6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
9 END;
10 /

Plans Unpacked: 3
PL/SQL procedure successfully completed.

Check the baselines created from the unload operation above

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;

SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!