0
db_time
emcs
72次下载
415次浏览
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');

评论

热门资源
热门脚本
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...