SQL script to find SQL ID's having more than one hash plan

 

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;

/