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  :top_pct_sql);