AWR Queries - Getting Information about SQL

Quick overview of SQL Performance for 1 SQL_ID
This query will show you AWR activity captured for a particular SQL_ID. Output includes the capture interval, plan hash value, number of executions, and the elapsed per exec. It is useful for getting an idea of how a SQL statement is performing and locating volatile explain plans.

select TO_CHAR(begin_interval_time,'MM/DD/YYYY HH24:MI') as BEGIN_INTERVAL, 
       TO_CHAR(end_interval_time,'MM/DD/YYYY HH24:MI') as END_INTERVAL, 
       sql_id, 
       plan_hash_value, 
       executions_total, 
       ROUND(elapsed_time_total/1000000,2) as elapsed,
       ROUND((elapsed_time_total/1000000)/executions_total,2) as elapsed_per_exec
from dba_hist_sqlstat join dba_hist_snapshot on dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id 
where sql_id IN (':sql_id')
ORDER BY DBA_HIST_SQLSTAT.SNAP_ID DESC;

SQL By Elapsed Time from AWR Data

-- SQL By Elapsed Time from AWR Data
--
-- :dbid     - Database ID
-- :instance_number - Instance number (usually 1)
-- :begin_snap_id      - Beginning Snap Id
-- :end_snap_id      - Ending Snap Id
-- :max_statement_count - Max Number of statements to return

WITH
-- Gather SQL Statistics for range 
sqlstat as (select sql_id, 
                   max(module) module, 
                   sum(elapsed_time_delta) elap, 
                   sum(cpu_time_delta) cput, 
                   sum(executions_delta) exec 
              from dba_hist_sqlstat 
             where dbid = :dbid 
               and instance_number = :inst_num 
               and :bid < snap_id 
               and snap_id <= :eid 
             group by sql_id),
-- Calculate DB Time for range
dbtimecalc as ( SELECT ((select value From DBA_HIST_SYS_TIME_MODEL where stat_name='DB time' and snap_id = :eid) -
                   (select value From DBA_HIST_SYS_TIME_MODEL where stat_name='DB time' and snap_id = :bid)) dbtime
                   FROM DUAL  
          )
select * 
  from (select nvl((sqlstat.elap/1000000), to_number(null)) ElapsedTimeSecs, 
               nvl((sqlstat.cput/1000000), to_number(null)) CPUTimeSecs, 
               sqlstat.exec TotalExecutions, 
               decode(sqlstat.exec, 0, to_number(null), (sqlstat.elap / sqlstat.exec / 1000000)), 
               (100 * (sqlstat.elap / (SELECT dbtime FROM dbtimecalc))) norm_val, 
               sqlstat.sql_id, 
               to_clob(decode(sqlstat.module, null, null , 'Module: ' || sqlstat.module)), 
               nvl(sqltext.sql_text, to_clob('** SQL Text Not Available **')) 
          from sqlstat, 
               dba_hist_sqltext sqltext 
         where sqltext.sql_id(+) = sqlstat.sql_id 
           and sqltext.dbid(+) = :dbid 
         order by nvl(sqlstat.elap, -1) desc, sqlstat.sql_id) 
 where rownum <= :tsql_max 
--   and (rownum <= :tsql_min or norm_val > :top_pct_sql);