top of page
CerebroSQL

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

Oracle transaction count

Count transaction view

Page transaction count

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

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

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

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

bottom of page