How to find SQL with multiple execution plans in Oracle AWR

Compare every SQL_ID that has run under more than one PLAN_HASH_VALUE. Rank by ela_gain — the elapsed-time saving between the fastest plan and the average of all plans. Big ela_gain values are candidates for SQL plan baselines or profiles to lock in the good plan.

DBA_HIST_SQLSTAT is part of Oracle Diagnostic Pack and requires that license on Enterprise Edition.

SPOOL sql_with_more_than_1plan.txt
SET LINES 220 PAGES 9999 TRIMSPOOL ON
SET NUMFORMAT 999,999,999
COLUMN plan_hash_value FORMAT 99999999999999
COLUMN min_snap        FORMAT 999999
COLUMN max_snap        FORMAT 999999
COLUMN min_avg_ela     FORMAT 999,999,999,999,999
COLUMN avg_ela         FORMAT 999,999,999,999,999
COLUMN ela_gain        FORMAT 999,999,999,999,999

SELECT sql_id,
       MIN(min_snap_id) AS min_snap,
       MAX(max_snap_id) AS max_snap,
       MAX(DECODE(rw_num, 1, plan_hash_value)) AS plan_hash_value,
       MAX(DECODE(rw_num, 1, avg_ela))         AS min_avg_ela,
       AVG(avg_ela)                            AS avg_ela,
       AVG(avg_ela) - MAX(DECODE(rw_num, 1, avg_ela)) AS ela_gain,
       MAX(DECODE(rw_num, 1, sum_exec)) - 1    AS min_exec,
       AVG(sum_exec) - 1                        AS avg_exec
FROM (
  SELECT sql_id, plan_hash_value, avg_buffer_gets, avg_ela, sum_exec,
         ROW_NUMBER() OVER (PARTITION BY sql_id ORDER BY avg_ela) AS rw_num,
         min_snap_id, max_snap_id
  FROM (
    SELECT sql_id, plan_hash_value,
           SUM(buffer_gets_delta)   / (SUM(executions_delta) + 1) AS avg_buffer_gets,
           SUM(elapsed_time_delta)  / (SUM(executions_delta) + 1) AS avg_ela,
           SUM(executions_delta) + 1 AS sum_exec,
           MIN(snap_id) AS min_snap_id,
           MAX(snap_id) AS max_snap_id
    FROM   dba_hist_sqlstat a
    WHERE  EXISTS (SELECT 1 FROM dba_hist_sqlstat b
                   WHERE  a.sql_id           = b.sql_id
                   AND    a.plan_hash_value <> b.plan_hash_value
                   AND    b.plan_hash_value  > 0)
    AND    plan_hash_value > 0
    GROUP BY sql_id, plan_hash_value
  )
)
GROUP BY sql_id
HAVING MAX(DECODE(rw_num, 1, sum_exec)) > 1
ORDER BY 7 DESC;
SPOOL OFF
CLEAR COLUMNS
SET NUMFORMAT 9999999999