概述

在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关行为的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", 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个时间段分别运行改脚本，把执行结果相减，即可得出该时间段内的事务数

4、查询当前正在执行的事务

SELECT s.sid, s.serial#, s.event, a.sql_text, a.sql_fulltext, s.username, s.status, s.machine, s.terminal, s.program, a.executions, s.sql_id, p.spid, a.direct_writes FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s LEFT JOIN v$sqlarea a ON s.sql_id = a.sql_id INNER JOIN v$process p ON s.paddr = p.addr 

5、查询长事务

with transaction_details as ( select inst_id , ses_addr , sysdate - start_date as diff from gv$transaction ) select s.username , to_char(trunc(t.diff)) || ' days, ' || to_char(trunc(mod(t.diff * 24,24))) || ' hours, ' || to_char(trunc(mod(t.diff * 24 * 60,24))) || ' minutes, ' || to_char(trunc(mod(t.diff * 24 * 60 * 60,60))) || ' seconds' as transaction_duration , s.program , s.terminal , s.status , s.sid , s.serial# from gv$session s , transaction_details t where s.inst_id = t.inst_id and s.saddr = t.ses_addr order by t.diff desc 

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~