前段时间写过一个很丑陋的过程来做这个事情,后来发现使用lag函数可以轻松实现,其实只要把STATS$SYSSTAT中的数据按照snap_id排序一下,然后用下一个减去上一个,得到的就是事务量了。同理,只要去statspack的spreport中挖掘,相应的哪个时间段产生redo最多,哪个时间段物理读最大等等都可以实现。[@more@]
A.SNAP_ID,
ROUND((SUM(A.VALUE) - LAG(SUM(A.VALUE), 1, SUM(A.VALUE))
OVER(ORDER BY B.SNAP_TIME)) /
(B.SNAP_TIME - LAG(B.SNAP_TIME, 1, SYSDATE)
OVER(ORDER BY B.SNAP_TIME)) / 24 / 60 / 60,
2) "trans/sec"
FROM STATS$SYSSTAT A, STATS$SNAPSHOT B
WHERE (A.NAME = 'user commits' OR A.NAME = 'user rollbacks')
AND A.SNAP_ID = B.SNAP_ID
AND B.SNAP_TIME >= SYSDATE - 1
GROUP BY B.SNAP_TIME, A.SNAP_ID;
1、哪段时间的每秒事务量最大
SELECT B.SNAP_TIME,
A.SNAP_ID,
ROUND((SUM(A.VALUE) - LAG(SUM(A.VALUE), 1, SUM(A.VALUE))
OVER(ORDER BY B.SNAP_TIME)) /
(B.SNAP_TIME - LAG(B.SNAP_TIME, 1, SYSDATE)
OVER(ORDER BY B.SNAP_TIME)) / 24 / 60 / 60,
2) "trans/sec"
FROM STATS$SYSSTAT A, STATS$SNAPSHOT B
WHERE (A.NAME = 'user commits' OR A.NAME = 'user rollbacks')
AND A.SNAP_ID = B.SNAP_ID
AND B.SNAP_TIME >= SYSDATE - 1
GROUP BY B.SNAP_TIME, A.SNAP_ID;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25016/viewspace-916113/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25016/viewspace-916113/