find sql's with multiple hash values in last one hour.

 --- Below code will tell us if we have multiple plans for a given sql_id when looked at a sql_id 

select sql_id,sum(executions_delta),avg(TPE),plan_hash_value,app from

(

SELECT sql_id,executions_delta,

round((elapsed_time_delta)/decode((executions_delta*1000000),0,1,(executions_delta*1000000)),6) TPE,plan_hash_value

,CASE(A.module) when 'JDBC Thin Client'  then 'JDBC' else A.module end app 

FROM  dba_hist_sqlstat A

JOIN  dba_hist_snapshot ss

  ON  A.snap_id = ss.snap_id

WHERE 

(ss.end_interval_time between (sysdate -1/24) and sysdate ) and 

 A.module not in ('em? agent_SQL_oracle_database','emagent_AQMetrics','Oracle Enterprise Manager.Metric Engine') 

  and rownum <200

order by 2 desc)

group by sql_id,plan_hash_value,app

order by 1 desc;

-- Details:-

SELECT sql_id,

to_char(begin_interval_time,'DD HH24:MI') begin_interval_time,to_char(ss.end_interval_time,'DD HH24:MI') end_interval_time,executions_delta,

round((elapsed_time_delta)/decode((executions_delta*1000000),0,1,(executions_delta*1000000)),6) TPE,plan_hash_value

,CASE(A.module) when 'JDBC Thin Client'  then 'JDBC' else A.module end app 

FROM  dba_hist_sqlstat A

JOIN  dba_hist_snapshot ss

  ON  A.snap_id = ss.snap_id

WHERE 

(ss.end_interval_time between (sysdate -1/24) and sysdate ) and 

 A.module not in ('em? agent_SQL_oracle_database','emagent_AQMetrics','Oracle Enterprise Manager.Metric Engine') 

 and rownum <200

order by 2 desc;