Fix the SQLID to use the particular best hash plan in Oracle

Choose the best SQL Plan for hash value and set the best plan for SQLID in Oracle.

-- Check the sqlid with different plan hash with their performance
	
SELECT T.SQL_ID,S.PLAN_HASH_VALUE,
         MIN (SN.BEGIN_INTERVAL_TIME)             BEGIN_INTERVAL_TIME,
         MAX (SN.END_INTERVAL_TIME)             END_INTERVAL_TIME,
         MAX (CAST (DBMS_LOB.SUBSTR (T.SQL_TEXT, 2000) AS NVARCHAR2 (2000)))             SQL_TEXT,
         SUM (S.EXECUTIONS_DELTA)                   EXECUTIONS,
         SUM (S.ELAPSED_TIME_DELTA)                 ELAPSED_TIME
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SQLTEXT T, DBA_HIST_SNAPSHOT SN
WHERE     T.SQL_ID = S.SQL_ID
         AND S.SNAP_ID = SN.SNAP_ID
         AND SN.BEGIN_INTERVAL_TIME >= SYSDATE - 1
         AND SN.END_INTERVAL_TIME <= SYSDATE
		 AND s.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM')
GROUP BY T.SQL_ID,S.PLAN_HASH_VALUE 
ORDER BY ELAPSED_TIME DESC


SELECT SQL_ID, PLAN_HASH_VALUE,
SUM(NVL(EXECUTIONS_DELTA,0)) EXECS, 
(SUM(ELAPSED_TIME_DELTA)/DECODE(SUM(NVL(EXECUTIONS_DELTA,0)),0,1,SUM(EXECUTIONS_DELTA))/1000000) AVG_ETIME
 FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SN
   WHERE     S.SNAP_ID = SN.SNAP_ID
   AND S.INSTANCE_NUMBER = SN.INSTANCE_NUMBER
   AND EXECUTIONS_DELTA > 0 
   AND ELAPSED_TIME_DELTA > 0 
   GROUP BY SQL_ID,PLAN_HASH_VALUE
   ORDER BY 1;
  

SELECT SQL_ID, COUNT(DISTINCT PLAN_HASH_VALUE) cnt
      FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SNAPSHOT SS
     WHERE STAT.SNAP_ID = SS.SNAP_ID AND
           SS.DBID = STAT.DBID AND
           SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND
           SS.BEGIN_INTERVAL_TIME > = SYSDATE-7 AND
           ss.END_INTERVAL_TIME<=sysdate AND
           STAT.PLAN_HASH_VALUE <> 0 AND
           STAT.executions_delta > 0 AND
           STAT.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM’)
    GROUP BY SQL_ID
    ORDER BY 1;

Reason of change the plan by SQLID:

Compare the SQL ID with a different plan. Choose the best plan on basics of AVG_ETIME and ELAPSED_TIME with help of the above queries output.
Note: the reason for fliping the plan is help v$sql_shared_cursor, it has reasons.

-- Check the value 'Y' is the reason of flipping the plan for sql id.
select * from v$sql_shared_cursor where sql_id = '';

Fix the SQLID to use the particular best plan manually with help of SQLT as follows:

First Method:
Choose the best plan by adding in SQL BASELINE:

-- Add the SQLID and PLAN HASH VALUE in SQL Baseline
var v_num number;
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '', plan_hash_value => '')
print v_num;

-- Check the baseline is created
select sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines;

Disable in SQL Baseline if not needed:

 To disable a baseline
var v_num number;
exec :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value',  plan_name => 'plan_name_value',  attribute_name=> 'enabled',  attribute_value=>'NO'); 
print v_num;

var v_num number;
EXEC  :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value',  plan_name => 'plan_name_value',  attribute_name=> 'fixed',  attribute_value=>'NO');  
print v_num; 

Another Method is to create sql_profile by using SQLT commands. Run the following script:

SQL> @START coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)
Enter value for 1:

Note: It ask for SQLID, Please put the SQL id here then it will show you the available plan_hash_value with AVG_ET_SECS

Parameter 2:  -- Choose the best hash sql plan for fix
PLAN_HASH_VALUE (required)
Enter value for 2:

Note: It will generate a file which you need to execute to fix the hash value plan for particular SQL ID. It will create a SQL Profile.

SQL> @coe_xfr_sql_profile_sqlid_planhashvalue.sql

Verify the SQL profile is created:

SELECT SQL_ID,SQL_PROFILE,PLAN_HASH_VALUE from v$SQL where SQL_ID = '';

Drop and Disable the SQL Profile for SQL query if you don’t need it:

-- For disable the profile:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name =>'',Attribute_name => 'STATUS', VALUE => 'DISABLE');

--For Drop the profile:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '');