SQL ID is using different SQL PLAN causing performance in Oracle

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 => '');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.