概述

在資料庫中事務是工作的邏輯單元，一個事務是由一個或多個完成一組的相關行為的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方面的內容，感興趣的朋友可以關注下~