Топ-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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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.

-> Request to retrieve 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)

-> Request to retrieve data <-

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

-----