Create SQL Plan Baselines for High Resource Intensive SQL's from AWR - 12C

=> Identify SNAP ID's for the interval

Here I am collecting resource intensive SQL's ran on a week from Monday (12-SEP-22) to Friday (16-SEP-22)

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SITPDB                         READ WRITE NO

SQL> alter session set container=SITPDB;

Session altered.

SQL>
SQL>
SQL> select min(snap_id) from dba_hist_snapshot where trunc(begin_interval_time)='12-SEP-22';

MIN(SNAP_ID)
------------
        3324

SQL> select max(snap_id) from dba_hist_snapshot where trunc(begin_interval_time)='16-SEP-22';

MAX(SNAP_ID)
------------
        3439

SQL> select user from v$database;

USER
------------------------------
SYS
SQL>

=> Create an AWR Baseline

BEGIN
 DBMS_WORKLOAD_REPOSITORY.create_baseline (
 start_snap_id => 3324,
 end_snap_id => 3439,
 baseline_name => 'weekly_baseline_sep_12_16');
END;
/

PL/SQL procedure successfully completed.
SQL>

=> Create a SQL Tuning Set Object

BEGIN
 dbms_sqltune.create_sqlset(
 sqlset_name => 'weekly_awr_dev1'
 ,description => 'STS from AWR');
END;
/

PL/SQL procedure successfully completed.
SQL>

=> Populate the SQL Tuning Set with High-Resource Queries Found in AWR Baseline

Here I am populating top 20 SQL's based on elapsed_time

DECLARE
 base_cur dbms_sqltune.sqlset_cursor;
BEGIN
 OPEN base_cur FOR
 SELECT value(x)
 FROM table(dbms_sqltune.select_workload_repository(
 'weekly_baseline_sep_12_16', null, null,'elapsed_time',
 null, null, null, 20)) x;
 dbms_sqltune.load_sqlset(
 sqlset_name => 'weekly_awr_dev1',
 populate_cursor => base_cur);
END;
/

PL/SQL procedure successfully completed.
SQL>

To view the queries within the SQL tuning set, run below query 

select * from dba_sqlset_statements where sqlset_name = 'weekly_awr_dev1';

=> Use the Tuning Set As Input to DBMS_SPM to Create Plan Baselines for Each Query Contained in the SQL Tuning Set

DECLARE
 dev_plan1 PLS_INTEGER;
BEGIN
 dev_plan1 := dbms_spm.load_plans_from_sqlset(
 sqlset_name=>'weekly_awr_dev1');
END;
/

PL/SQL procedure successfully completed.
SQL>

Now each query in the SQL tuning set has an enabled plan baseline associated with it.

SQL> select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines order by elapsed_time desc;