In this post, we will check out how to move SQL baseline from One Database to Another
How to move the sql baselines in Oracle
Suppose you have created a sql baseline in System A and Now you want to transfer that sql baseline to System B.
Step 1
On System A: Creating a staging table to store the SQL baseline
exec DBMS_SPM.CREATE_STGTAB_BASELINE(table_name=>'STAGE',schema_name=>'SCOTT');
Step 2
On System A: Pack the SQL baselines into the Staging Table
Pack all baselines in the database.
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT');
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/
Pack ALL Baselines Plans of a query
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT', sql_handle => '&sql_handle');
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/
Pack a specific Baseline Plan of a Query
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT', sql_handle => '&sql_handle', plan_name => '&plan_value' );
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/
Pack only ACCEPTED Baseline Plans of a Query
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT', sql_handle => '&SQL_HANDLE', accepted => 'YES' );
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/
Step 3
On System A: Export from Source Database
$ exp scott/tiger file=stage.dmp tables=stage
Step 4
On System B: Import the staging table
$ imp scott/tiger file=stage.dmp full=y
Step 5
On System B: Unpack Staging Table
If importing to the same schema, the schema owner does not need to be specified:
SQL> EXEC DBMS_SPM.UNPACK_STGTAB_BASELINE('STAGE','SCOTT');
Step 6
On System B: Check if the baseline is enabled
SELECT sql_handle, plan_name, origin, enabled, accepted FROM dba_sql_plan_baselines;
I hope you will find this article on How to move the sql baselines useful and helpful. Please do provide the feedback
Related Articles
SQL Tuning Set in Oracle
how to create sql baseline in Oracle 19c
how to create sql baseline from Sql tuning set in Oracle
How to drop the sql baseline in Oracle
how to create sql baseline from AWR repository without STS
