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

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

You May Also Like

Script to get cpu usage and wait event information in oracle database
OGG-00665 OCI Error describe for query
Flashback primary database in dataguard environment
Oswatcher tool for collecting server diagnostic information
Apply patch on oracle 12.2 database ( Release update)
Find pending distributed pending transactions in oracle
sec_case_sensitive_logon parameter in oracle
TNS-12542: TNS:address already in use
Oracle database Security Assessment Tool
Generate custom report from OEM cloud control

From This Website

Oracle database Security Assessment Tool
Shell script to monitor asm diskgroup usage
How to run expdp in pluggable database(PDB)
TRUST_EXISTING_TABLE_PARTITIONS in oracle 12.2 datapump
how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c
How to recreate physical standby controlfile
sec_case_sensitive_logon parameter in oracle
Shell script to monitor goldengate process
How to enable active dataguard in physical standby database
Setting up Table replication in oracle goldengate