ITRemote

-- Monitoring Active Session History
Col session_id format 99999
Col session_serial# format 999999
Col sample_id format 999999999
Col sample_time format a22
Col Elapes_Sec format 9999
Col sql_exec_start format a22
Col sql_plan_hash_value format 99999999999

Select ash.sample_id,ash.session_id,ash.session_serial#,ash.sql_id,ash.sql_plan_hash_value,
       cast(ash.sample_time as Date) sample_time,ash.sql_exec_start,
       (extract(hour from (ash.sample_time - ash.sql_exec_start))*60*60 +
        extract(minute from (ash.sample_time - ash.sql_exec_start))*60 +
        round(extract(second from (ash.sample_time - ash.sql_exec_start)))
        ) Elapes_Sec
 from V$ACTIVE_SESSION_HISTORY ash,
     (select max(sample_id) max_sample_id,session_id,session_serial#,sql_id,sql_plan_hash_value,sql_exec_start
        from V$ACTIVE_SESSION_HISTORY
       where sql_id = '&sql_id'
       group by session_id,session_serial#,sql_id,sql_plan_hash_value,sql_exec_start) ash2
where ash.session_id = ash2.session_id
  and ash.session_serial# = ash2.session_serial#
  and ash.sql_id = ash2.sql_id
  and ash.sql_plan_hash_value = ash2.sql_plan_hash_value
  and ash.sql_exec_start = ash2.sql_exec_start
  and ash.sample_id = ash2.max_sample_id
  and trunc(ash.sql_exec_start) >= trunc(sysdate-1)
order by 7
/