SQL Plan baseline from AWR

One of the concurrent program in EBS Development region was reported as running slow and the issue was fixed after i created SQL plan baselines after taking up better execution plan for 2 sql_id in AWR history reports.   Below are the steps

SQL_ID             PLAN_HASH          RESPONSE_IN_SEC

-----------           -----------           ----------------

36qn50vfsgzjh    2709963026         .000257534  AWR history  --  36,750 exec

36qn50vfsgzjh    584524445           .509160245  cursor memory --  23,544 exec

a07y05uya8653  1271677127          .000286352  AWR history    --  17,356 exec   

a07y05uya8653  4293861213          .681914067  cursor memory - 14398 exec

create sql tuning set  ( RUN all below step as SYS user)

exec dbms_sqltune.create_sqlset(sqlset_name => 'a07y05uya8653_sqlset_dev2',description => 'dev2_ebstax_sql2');

Load the sql plan from AWR & use filter option for any specific plan hash value

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)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('a07y05uya8653_sqlset_dev2', baseline_ref_cur);

end;

/

OR

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=1271677127',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('a07y05uya8653_sqlset_dev2', baseline_ref_cur);

end;

/

Verify sql plan value

select * from table(dbms_xplan.display_sqlset('a07y05uya8653_sqlset_dev2','&sql_id'));

Load into baseline from sql tuning set

set serveroutput on

declare

my_int pls_integer;

begin

my_int := dbms_spm.load_plans_from_sqlset (

sqlset_name => 'a07y05uya8653_sqlset_dev2',

sqlset_owner => 'SYS',

fixed => 'NO',

enabled => 'YES');

DBMS_OUTPUT.PUT_line(my_int);

end;

/

SQL> SELECT SQL_HANDLE,CREATOR,  PARSING_SCHEMA_NAME, CREATED, ENABLED, ACCEPTED, FIXED, module FROM   DBA_SQL_PLAN_BASELINES order by created;

SQL_HANDLE                     CREATOR    PARSING_SCHEMA_NAME            CREATED                        ENA ACC FIX MODULE

------------------------------ ---------- ------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------

SQL_8d714864a2f6cad0           SYS        APPS                           14-AUG-17 05.13.28.000000 PM   YES YES NO  ARHGNRW

SQL_851abbb354b962d0           SYS        APPS                           14-AUG-17 05.21.43.000000 PM   YES YES NO  ARHGNRW

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name in ('36qn50vfsgzjh_sqlset_dev2','a07y05uya8653_sqlset_dev2');

NAME                           OWNER                          CREATED                        STATEMENT_COUNT

------------------------------ ------------------------------ ------------------------------ ---------------

36qn50vfsgzjh_sqlset_dev2      SYS                            14-AUG-17                                    1

a07y05uya8653_sqlset_dev2      SYS                            14-AUG-17                                    1

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_8d714864a2f6cad0',format=>'basic'));

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_851abbb354b962d0',format=>'basic'));

Drop sql tuning set if required

BEGIN

  DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'a07y05uya8653_sqlset_dev2' );

END;

/