Oracle获取吞吐量和IOPS的脚本

Oracle获取吞吐量和IOPS的脚本

数据底层存储要换盘，对新盘做IOPS压测。

并花了点时间写了脚本获取数据库每秒的吞吐量和IOPS信息。

可以将数据导成excel并绘制为折线图等统计图。 

--获取 IOPS(当前实例)
select to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi') || ' -> ' ||
       to_char(t.end_interval_time, 'hh24:mi') "TimeRange",
       --t.seconds "Seconds",
       round(sum(t.PhyRWTotalIOReq) / t.seconds, 2) IOPS
  from (select b.begin_interval_time,
               b.end_interval_time,
               (to_date(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') -
               to_date(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 seconds,
               a.stat_name,
               lag(a.value, 1) over(partition by stat_name order by b.end_interval_time) last_value,
               a.value,
               a.value - lag(a.value, 1) over(partition by stat_name order by b.end_interval_time) PhyRWTotalIOReq
          from dba_hist_sysstat a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.instance_number = (select instance_number from v$Instance)
           and a.stat_name in
               ('physical read total IO requests',
                'physical write total IO requests')
         order by a.stat_name, b.end_interval_time) t
 where t.last_value is not null
 group by t.begin_interval_time, t.end_interval_time, t.seconds
 order by t.end_interval_time;

--获取 IOPS (全部实例)
select to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi') || ' -> ' || to_char(t.end_interval_time, 'hh24:mi') "TimeRange",
       round(sum(t.PhyRWTotalIOReq) / t.seconds, 2) IOPS
  from (select b.begin_interval_time,
               b.end_interval_time,
               (to_date(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') -
               to_date(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 seconds,
               a.stat_name,
               lag(a.value, 1) over(partition by a.instance_number, stat_name order by b.end_interval_time) last_value,
               a.value,
               a.value - lag(a.value, 1) over(partition by a.instance_number, stat_name order by b.end_interval_time) PhyRWTotalIOReq
          from dba_hist_sysstat a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.stat_name in
               ('physical read total IO requests',
                'physical write total IO requests')
         order by a.instance_number, a.stat_name, b.end_interval_time) t
 where t.last_value is not null
 group by to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi') || ' -> ' || to_char(t.end_interval_time, 'hh24:mi'),
          t.seconds
 order by 1;

--获取 throughput(当前实例)
select to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi') || ' -> ' ||
       to_char(t.end_interval_time, 'hh24:mi') "TimeRange",
       --t.seconds "Seconds",
       round(sum(t.PhyRWTotalBytes) / t.seconds, 2) "ThroughputPersBytes"
  from (select b.begin_interval_time,
               b.end_interval_time,
               (to_date(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') -
               to_date(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 seconds,
               a.stat_name,
               lag(a.value, 1) over(partition by stat_name order by b.end_interval_time) last_value,
               a.value,
               a.value - lag(a.value, 1) over(partition by stat_name order by b.end_interval_time) PhyRWTotalBytes
          from dba_hist_sysstat a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.instance_number = (select instance_number from v$Instance)
           and a.stat_name in
               ('physical read total bytes', 'physical write total bytes')
         order by a.stat_name, b.end_interval_time) t
 where t.last_value is not null
 group by t.begin_interval_time, t.end_interval_time, t.seconds
 order by t.end_interval_time;

--获取 throughput(全部实例)
select to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi') || ' -> ' || to_char(t.end_interval_time, 'hh24:mi') "TimeRange",
       --t.seconds "Seconds",
       round(sum(t.PhyRWTotalBytes) / t.seconds, 2) "ThroughputPersBytes"
  from (select b.begin_interval_time,
               b.end_interval_time,
               (to_date(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') -
               to_date(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 seconds,
               a.stat_name,
               lag(a.value, 1) over(partition by a.instance_number, stat_name order by b.end_interval_time) last_value,
               a.value,
               a.value - lag(a.value, 1) over(partition by a.instance_number, stat_name order by b.end_interval_time) PhyRWTotalBytes
          from dba_hist_sysstat a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.stat_name in
               ('physical read total bytes', 'physical write total bytes')
         order by a.instance_number, a.stat_name, b.end_interval_time) t
 where t.last_value is not null
 group by to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi') || ' -> ' || to_char(t.end_interval_time, 'hh24:mi'), t.seconds
 order by 1;

https://www.cnblogs.com/PiscesCanon/p/18229342

防。

折线图效果：