Oracle 23c New Features for DBA'sFind the best way to find how much time the query (sql_id) has ran in past or current
ASH stores whenever a session is active every second in v$active_session_history for past two hours, In rotation one sample of every 10 seconds will be visible to dba_hist_active_session_history.
From v$active_session_history
select to_char(sample_time,'DD-MON-YY HH24') Time, sql_id,sql_plan_hash_value,sql_exec_id,round((count(*))/60/60, 2) as Hours
from v$active_session_history
where sql_id='fvv19kqad3vj3'
group by to_char(sample_time,'DD-MON-YY HH24'),sql_id,sql_plan_hash_value,sql_exec_id
order by 4;
From dba_hist_active_sess_history (if query has executed sometime back more than 2 hours)
select to_char(sample_time,'DD-MON-YY HH24') Time, sql_id,sql_hash_value,sql_exec_id,round((count(*) * 10)/60/60, 2) as Hours
from dba_hist_active_sess_history
where sql_id='fvv19kqad3vj3'
group by to_char(sample_time,'DD-MON-YY HH24'),sql_id,sql_hash_value,sql_exec_id
order by 4;
Sample Output
Follow Me!!!