Shareware software


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