Check execution time and plan for SQL ID from the history table in Oracle
dba_hist_active_sess_history table is used to fetch the information about SQL id
set linesize 150
col exec_start for a25
col exec_end for a25
col difference for a28
select instance_number, sql_exec_id, sql_plan_hash_value, exec_start, to_char(exec_end,'MM/DD/YYYY HH:MI:SS AM') exec_end, difference
from (
select instance_number, sql_exec_id, sql_plan_hash_value, to_char(sql_exec_start,'MM/DD/YYYY HH:MI:SS AM') as exec_start, max(sample_time) exec_end,
EXTRACT(HOUR FROM (max(sample_time) - sql_exec_start) DAY TO SECOND) || ' HH '
|| EXTRACT(MINUTE FROM (max(sample_time) - sql_exec_start) DAY TO SECOND) || ' MI '
|| EXTRACT(SECOND FROM (max(sample_time) - sql_exec_start) DAY TO SECOND) || ' SS' difference
from dba_hist_active_sess_history
where sql_id='&sql_id'
group by instance_number, sql_exec_id, sql_plan_hash_value, sql_exec_start
order by sql_exec_start );