Hey Everyone,

 

Hope you’re fine.

 

This is a simple and short post, but extremely useful.

 

I was in a migration project and client used PreProd environment for stress test, code deploy, functional validations, etc. This PreProd environment have the sizing similar with Prod environment.

 

This PreProd also it was our “golden” environment in terms of code version and performance, we did some changes in the environment, including SQL Patches, SQL Profiles and SQL Baselines, resources that will bring us plan stability and some changes that are needed in execution plans.

 

How to ensure that we’ll have the same execution plans and behavior in Production? Well, that’s a good question with a simple answer: we’ll copy these resources from PreProd to Prod. This task is relatively simple.

 

PreProd Steps:

 

1) Create a Staging Table for the SQL Patches;
2) Pack the SQL Patches in the Staging Table;
3) Create a Staging Table for the SQL Profiles;
4) Pack the SQL Profiles in the Staging Table;
5) Create a Staging Table for the SQL Baselines;
6) Pack the SQL Baselines in the Staging Table;
7) Perform a DataPump Export of Staging Tables.

 

Prod Steps:

 

1) Perform a DataPump Import of Staging Tables;
2) Unpack the SQL Patches from Staging Table;
3) Unpack the SQL Profiles from Staging Table;
4) Unpack the SQL Baselines from Staging Table.

 

Just this!

 

The steps below are the ones described in the previous list:

 

-- PreProd

-- SQL Patches

exec dbms_sqldiag.create_stgtab_sqlpatch('SQLPATCH_STAGE','SYSTEM');

exec dbms_sqldiag.pack_stgtab_sqlpatch(staging_table_name=>'SQLPATCH_STAGE',staging_schema_owner=>'SYSTEM');

-- SQL Profiles

exec dbms_sqltune.create_stgtab_sqlprof('SQLPROFILE_STAGE','SYSTEM');

select 'exec dbms_sqltune.pack_stgtab_sqlprof(staging_table_name=>''SQLPROFILE_STAGE'',staging_schema_owner=>''SYSTEM'',profile_name=>'''||name||''');'

from dba_sql_profiles;

-- Baselines

exec dbms_spm.create_stgtab_baseline('SQLBASELINE_STAGE','SYSTEM');

DECLARE

my_plans number;

BEGIN

my_plans := dbms_spm.pack_stgtab_baseline(

table_owner => 'SYSTEM',

table_name => 'SQLBASELINE_STAGE',

enabled => 'yes',

accepted=> 'yes');

END;

/

-- Export

expdp "'/ as sysdba'" directory=qa_refresh dumpfile=exp_patches_profiles_baselines.dmp logfile=exp_patches_profiles_baselines.log tables=SYSTEM.SQLPATCH_STAGE,SYSTEM.SQLPROFILE_STAGE,SYSTEM.SQLBASELINE_STAGE

-- Prod

-- Import

impdp "'/ as sysdba'" directory=qa_refresh dumpfile=exp_patches_profiles_baselines.dmp logfile=imp_patches_profiles_baselines.log full=yes

-- SQL Patches

exec dbms_sqldiag.unpack_stgtab_sqlpatch(replace=>true,staging_table_name=>'SQLPATCH_STAGE',staging_schema_owner=>'SYSTEM');

-- SQL Profiles

exec dbms_sqltune.unpack_stgtab_sqlprof(replace=>true,staging_table_name=>'SQLPROFILE_STAGE',staging_schema_owner=>'SYSTEM');

-- Baselines

SET SERVEROUTPUT ON

DECLARE

l_plans_unpacked PLS_INTEGER;

BEGIN

l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(

table_name => 'SQLBASELINE_STAGE',

table_owner => 'SYSTEM');

DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);

END;

/
Hope this helps.


Peace,
 Vinicius