Топ-100
 
CerebroSQL

Oracle transaction stats

Information on transactions in the Oracle database, their number by time, statuses, rollback

Count transaction view
Count transaction view.jpg

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.jpg

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.jpg

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.jpg

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