db_time
emcs
98次下载
813次浏览
2019-07-24
4.5

脚本内容

--查询DB Time SELECT TO_CHAR(a.end_interval_time,'yyyymmdd hh24'), SUM (a.db_time) inst1_m, SUM (b.db_time) inst2_m FROM (SELECT pre_snap_id, snap_id, end_interval_time, ROUND((value - pre_value) / 1000000 / 60) db_time FROM (SELECT a.snap_id, end_interval_time, lag(a.snap_id) over(order by a.snap_id) pre_snap_id, value, lag(value) over(order by a.snap_id) pre_value FROM dba_hist_sys_time_model a, dba_hist_snapshot b WHERE stat_name = 'DB time' AND a.dbid = b.dbid AND a.snap_id = b.snap_id AND a.instance_number=b.instance_number AND a.dbid = (SELECT dbid FROM v$database ) AND a.instance_number = 1 ) WHERE pre_snap_id IS NOT NULL AND end_interval_time>sysdate-30 ORDER BY snap_id DESC ) a, (SELECT pre_snap_id, snap_id, end_interval_time, ROUND((value - pre_value) / 1000000 / 60) db_time FROM (SELECT a.snap_id, end_interval_time, lag(a.snap_id) over(order by a.snap_id) pre_snap_id, value, lag(value) over(order by a.snap_id) pre_value FROM dba_hist_sys_time_model a, dba_hist_snapshot b WHERE stat_name = 'DB time' AND a.dbid = b.dbid AND a.snap_id = b.snap_id AND a.instance_number=b.instance_number AND a.dbid = (SELECT dbid FROM v$database ) AND a.instance_number = 2 ) WHERE pre_snap_id IS NOT NULL AND end_interval_time>sysdate-30 ORDER BY snap_id DESC ) b WHERE a.snap_id=b.snap_id(+) GROUP BY TO_CHAR(a.end_interval_time,'yyyymmdd hh24') ORDER BY TO_CHAR(a.end_interval_time,'yyyymmdd hh24');

评论

贡献排行榜