Changing Execution Plan of SQL Statement

To change execution plan of the sql statement. Collect the AWR report  and find out the SQL_ID. Create SQL Tuning set between the snapshots generated by database using SQL_ID

Creating STS from AWR info 

declare

cursor1 sys_refcursor

begin 

open cursor1  for

select value(P)

from table(

dbms_sqltune.select_workload_repository(begin_snao=>34,end_snap=>35, basic_filter=>’sql_id=''oraclewinning''',attribute_list=>'ALL')P;

dbms_sqltune.load_sqlset(sqlset_name=>'myfirstset',populate_cursor=>'cursor1')

close cursor1;

end;

/

verify newly created sql tuning contents 

select * from table(dbms_sqltune.select_sqlset(sqlset_name=>'myfirstset'));

Loading desired plan from STS to SQL Plan Baseline

declare

best_plans pls_integer;

begin

best_plans :=dbms_spm.load_plans_from_sqlset(

sqlset_name=>'myfirstset',

basic_filter=>'plan_hash_value  = ''oraclewinning''');

end;

/

when desired plan is loaded into the SQL PLAN BASELINE, In order to change the plan we have flush the current cursor in the library cache. We cab find existing cursor information with the help of SQL_ID

Find out sql_id of the existing sql in memory using v$sqlarea; Using v$sqlarea we can find out "sql_id" as follows 
select sql_id, force_matching_signature, sql_text from v$sqlarea where SQL_TEXT like '%Select max%';

using DBMS_XPLAN.DISPLAY_CURSOR we can find "sql_id" stored in memory 

SQL>select * from TABLE(dbms_xplan.display_cursor('SQL_ID'));

Flush the cache from memory and using dbms_shared_pool.purge 

SQL>exec dbms_shared_pool.purge('address','old_hash_value','C');

--Finally we can re-run sql- statements and check execution time / plan of the sql statements 

---NIKHIL TATINENI--
---Performance Tuning--