Last active
June 11, 2019 13:11
-
-
Save fljdin/adc7cf8b5fe64e871e3f661ead97bc03 to your computer and use it in GitHub Desktop.
Get dbtime (seconds) from statspack or awr
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Average DB Time over the past week | |
select instance_number, avg(dbtime_min) avg_dbtime_min | |
from ( | |
select a.instance_number, begin_interval_time, end_interval_time, | |
round((a.value-lag(a.value) over (order by a.instance_number, a.snap_id ))/1000000/60,2) dbtime_min | |
from dba_hist_sys_time_model a, dba_hist_snapshot b | |
where a.snap_id = b.snap_id and a.instance_number = b.instance_number | |
and a.stat_name = 'DB time' | |
) | |
where begin_interval_time between trunc(sysdate - 7) and trunc(sysdate) and dbtime_min > 0 | |
group by instance_number; | |
-- DB time per hour over the past week | |
set pages 100 | |
select d.name, instance_number, to_char(begin_interval_time, 'HH24'), avg(dbtime_min) avg_dbtime_min | |
from ( | |
select a.instance_number, begin_interval_time, end_interval_time, | |
round((a.value-lag(a.value) over (order by a.instance_number, a.snap_id ))/1000000/60,2) dbtime_min | |
from dba_hist_sys_time_model a, dba_hist_snapshot b | |
where a.snap_id = b.snap_id and a.instance_number = b.instance_number | |
and a.stat_name = 'DB time' | |
), v$database d | |
where begin_interval_time between trunc(sysdate - 7) and trunc(sysdate) and dbtime_min > 0 | |
group by d.name, instance_number, to_char(begin_interval_time, 'HH24') | |
order by 2, 3; | |
-- DB time per day over the past week | |
select d.name, instance_number, to_char(begin_interval_time, 'D'), avg(dbtime_min) avg_dbtime_min | |
from ( | |
select a.instance_number, begin_interval_time, end_interval_time, | |
round((a.value-lag(a.value) over (order by a.instance_number, a.snap_id ))/1000000/60,2) dbtime_min | |
from dba_hist_sys_time_model a, dba_hist_snapshot b | |
where a.snap_id = b.snap_id and a.instance_number = b.instance_number | |
and a.stat_name = 'DB time' | |
), v$database d | |
where begin_interval_time between trunc(sysdate - 7) and trunc(sysdate) and dbtime_min > 0 | |
group by d.name, instance_number, to_char(begin_interval_time, 'D') | |
order by 2, 3; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select r.*, round(dbtime / elapsed, 2) * 100 rel_dbtime_pct from ( | |
select t.instid, round((e.value - b.value)/1000000,1) dbtime, | |
extract(day from elapsed_interval) * 86400 + extract(hour from elapsed_interval) * 1440 | |
+ extract(minute from elapsed_interval) * 60 + extract(second from elapsed_interval) as elapsed | |
from (select dbid, instance_number instid, min(snap_id) begin_snap, max(snap_id) end_snap, | |
max(end_interval_time)-min(begin_interval_time) elapsed_interval | |
from dba_hist_snapshot group by dbid, instance_number) t | |
cross join dba_hist_stat_name sn | |
join dba_hist_sys_time_model e on e.snap_id = t.end_snap and e.dbid = t.dbid and e.instance_number = t.instid and e.stat_id = sn.stat_id | |
join dba_hist_sys_time_model b on b.snap_id = t.begin_snap and b.dbid = t.dbid and b.instance_number = t.instid and b.stat_id = sn.stat_id | |
where sn.stat_name = 'DB time' | |
) r; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select r.*, round(dbtime / elapsed, 2) * 100 rel_dbtime_pct from ( | |
select t.instid, round((e.value - b.value)/1000000,1) dbtime, elapsed | |
from (select dbid, instance_number instid, min(snap_id) begin_snap, max(snap_id) end_snap, (max(snap_time)-min(snap_time))*24*60*60 elapsed | |
from stats$snapshot group by dbid, instance_number) t | |
cross join stats$time_model_statname sn | |
join stats$sys_time_model e on e.snap_id = t.end_snap and e.dbid = t.dbid and e.instance_number = t.instid and e.stat_id = sn.stat_id | |
join stats$sys_time_model b on b.snap_id = t.begin_snap and b.dbid = t.dbid and b.instance_number = t.instid and b.stat_id = sn.stat_id | |
where sn.stat_name = 'DB time' | |
) r; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment