Below script will display execution history of an sql_id from AWR. It will join dba_hist_sqlstat and dba_hist_sqlsnapshot table to get the required information.

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

DATABASE SCRIPTS


Related Topics

How to get tablespace quota details of an user in oracle
Find user commits per minute in oracle database
How to find execution history of an sql_id
Find sessions consuming lot of CPU
Find the active transactions in oracle database
Script to get cpu usage and wait event information in oracle database
Get sid from ospid
Find pending distributed pending transactions in oracle
How to monitor parallel queries in oracle db
How to find cpu and memory information of oracle database server

You May Also Like

Multinode Hadoop installation steps
REUSE_DUMPFILES parameter in EXPDP
how to send mail using utl_mail in oracle 11g
Perform Flashback in pluggable database(PDB) in oracle 12.2
Apply patch on oracle 12.2 database ( Release update)
How to install oracle client in silent mode using response file
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
How to get the execution plan for a SQL between two AWR snapshots
PDB Lockdown Profiles in Oracle 12.2
ENABLE_PARALLEL_DML hint in oracle 12c

From This Website

ORA-01536: space quota exceeded for tablespace
OGG-01028 partial record at sequence extract abended
Shell script to monitor asm diskgroup usage
How to recreate MGMT database in ORACLE 12C GRID
How to deinstall/cleanup standalone grid infrastructure
How to find cpu and memory information of oracle database server
SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp
How to get tablespace quota details of an user in oracle
Install VNC Server and Desktop on CENTOS6/RHEL 6
Shell script to delete old archives using RMAN