Example how to get SQLs from AWR which are performing slower than before

awr_sql_stat_changes.sql

with
 simple_data as (
   select 
     case when sn.end_interval_time > trunc(sysdate) then 1 else 0 end curr_period,
     sn.snap_id, 
     sql_id,
     plan_hash_value,
     force_matching_signature,
     optimizer_env_hash_value,
     parsing_schema_name,
   -- stats:
     executions_delta   as execs,
     elapsed_time_delta as ela,
     elapsed_time_delta   /executions_delta as elaexe,
     rows_processed_delta /executions_delta as r_processed,
     fetches_delta        /executions_delta as fetches,
     disk_reads_delta     /executions_delta as disk_reads,
     buffer_gets_delta    /executions_delta as buff_gets,
     cpu_time_delta       /executions_delta as cpu_time,
     iowait_delta         /executions_delta as io_time,
     plsexec_time_delta   /executions_delta as plsql_time,
     (clwait_delta + apwait_delta + ccwait_delta + javexec_time_delta)/executions_delta as other_time
   from dba_hist_sqlstat st
       ,dba_hist_snapshot sn
   where 
        sn.dbid = st.dbid and sn.instance_number = st.instance_number and sn.snap_id=st.snap_id
     and st.end_of_fetch_count_delta>0
     and st.executions_delta>0
)
,cur as (
   select *
   from (
      select 
          row_number()over(partition by sql_id order by snap_id desc) rn
         ,c.* 
      from simple_data c
      where curr_period=1
   )
   where rn = 1
)
,old as (
   select sql_id,plan_hash_value
         ,cast(collect(plan_hash_value) as ku$_objnumset) plan_hv_s
         ,min(elaexe) as elaexe_min
         ,avg(elaexe) as elaexe_avg
         ,max(elaexe) as elaexe_max
   from simple_data o
   where curr_period=0
   group by 
     sql_id,plan_hash_value
)
select 
   cur.sql_id 
  ,cur.plan_hash_value as plan_hv_cur
  ,old.plan_hash_value as plan_hv_old
  ,cur.elaexe

  ,old.elaexe_avg
  ,old.elaexe_min
  ,old.elaexe_max

  ,cur.force_matching_signature
  ,cur.optimizer_env_hash_value
  ,cur.parsing_schema_name
  ,cur.execs
  ,cur.ela
  ,cur.elaexe
  ,cur.r_processed
  ,cur.fetches
  ,cur.disk_reads
  ,cur.buff_gets
  ,cur.cpu_time
  ,cur.io_time
  ,cur.plsql_time
  ,cur.other_time
from cur
    ,lateral(
      select *
      from old
      where cur.sql_id = old.sql_id
        and (cur.plan_hash_value not member of (old.plan_hv_s) 
             or 
             cur.plan_hash_value = old.plan_hash_value
            )
     ) old
where
  (cur.elaexe > &p * old.elaexe_max)
  or 
  (cur.plan_hash_value != old.plan_hash_value and cur.elaexe > &p * old.elaexe_avg)
/