SELECT inst_id,
SNAP_END_TIME AS SNAP_TIME,
HOURMIN,
ROUND((VALUE - LAST_VALUE) / 1000000 / 60, 2) DB_TIME,
ROUND(ROUND((VALUE - LAST_VALUE) / 1000000 / 60, 2) /
ROUND((TO_DATE(SNAP_END_TIME, 'yyyy-mm-dd hh24:mi:ss') -
TO_DATE(SNAP_BEGIN_TIME, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),2) "DB_TIME/ELAPSED_TIME"
FROM (select to_char(end_interval_time, 'yyyy-mm-dd hh24:mi:ss') SNAP_END_TIME,
to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') SNAP_BEGIN_TIME,
to_char(end_interval_time, 'hh24:mi') HOURMIN,
a.instance_number inst_id,
a.value,
case
when b.startup_time = b.begin_interval_time then
0
else
lead(a.value) over(order by b.snap_id desc)
end last_value
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.stat_name = 'DB time'
and a.dbid = (select dbid from v$database)
and a.instance_number = b.instance_number
and a.snap_id = b.snap_id
and b.END_INTERVAL_TIME >= sysdate - 5
and a.instance_number = 1)
WHERE LAST_VALUE IS NOT NULL
order by 3, 2 desc;