steps to create baseline for a SQL statement in oracle database

1. -- Creating SQL tuning set to load better plan from AWR repository

exec dbms_sqltune.create_sqlset(sqlset_name => 'dz7j81n20pfm4_sqlset_test',description => 'sqlset descriptions');

2. -- Selecting historical SQL from AWR and loading that plan into tuning set

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||' and plan_hash_value=4245310385',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('dz7j81n20pfm4_sqlset_test', baseline_ref_cur);
end;
/

-- input values..

sql id : dz7j81n20pfm4
bein snap: 38451
end snap: 38454

3. -- check to see tuning set was created above.

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='dz7j81n20pfm4_sqlset_test';

4. -- Load SQL and good plan into baseline

set serveroutput on
declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'dz7j81n20pfm4_sqlset_test',
basic_filter => 'sql_id="dz7j81n20pfm4" and plan_hash_value =4245310385',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_int);
end;
/

5. -- verify baseline is created

SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE FROM   DBA_SQL_PLAN_BASELINES;