每分钟的SYSSTAT汇总统计

--每分钟的SYSSTAT汇总统计
select to_char(createtime,'dd hh24:mi') createtime,
       round((user_commits - lag(user_commits, 1, user_commits) over(order by createtime)) / 60, 0) commits,
       round((user_rollbacks - lag(user_rollbacks, 1, user_rollbacks) over(order by createtime)) / 60, 0) rollbacks,
       round((user_commits - lag(user_commits, 1, user_commits) over(order by createtime)) / 60, 0) +
            round((user_rollbacks - lag(user_rollbacks, 1, user_rollbacks) over(order by createtime)) / 60, 0) transactions,
       round((session_logical_reads - lag(session_logical_reads, 1, session_logical_reads) over(order by createtime)) / 60, 0) logical_reads,
       round((db_block_gets - lag(db_block_gets, 1, db_block_gets) over(order by createtime)) / 60, 0) db_block_gets,
       round((consistent_gets - lag(consistent_gets, 1, consistent_gets) over(order by createtime)) / 60, 0) consistent_gets
  from (select to_date(to_char(createtime, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd hh24:mi') createtime,
                sum(case when name = 'user commits' then value else 0 end) user_commits,
                sum(case when name = 'user rollbacks' then value else 0 end) user_rollbacks,
                sum(case when name = 'session logical reads' then value else 0 end) session_logical_reads,
                sum(case when name = 'db block gets' then value else 0 end) db_block_gets,
                sum(case when name = 'consistent gets' then value else 0 end) consistent_gets
           from productmon.sysstat_log
          where 1 = 1
            --and createtime >= to_date('2013-12-23 09:00', 'yyyy-mm-dd hh24:mi')
            and createtime >= sysdate - 30 / 1440
          group by to_date(to_char(createtime, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd hh24:mi'))
 order by 1 desc;