SQL script to find SQL ID's having more than one hash plan
This will be for the last 7 days.
Script:
SELECT
sql_id, COUNT(DISTINCT plan_hash_value)
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
having COUNT(DISTINCT plan_hash_value) >1
ORDER BY
1;
PL/SQL to Compare if they are the same or different
Script:
SET SERVEROUTPUT ON
DECLARE
v_count number := 0;
CURSOR SQLID IS
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;
BEGIN
FOR I IN SQLID
loop
DBMS_OUTPUT.PUT_LINE ('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
IF I.cnt > 1 THEN
DBMS_OUTPUT.PUT_LINE ('Multiple plan for this sql :-'||I.sql_id||'. Checked for last 7 days. Please login to DB and do action item as needed ');
ELSE
DBMS_OUTPUT.PUT_LINE ('This sqlid '||I.sql_id||' has more than one plan, but the plan was not changed since last 7days');
END IF;
end loop;
END;
/
