Last10 execution of the SQL query

set lines 132

col sql_id head "SQL ID"

col exec_time head "Exec Time (min)"

col exec_date head "Exec Date"

col sid head "SID"

col status head "Status"

accept sql_id prompt 'Enter SQL ID: '  -- User Input for SQL ID

select rownum, 

       s.sid, 

       q.sql_id, 

       to_char(q.first_load_time, 'mm/dd/yy hh24:mi:ss') as exec_date, 

       round(q.execution_time/60, 2) as exec_time,  -- Execution time in minutes

       ses.status  -- Status of the session

from (

  select sql_id, 

         first_load_time,

         execution_time  -- Total execution time in seconds

  from v$sql_statistics

  where sql_id = '&sql_id'

  order by first_load_time desc

) q

left join v$session s on s.sql_id = q.sql_id

left join v$session ses on ses.sid = s.sid

where rownum <= 10;  -- Display only the last 10 executions