creating baselines from AWR for consistant performance and how to move them to another environments

1)
Here we are creating a BASELINE profiles from previous runs using AWR reports... if we think the execution plan in one database is working fine and want to have the same consistant execution plan for sql.

login as system user

1)

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'BASIC_PROC_NAME',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/

2)

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>4176, end_snap=>4179,basic_filter=>'sql_id = ''0cadrfdx9t0z1e''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'BASIC_PROC_NAME', populate_cursor=>cur);
  CLOSE cur;
END;
/



 SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'BASIC_PROC_NAME')
             );
           


3)

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'BASIC_PROC_NAME',
    basic_filter=>'plan_hash_value = ''4972313474'''
    );
END;
/

Make sure you see the row….

SELECT * FROM dba_sql_plan_baselines ;

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;

4) To backup the sql baseline to other environment... then continue.

exec DBMS_SPM.CREATE_STGTAB_BASELINE('SQL_PROF_BASELINE','SCHEMA_NAME');

select table_name,owner from dba_tables where table_name='SQL_PROF_BASELINE';

SELECT * FROM dba_sql_plan_baselines;
no rows initially

5)

var x number;
begin
:x := DBMS_SPM.PACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME', sql_handle => 'SQL_300901f0272d8837', plan_name => 'SQL_PLAN_30281y0mkv21r64b2d935' );
end;
/


6)

export ....

7)

Purge the existing which is not good....

exec DBMS_SHARED_POOL.PURGE ('0000000410E16308, 3635099784', 'C');

8)
import.... as system user

unpack...

7)

 var x number;
begin
:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME');
end;
 /

SELECT * FROM dba_sql_plan_baselines;

***************************** END **********************************



 DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle => 'SQL_300901f0272d8837'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/


Hope this Helps....