Oracle Baseline/ SQL plan migration to its DEV/QA/Clone
On Source database (source)====================
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;
/ 2 3 4 5 6 7
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
SQL>
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
=====================
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
