詳解Oracle 數據庫中的事務數及長事務常用sql

在數據庫中事務是工作的邏輯單元，一個事務是由一個或多個完成一組的相關行為的SQL語句組成，通過事務機制確保這一組SQL語句所作的操作要么都成功執行，完成整個工作單元操作，要么一個也不執行。

如果要查詢當前事務我們可以查詢v$transaction表獲得相關信息，查一段時間的事務數可以通過awr報告中的Transactions和user commits來得知，不過今天主要介紹怎么用sql來查詢事務數。

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;

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;

官網：

DeltaCommits + DeltaRollbacks where:DeltaCommits: difference of 'select value from v$sysstat where name='user commits'' between sample end and startDeltaRollbacks: 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個時間段分別運行改腳本，把執行結果相減，即可得出該時間段內的事務數

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

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方面的內容，感興趣的朋友可以關注下~