Connected.
SQL>
SQL>
SQL> insert into ttt values('1','2','3','4','5');
---------- ---------- ---------- ----------------
10 17 22579 ACTIVE
Transactions: | 916.9 |
user commits | 827,280 | 916.90 | 1.00 |
SQL> select instance_number,
2 metric_unit,
3 trunc(begin_time) time,
4 round(avg(average), 2) average
5 from DBA_HIST_SYSMETRIC_SUMMARY
6 where metric_unit = 'Transactions Per Second'
7 and begin_time >=
8 to_date('2013-01-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
9 and begin_time < to_date('2013-01-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
10 group by instance_number, metric_unit, trunc(begin_time)
11 order by instance_number;
--------------- ------------------------------ ------------ ----------
1 Transactions Per Second 21-JAN-13 .17
2 metric_unit,
3 trunc(begin_time) time,
4 avg(average)*60*60*24 "Transactions Per Day"
5 from DBA_HIST_SYSMETRIC_SUMMARY
6 where metric_unit = 'Transactions Per Second'
7 and begin_time >=
8 to_date('2013-01-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
9 and begin_time < to_date('2013-01-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
10 group by instance_number, metric_unit, trunc(begin_time)
11 order by instance_number;
--------------- ------------------------------ ------------ --------------------
1 Transactions Per Second 21-JAN-13 14727.5308
DeltaCommits + DeltaRollbacks where:
-
DeltaCommits: difference of 'select value from v$sysstat where name='user commits'' between sample end and start
-
DeltaRollbacks: difference of 'select value from v$sysstat where name='user rollbacks'' between sample end and start
在2个时间段分别执行上面语句,把2个结果相减即可得出这段时间内的事务数。
根据这个方法,我写出查询某个用户的时间段内事务数的方法:
select s.USERNAME,sum(se.VALUE) "session transaction number",sum(sy.VALUE) " database transaction number" from v$session s,v$sesstat se,v$sysstat sy
where s.sid=se.SID and se.STATISTIC#=sy.STATISTIC#
and sy.NAME='user commits'
and s.USERNAME=upper('&username')
group by s.USERNAME;
使用方法和上面的一样,需要在2个时间段分别运行改脚本,把执行结果相减,即可得出该时间段内的事务数
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12457158/viewspace-752987/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12457158/viewspace-752987/