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;
/


If you like please follow and comment