概述
在数据库中事务是工作的逻辑单元,一个事务是由一个或多个完成一组的相关行为的SQL语句组成,通过事务机制确保这一组SQL语句所作的操作要么都成功执行,完成整个工作单元操作,要么一个也不执行。
如果要查询当前事务我们可以查询v$transaction表获得相关信息,查一段时间的事务数可以通过awr报告中的Transactions和user commits来得知,不过今天主要介绍怎么用sql来查询事务数。
1、查询XX时间段每秒事务数
select instance_number, metric_unit, trunc(begin_time) time, round(avg(average), 2) average from DBA_HIST_SYSMETRIC_SUMMARY where metric_unit = 'Transactions Per Second' and begin_time >= to_date('2019-10-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and begin_time < to_date('2019-10-09 14:00:00', 'yyyy-mm-dd hh24:mi:ss') group by instance_number, metric_unit, trunc(begin_time) order by instance_number;
2、查询XX时间段每天事务数
select instance_number, metric_unit, trunc(begin_time) time, avg(average) * 60 * 60 * 24 "Transactions Per Day" from DBA_HIST_SYSMETRIC_SUMMARY where metric_unit = 'Transactions Per Second' and begin_time >= to_date('2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and begin_time < to_date('2019-10-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') group by instance_number, metric_unit, trunc(begin_time) order by instance_number;
3、查询某个用户事务数
官网:
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