Oracle transaction stats

Information about the number of completed transactions allows you to quickly assess the load on the system. Plan allocation of CPU, IO resources

Count transaction view

Information on the number of transactions by year, month, day

For the year - Statistics by years

SELECT TO_CHAR(FIRST_TIME,'YYYY') INTERVAL,

round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT" FROM V$LOG_HISTORY

GROUP BY TO_CHAR(FIRST_TIME,'YYYY')

ORDER BY 1

Monthly - Statistics for the months of the current year

SELECT TO_CHAR(FIRST_TIME,'MM') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY

where to_char(first_time,'yyyy')=$$CURRENT_YEAR
GROUP BY TO_CHAR(FIRST_TIME,'MM')
ORDER BY 1

For days - Statistics for the days of the current year

SELECT TO_CHAR(FIRST_TIME,'MM-dd') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY

where to_char(first_time,'yyyy')=$$CURRENT_YEAR
GROUP BY TO_CHAR(FIRST_TIME,'MM-dd')
ORDER BY 1

Dead transaction

Transactions for which the rollback failed. Data from system table x$ktuxe

select ktuxeusn,
ktuxeslt,
ktuxesqn,
ktuxesta,
ktuxesiz
from x$ktuxe
where ktuxesta <> 'INACTIVE'

and ktuxecfl like 'ÞAD%'

order by ktuxesiz asc

Rollback progress

Information on the blocks processed by the transaction is displayed. In this case, if the value in the "used_ublk" column decreases, this signals the rollback of the changed data.

select ses.username,

ses.sid,
substr(ses.program, 1, 19) command,
tra.used_ublk
from v$session ses,

v$transaction tra
where ses.saddr = tra.ses_addr

Transaction recovery by SMON

List of sessions whose changes are rolled back by SMON (session was terminated in OS)

select usn,

state,

undoblockstotal "Total",

undoblocksdone "Done",

undoblockstotal-undoblocksdone "ToDo", decode(cputime,0, 'unknown',

sysdate+(((undoblockstotal-undoblocksdone)/(undoblocksdone/cputime))/86400)) "Estimated"

from v$fast_start_transactions